[GIS] Creating GeoJSON from Postgres

postgispostgis-2.0postgresql

We are transitioning from Google Maps to Mapbox. Mapbox is a proponent of geoJSON and uses it extensively, so we are considering adopting it for all of our maps.

An issue is the creation of the geoJSON from Postgres v9.3.6 with PostGIS v2.1.5, and within PHP

This is my current SQL query:

SELECT
stop_name, stop_lat, stop_lon, stop_time_id, stop_times.stop_id 
FROM stop_times
INNER JOIN stops 
ON
stop_times.stop_id = stops.stop_id
WHERE
trip_id =$query_trip_id
ORDER BY
stop_times.stop_sequence
ASC";

This is an example output:

stop_name || stop_lat || stop_lon || stop_time_id || stop_id || arrival_time || departure_time
Location1   35.782312   -78.671343     1526016        777640      09:17:00         09:17:00
Location2   35.78144    -78.674683     1526125        777662      09:19:00         09:19:00
Location3   35.78022    -78.67614      1526126        777641      09:19:00         09:19:00

It seems as though I have 2 options to generate this file from the database:

1) rewrite the above SQL query so that it generates the geoJSON file directly as per the instructions below:

http://www.postgresonline.com/journal/archives/267-Creating-GeoJSON-Feature-Collections-with-JSON-and-PostGIS-functions.html

(note that the example in the link uses geography(POINT), rather than lat and long in separate fields)

2) create temporary table, insert the results from the intitial SQL query, and generate the geoJSON from the single temporary table

As I see it, option 1 means writing some horrible looking SQL to generate the geoJSON, because I am joining tables (and I have to do this with several maps), but the processing times will be reduced

Option 2 has the advantage that the original SQL stays intact, and I only have to create the geoJSON from one table (and not two). But the processing time will be longer.

Recommendations?

Best Answer

To follow up, what I did was to:

  1. Keep the original SQL query (easier to understand/maintiain)

  2. Create a temp table

  3. Insert values into temp table, using ST_MakePoint($stop_lon, $stop_lat) and geom to handle the lat/long values

  4. Use the following SQL to create the geoJSON file

    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((SELECT l FROM (SELECT stop_name, stop_time_id, stop_id, arrival_time, departure_time) As l )) As properties FROM temptable_trip_visualization As lg ) As f ) As fc;

Related Question