1

I would like to create a postgresql function to correct geographic coordinates inside a polygon that are outside the -180 to 180 range of longitudes.

To do this, I thought I would create a function to loop through all the points in a polygon and do the correction.

However, I don't see a method for extracting the nth point in a polygon, only a linestring with ST_PointN.

Is this possible and/or am I going about this the correct way.

  • Doesn't PostGIS automatically correctly handle the +/- 180 meridian problem? – Martin F Jan 22 '15 at 3:20
  • When I try to convert to a geography I get an out of range error (see my comment below). I wonder if it is because of my PostGIS version? I have: "POSTGIS="2.0.3 r11132" GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER" – maw269 Jan 22 '15 at 21:34
2

For PostGIS 2.1, you can just convert the geometry to a geography type, where longitudes are always normalised to the -180/+180 range. For example, take these coordinates that are outside the range, and compare the normalised output:

SELECT ST_AsText(geom::geography::geometry)
FROM (SELECT 
  'POLYGON((180.12 -16.69,180.00 -16.80,179.89 -16.95,
            179.92 -17.02,180.18 -16.79,180.12 -16.69))'::geometry AS geom
) AS f;

POLYGON((-179.88 -16.69,180 -16.8,179.89 -16.95,179.92 -17.02,-179.82 -16.79,-179.88 -16.69))
  • This is my issue that started this question. When I run your example, I get--> ERROR: Coordinate values are out of range [-180 -90, 180 90] for GEOGRAPHY type SQL state: 22023. What version of PostGIS are your using? I have: "POSTGIS="2.0.3 r11132" GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER" – maw269 Jan 22 '15 at 21:18
  • This is a feature for PostGIS 2.1. For older versions a more complicated method needs to be done... – Mike T Jan 22 '15 at 22:03
  • 1
    Here is a post on upgrading to PostGIS 2.1 which doesn't seem to bad: gis.stackexchange.com/questions/69496/… – maw269 Jan 24 '15 at 19:33
  • I would like to report that I was able to upgrade to PostGIS 2.1 and converting to the geography type normalized my coordinates. – maw269 Jan 26 '15 at 20:06
2

You can use ST_DumpPoints and iterate over its results. It should be exactly what you're looking for.

  • I looked into this and am struggling with writing a function, but this seems like a legitimate way to go about it. – maw269 Jan 23 '15 at 16:19
  • Yeah, upon furter inspection it doesn't look that easy, since some of the candidate functions only work on linestrings. At worst you could dump to WKT, edit and reconvert, but that's so ugly I almost abstained from typing it. – lynxlynxlynx Jan 23 '15 at 17:23
  • I still think the ST_DumpPoints function would work but I am having trouble with Postgres arrays. If I figure it out I will post the function I created. – maw269 Jan 24 '15 at 19:35
  • How did you plan to move the vertices? – lynxlynxlynx Jan 25 '15 at 6:54
  • Unfortunately I haven't gotten there yet, fortunately I was able to upgrade to PostGIS 2.1 without incident and that took care of my issue as stated in the answer above. – maw269 Jan 26 '15 at 20:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.