[GIS] PostGIS: incorrect interpretation of a polygon that intersects the 180th meridian

postgispostgresql

I try to find out all geopoints which intersect the polygon set as a parameter.

The problem is when I pass polygon that roughly covers an area of Bering Strait (nearby 180 longitude):
enter image description here

So I use the query:

SELECT ST_AsText(l.geo_point)
FROM "lightnings" "l"
WHERE (ST_Intersects(ST_GeomFromText('Polygon((132.0 40.0, -148.0 40.0, -148.0 -8.0, 132.0 -8.0, 132.0 40.0) )', 4326), geo_point));

As you can see, vertexes are set in a correct order, clockwise, from the North-West. But the result covers outside area and including whole other world.

For example, in result:

POINT(75.5637 40.0434)

The problem doesn't touch the 0th meridian.

Elementary test:

SELECT ST_Area(ST_GeomFromText('Polygon((0.0 60.0, 10.0 60.0, 10.0 40.0, 0.0 40.0, 0.0 60.0) )', 4326))
UNION ALL
SELECT ST_Area(ST_GeomFromText('Polygon((-5.0 60.0, 5.0 60.0, 5.0 40.0, -5.0 40.0, -5.0 60.0) )', 4326))
UNION ALL
SELECT ST_Area(ST_GeomFromText('Polygon((175.0 60.0, -175.0 60.0, -175.0 40.0, 175.0 40.0, 175.0 60.0) )', 4326))

gives the result:

200
200
7000

Is there any simple trick to force PostGIS understanding me? I don't like an idea to divide the polygon…

Best Answer

PostGIS has a built in function ST_Shift_Longitude(geom) to return Lon/Lat geometries as 0-360 degrees instead of -180 - 180.

Wrap your polygons in this, and the resulting area calculations should be OK.

Description here

BTW, I guess you realize that your ST_Area calculation is giving square degrees. Not really useful... If you use the new Geography data type, (ST_GeographyFrom Text(...) ) then the Area calculation will be in meters.

Addition:

Here's what I get

geodata=# SELECT ST_Area(
   ST_GeomFromEWKT('SRID=4326;POLYGON((
   -179.5 0, 179.5 0, 179.5 1, -179.5 1, -179.5 0))')
   );

 st_area 
---------
     359
(1 row)

geodata=# SELECT ST_Area(
   ST_SHIFT_Longitude(
   ST_GeomFromEWKT('SRID=4326;POLYGON((
   -179.5 0, 179.5 0, 179.5 1, -179.5 1, -179.5 0))')));

 st_area 
---------
       1
(1 row)
Related Question