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/journ...lections-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?
أكثر...
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/journ...lections-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?
أكثر...