PostGIS Query To OpenLayers

Question

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.

Answer 1

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;

• Todd Krueger: 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: Or to be more specific...How do I pass the featureCollection created in .NET back to OpenLayers/JavaScript? Thanks
• Thomas B: 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?