A function that converts a PostGIS query into a GeoJSON object.

postgistogeojson.js

/* 
 *   A function that converts a PostGIS query into a GeoJSON object.
 *   Copyright (C) 2012  Samuel Giles <[email protected]>
 *
 *   This program is free software: you can redistribute it and/or modify
 *   it under the terms of the GNU General Public License as published by
 *   the Free Software Foundation, either version 3 of the License, or
 *   (at your option) any later version.
 *
 *   This program is distributed in the hope that it will be useful,
 *   but WITHOUT ANY WARRANTY; without even the implied warranty of
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *   GNU General Public License for more details.

 *  You should have received a copy of the GNU General Public License
 *  along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

/**
 * Takes an array of associative objects/arrays and outputs a FeatureCollection object.  See <http://www.geojson.org/geojson-spec.html> example 1.1/
 * The Query that fetched the data would need to be similar to: 
 *              SELECT {field_list}, st_asgeojson(...) AS geojson FROM geotable
 * Where the "AS geojson" must be as is. Because the function relies on a "geojson" column.
 * 
 * @param queryResult The query result from the PostGIS database.  Format deduced from <https://gist.github.com/2146017>
 * @returns The equivalent GeoJSON object representation.
 */
function postGISQueryToFeatureCollection(queryResult) {
  // Initalise variables.
  var i = 0,
      length = queryResult.length,
      prop = null,
      geojson = {
        "type": "FeatureCollection",
        "features": []
      };    // Set up the initial GeoJSON object.

  for(i = 0; i < length; i++) {  // For each result create a feature
    var feature = {
      "type": "Feature",
      "geometry": JSON.parse(queryResult[i].geojson)
    };
    // finally for each property/extra field, add it to the feature as properties as defined in the GeoJSON spec.
    for(prop in queryResult[i]) {
      if (prop !== "geojson" && queryResult[i].hasOwnProperty(prop)) {
        feature[prop] = queryResult[i][prop];
      }
    }
    // Push the feature into the features array in the geojson object.
    geojson.features.push(feature);
  }
  // return the FeatureCollection geojson object.
  return geojson;
}

/*  ##########################         EXAMPLE              ######################################## */

// An array of ASSOCIATIVE results filled with two example results.
var queryResults = [
   {
      "geojson": '{"type": "Point", "coordinates": [102.0, 0.5]}',
      "prop0" : "value0"
   },
   {
      "geojson": '{ "type": "LineString","coordinates": [[102.0, 0.0], [103.0, 1.0], [104.0, 0.0], [105.0, 1.0]]}',
      "prop1": 0.0
   }];


var geoJson = postGISQueryToFeatureCollection(queryResults);

Note, in the example, prop0 and prop1 would actually be the field names specified in the field list in the SQL, IFF I've understood correctly. (although obviously the names would be consistent between tuples, but come on it's an example :p)

Sam, Let take this example.

Select AsGeoJSON(geometry),name,country, city from developer;

This is the result:

