I'm trying to convert line features from PostGIS to GeoJSON files, as follows:
from psycopg2 import *
from subprocess import call
conn = connect(dbname='gis')
cur=conn.cursor()
cur.execute("SELECT distinct osm_id,route_name from planet_osm_line where route_name like '%Rail Trail' and network='rcn';")
for record in cur:
print "%s" % record[1]
call (["ogr2ogr", "-f", "GeoJSON", record[1] + ".json", 'PG:dbname=\'gis\'', "-sql", 'SELECT route_name,osm_id,tags::hstore->\'state\' as state,way from planet_osm_line where osm_id=%d' % record[0]])
That is, ultimately ogr2ogr gets called like this:
ogr2ogr -f GeoJSON blah.json "PG:dbname='gis'" -sql 'SELECT route_name,osm_id,tags::hstore->\'state\' as state,way from planet_osm_line where osm_id=-12345'
The GeoJSON files that get created have coordinates that look like this [ 16153119.78, -4561568.23 ]
:
{ "type": "Feature", "properties": { "route_name": "Waverley Rail Trail", "osm_id": -64660, "state": null }, "geometry": { "type": "LineString", "coordinates": [ [ 16153119.78, -4561568.23 ], [ 16153117.1, -4561567.48 ], [ 16153114.81, -4561565.86 ], [ 16153113.24, -4561563.55 ], [ 16153112.64, -4561561.63 ], [ 16153112.64, -4561558.82 ], [ 16153113.6, -4561556.2 ], [ 16153115.39, -4561554.05 ], [ 16153117.82, -4561552.66 ], [ 16153119.98, -4561552.2 ], [ 16153122.76, -4561552.48 ], [ 16153125.28, -4561553.69 ], [ 16153127.35, -4561555.88 ], [ 16153128.43, -4561558.46 ], [ 16153128.62, -4561560.38 ], [ 16153128.08, -4561563.13 ], [ 16153126.64, -4561565.51 ], [ 16153124.47, -4561567.26 ], [ 16153121.82, -4561568.17 ], [ 16153119.78, -4561568.23 ] ] } }
Please forgive my ignorance, but what am I doing wrong here? I assume what is being written is some kind of projected value rather than the raw lat/long – so how do I get those?
I've tried using t_srs=ESPG:3857
but that didn't fix it.
Best Answer
OpenLayers uses the EPSG:3857 coordinate system, in meters, and not the WGS84 system, in degrees, look at OpenStreetMap Wiki: EPSG:3857
But why use
subprocess
andogr2ogr
?1) you can use directly the PostGIS ST_AsGeoJSON function:
What is the srid of your layer ?
Use of
ST_AsGeoJSON
:2) or the
osgeo.ogr
module: