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);

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