var queryResults =
{
"type": "FeatureCollection",
"features": [
{ "type": "Feature", "geometry": {"type": "Point","coordinates": [1332700, 7906300]},
"properties": {"Name": "Igor Tihonov","Country":"Sweden", "City":"Gothenburg"}},
{ "type": "Feature", "geometry": {"type": "Point","coordinates": [790300, 6573900]},
"properties": {"Name": "Marc Jansen","Country":"Germany", "City":"Bonn"}},
{ "type": "Feature", "geometry": {"type": "Point","coordinates": [568600, 6817300]},
"properties": {"Name": "Bart van den Eijnden","Country":"Netherlands", "City":"Utrecht"}},
{ "type": "Feature", "geometry": {"type": "Point","coordinates": [-7909900, 5215100]}
]
};

It seems right. Now, do I add this what I want to do.

Here is the one that I hacked for Spatialite that we are addressing. I am just using PostGIS example since I am not sure yet on this Spatialite hack.

https://gist.github.com/2296532

However, you are already in the right track. However, it is not yet at the right format. Properties is missing, as you can below in PHP code.

'{"type": "Feature", "geometry": ' . $row['geojson'] . ', "properties": {';
$props = '';

Thanks. Noli

Sam,

Now, how to I incorporate you solution my function.

 function SpatialiteQueryToFeatureCollection(bcoResults) {
      var aResult = bcoResults[0];
      if (aResult.errorMessage != 'not an error') {
        handleError('queryError', [aResult.errorMessage]);
         return qc.STACK_EXIT;
   }

  // support AsText(Geometry), Askml(Geometry) and AsGeoJSON(Geometry)
  var aformatType = trim(document.getElementById('formatType').value);
      var fieldNames = aResult.fieldNames,
   records = aResult.data,
   container = document.getElementById('OutputWKT');
    container.innerHTML = '';

var theField = 0;
for (var i = 0, ii = fieldNames.length; i < ii; i++) {

    if (fieldNames[i] == aformatType) {
   // if (fieldNames[i] == 'AsText(Geometry)') {
        theField = i;
        break;
    }
}

for (var i = 0, ii = records.length; i < ii; i++) {
    var t = document.createElement('textarea');
    t.innerText = records[i][theField];
    container.appendChild(t);
}

return qc.STACK_CONTINUE;
}

Noli

I think I see, so actually, you're getting a result set as well formed GeoJSON, if that is the result set from the query exactly, the JSON parse/encode functions should work as is without the need for a specific function. See the spec for what I mean: http://www.geojson.org/geojson-spec.html

Sam,

Reply for comments above.

I am getting GeoJSON result for AsGeoJSON(geometry), not the other attributes i.e. name, city and country. It needs to be in JSON (GeoJSON for spatial + JSON for the attributes (i.e. properties).

I modified your function. Please check. Is "geoson" the header of the column which probably "AsGeoJSON(geometry)" in Select AsGeoJSON(geometry),name,country, city from developer;

   function SpatialiteQueryToFeatureCollection(bcoResults) {
          var aResult = bcoResults[0];
          if (aResult.errorMessage != 'not an error') {
            handleError('queryError', [aResult.errorMessage]);
             return qc.STACK_EXIT;
       }

      // support AsText(Geometry), Askml(Geometry) and AsGeoJSON(Geometry)
      // var aformatType = trim(document.getElementById('formatType').value);
      // var fieldNames = aResult.fieldNames,
      //  records = aResult.data,
      //  container = document.getElementById('OutputWKT');
      //   container.innerHTML = '';

         var i = 0,
          length = queryResult.length,
          prop = null,
          geojson = {
            "type": "FeatureCollection",
            "features": []
          }; // Set up the initial GeoJSON object.


      for(i = 0; i < length; i++) { // For each result create a feature
        var feature = {
          "type": "Feature",
         // "geometry": JSON.parse(queryResult[i].geojson)
           "geometry": JSON.parse(queryResult[i].AsGeoJSON(geometry))

        };

        // Just get the AsGeoJSON(geometry)
        for (var i = 0, ii = records.length; i < ii; i++) {
            var t = document.createElement('textarea');
            t.innerText = records[i][theField];
            container.appendChild(t);
        }

        // finally for each property/extra field, add it to the feature as properties as defined in the GeoJSON spec.
        for(prop in queryResult[i]) {

          // if (prop !== 'AsGeoJSON(geometry)' && queryResult[i].hasOwnProperty(prop)) {

          if (prop !== 'AsGeoJSON(geometry)' && queryResult[i].hasOwnProperty(prop)) {
            feature[prop] = queryResult[i][prop];
          }
        }
        // Push the feature into the features array in the geojson object.
        geojson.features.push(feature);
      }
      // return the FeatureCollection geojson object.
      //return geojson;
        bcoResults = geoson;

      return qc.STACK_CONTINUE;
      }

I think you have not completely decipher this

$rowOutput = (strlen($rowOutput) > 0 ? ',' : '') . '{"type":
"Feature", "geometry": ' . $row['geojson'] . ', "properties": {';

  for(i = 0; i < length; i++) { // For each result create a feature
        var feature = {
          '{type": "Feature",
         // "geometry": JSON.parse(queryResult[i].geojson),
           "properties": {';

Sam,

This is the latest version. Kindly check if it make sense.

Thanks, Noli

  function SpatialiteQueryToFeatureCollection(bcoResults,  parameters){
          var aResult = bcoResults[0];
          if (aResult.errorMessage != 'not an error') {
            handleError('queryError', [aResult.errorMessage]);
             return qc.STACK_EXIT;
       }

      // support AsText(Geometry), Askml(Geometry) and AsGeoJSON(Geometry)
       var aformatType = trim(document.getElementById('formatType').value);
       var fieldNames = aResult.fieldNames,

        // records[i][theField] is ==  queryResult[i].geojson)
        // geoson is actually the theField

       var records = aResult.data,

       var queryResult = aResult.data,

        container = document.getElementById('OutputWKT');
        container.innerHTML = '';



         // geoson = theField;

         var i = 0,
         var length = queryResult.length,
         var prop = null,

          var geojson = {
            "type": "FeatureCollection",
            "features": []
          }; // Set up the initial GeoJSON object.


        // old for AsGeoJSON(geometry)
        var theField = 0;
        for (var i = 0, ii = fieldNames.length; i < ii; i++) {

            if (fieldNames[i] == aformatType) {
            // if (fieldNames[i] == 'AsText(Geometry)') {
             theField = i;
             break;
           }
         }

   //    $rowOutput = (strlen($rowOutput) > 0 ? ',' : '') . '{"type":
  //     "Feature", "geometry": ' . $row['geojson'] . ', "properties": {';


      for(i = 0; i < length; i++) { // For each result create a feature
        var feature = {
          "{type": "Feature",
           "geometry": JSON.parse(queryResult[i].geojson),        
           "properties": {",

        };

        // Just get the AsGeoJSON(geometry)
        for (var i = 0, ii = records.length; i < ii; i++) {
            var t = document.createElement('textarea');
            t.innerText = records[i][theField];
            container.appendChild(t);
        }

        // finally for each property/extra field, add it to the feature as properties as defined in the GeoJSON spec.
        for(prop in queryResult[i]) {

          // if (prop !== "geojson" && queryResult[i].hasOwnProperty(prop)) {

          if (prop !== 'AsGeoJSON(geometry)' && queryResult[i].hasOwnProperty(prop)) {
            feature[prop] = queryResult[i][prop];
          }
        }
        // Push the feature into the features array in the geojson object.
        geojson.features.push(feature);
      }
      // return the FeatureCollection geojson object.
      //return geojson;
      //  bbcoResults = geoson;
        var t = document.createElement('textarea');
        t.innerText = geoson;


      return qc.STACK_CONTINUE;
      }

Sam,

I am not sure about this one:

   //    $rowOutput = (strlen($rowOutput) > 0 ? ',' : '') . '{"type":
  //     "Feature", "geometry": ' . $row['geojson'] . ', "properties": {';

Is this the right javascript for the above?

      for(i = 0; i < length; i++) { // For each result create a feature
        var feature = {
          {"type": "Feature",
          "geometry": JSON.parse(queryResult[i].geojson),"properties": {;
        };

Noli