1

I am trying to get data from a PostGIS datatable using .NET, pass it to javascript, create a layer in OpenLayers and add the PostGIS data to the layer. I can successfully query from the PostGres/PostGIS table using the following SQL;

SELECT ""tract_name"", ST_AsGeoJSON(""geom"") FROM the_layer;

After querying the database the result ends up in the PostGres DataAdapter as a DataSet.

I was hoping to simply pass the data to javascript where I could use the following to add the data;

        var geojson_format = new OpenLayers.Format.GeoJSON();
        var vector_layer = new OpenLayers.Layer.Vector("query_POSTGIS_layer"); 
        map.addLayer(vector_layer);
        vector_layer.addFeatures(geojson_format.read(postGISqueryResult)); 

What I can't figure out is how to convert the PostGres DataSet into a GeoJSON format that can be handled by the code above.

Application stack is PostGres/PostGIS, GeoServer, and OpenLayers.

3

Make sure your response is a featurecollection.

You can get more information on how to do this here: http://www.postgresonline.com/journal/archives/267-Creating-GeoJSON-Feature-Collections-with-JSON-and-PostGIS-functions.html

So you probably can get along with something like

   SELECT row_to_json(fc)
 FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
 FROM (SELECT 'Feature' As type
    , ST_AsGeoJSON(lg.geom)::json As geometry
    , row_to_json((tract_name)) As properties
   FROM the_layer As lg   ) As f )  As fc;
  • Thanks Thomas, I appreciate your assistance. But, that leaves me with the same problem How do I change that featureCollection to something OpenLayers/JavaScript can read? – Todd Krueger Nov 13 '15 at 15:53
  • Or to be more specific...How do I pass the featureCollection created in .NET back to OpenLayers/JavaScript? Thanks – Todd Krueger Nov 13 '15 at 16:29
  • i don't work with .NET, but this answer tou your question sounds quite promising: gis.stackexchange.com/questions/170108/…. what do you get if you call console.log(geojson_format.read(postGISqueryResult)) at the end of your statement? – Thomas B Nov 14 '15 at 21:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.