4

I didn't use the '-E 3857' switch when importing the OSM data into postgresql using osm2psql.

I'm trying to set up a map server for the first time and I'm following the instructions from here: https://github.com/mapserver/mapserver/wiki/RenderingOsmDataWindows#osm-prep.

I read that epsg900913 and epsg3857 are basically the same here: What is the difference between EPSG:900913 and EPSG:3857?.

How do I change/transform the spatial reference in the osm tables (i.e. osm_line, osm_point, osm_polygon, etc) within postgresql from epsg900913 to epsg3857 using a SQL command?

Community
  • 1
Patriotec
  • 201
  • 2
  • 5

2 Answers2

6

I found the solution here: http://postgis.net/docs/UpdateGeometrySRID.html

--This will change the srid of the roads table to 3857 from whatever it was before

UpdateGeometrySRID(varchar table_name, varchar column_name, integer srid);

So for my database the table name was 'planet_osm_line' and the column that contains the srid and geometry is 'way'. the SQL query I ran was:

SELECT UpdateGeometrySRID('planet_osm_line','way',3857);

I checked if it worked by running a psql query from the command line:

psql -U postgres -d osm -c "SELECT ST_extent(way) FROM planet_osm_line"

Data was returned

Patriotec
  • 201
  • 2
  • 5
0

If you want a little more control and a little more SQL:

UPDATE TABLE foo
SET geom = ST_TRANSFORM(geom, 3857);

The command updates the geom geometry column of your table foo with a geometry with transformed SRID.

raphael
  • 3,407
  • 23
  • 61