0

The OSM data is in 900913. I need to get 4326, but so that the longitude's range is NOT -180..180, but rather some other, shifted range, based on a parameter. This way, if I need to draw an object near antimeridian (e.g. Alaska), I could request range of -360..0. The leaflet will be centered at -180, and there won't be any incomplete shapes.

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
Yuri Astrakhan
  • 1,053
  • 8
  • 13

2 Answers2

2

Firstly you ST_Transform(geom,4236) to transform the geometry to the required CRS. https://postgis.net/docs/ST_Transform.html

Then use ST_Translate(geom,x,y) to shift it to a new spot. https://www.postgis.net/docs/ST_Translate.html

Then use ST_scale(geom,xFactor,yFactor) to scale the geometry based on what you need https://postgis.net/docs/ST_Scale.html

So the total transformation SQL would end up something similar to:

ST_Scale(ST_Translate(ST_Transform(geom,4236),x,y),xfactor,yfactor)
Mike T
  • 42,095
  • 10
  • 126
  • 187
Liam G
  • 2,156
  • 11
  • 11
  • thank you, but this does not solve the problem. I do not need new coordinates for each data point. I need to make sure that all polygon's bounding box is not -180..180, but instead some shifted value, like -360..0. This way the islands of Alaska would not be shown both on the leftmost and rightmost sides of the screen -- https://en.wikipedia.org/wiki/User:Yurik/maplink#/maplink/0/1/66/4 - for this map I would specify central longitude of -110, and the bounding box would be -290..70. If a polygon goes across that boundry, I would expect postgis to cut it into two. – Yuri Astrakhan Sep 13 '16 at 04:36
  • why can't you use @Freight_Train's solution with ST_Translate? That would shift the polygon based on any x offset that you specify. Use CASE WHEN if you only want to do this for polygons that cross 180 degrees. – amball Sep 13 '16 at 19:35
  • Your comment suggests that you want to cut the geometries as well, into parts that are in the E and W hemispheres. If so, use ST_Split, and cut them with a line that runs from (90,180) to (-90, 180). – amball Sep 13 '16 at 19:37
1

The OSM data is in 900913.

Um, no, it isn't, that's just how it is displayed on the web. Almost all the OpenStreetMap APIs and planet downloads (whether .osm.xml or .osm.pbf) have coordinates in WGS84 (EPSG 4326), and so does any PostGIS column created by osm2pgsql. (If you obtain the OSM data in some other way that results in a different SRS in the database, you might want to elaborate on that, so that alternatives can be suggested if appropriate.)

I need to get 4326, but so that the longitude's range is NOT -180..180, but rather some other, shifted range, based on a parameter. This way, if I need to draw an object near antimeridian (e.g. Alaska), I could request range of -360..0. The leaflet will be centered at -180, and there won't be any incomplete shapes.

In PostGIS ≥ 2.3 you can do that with ST_WrapX(geom, longitude_where_to_split_the_globe, -360), e.g. to get -360..0, use ST_WrapX(geom, 0, -360).

For older PostGIS versions, I don't know how to do this. (See also my own question Is there an inverse operation to ST_Shift_Longitude in PostGIS < 2.3?.)

das-g
  • 1,445
  • 2
  • 11
  • 33