Opened 7 years ago

Closed 7 years ago

#3805 closed defect (wontfix)

No way to create geography that croses -180..180 (date-line)

Reported by: OlegSerov Owned by: pramsey
Priority: medium Milestone: PostGIS 2.3.5
Component: postgis Version: 2.3.x
Keywords: Cc:

Description

  1. Alaska vs alberta:

SELECT ST_GeogFromText('POLYGON((-116.614 61.7534,-109.804 61.7534,-109.804 57.5029,-116.614 57.5029,-116.614 61.7534))') && (ST_GeogFromText(' POLYGON((172.347846 71.441059,-173.233993 71.441059,-158.815832 71.441059,-144.397672 71.441059,-129.979511 71.441059,-129.979511 51.175092,-144.397672 51.175092,-158.815832 51.175092,-173.233993 51.175092,172.347846 51.175092,172.347846 71.441059))'));

Returns TRUE, should return FALSE;

  1. 2 parts of Alaska vs alberta

SELECT ST_GeogFromText('POLYGON((-116.614 61.7534,-109.804 61.7534,-109.804 57.5029,-116.614 57.5029,-116.614 61.7534))') && ST_GeogFromText('MULTIPOLYGON(((-179.999990 71.441059,-167.494870 71.441059,-154.989750 71.441059,-142.484631 71.441059,-129.979511 71.441059,-129.979511 51.175092,-142.484631 51.175092,-154.989750 51.175092,-167.494870 51.175092,-179.999990 51.175092,-179.999990 71.441059)),((172.347846 71.441059,179.999990 71.441059,179.999990 51.175092,172.347846 51.175092,172.347846 71.441059)))');

Returns TRUE, should return false

Version: PostgreSQL 9.3.17 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit POSTGIS="2.3.2 r15302" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.9.1" LIBJSON="0.11.99" RASTER

Change History (8)

comment:1 by pramsey, 7 years ago

Resolution: invalid
Status: newclosed

The fact that the 3-space volumetric bounds of alaska and alberta intersect (true) does not mean that the actual geographies do, as can be seen by running the exact test.

SELECT ST_Intersects(ST_GeogFromText('POLYGON((-116.614 61.7534,-109.804
 61.7534,-109.804 57.5029,-116.614 57.5029,-116.614 61.7534))'),  
 (ST_GeogFromText(' POLYGON((172.347846 71.441059,-173.233993
 71.441059,-158.815832 71.441059,-144.397672 71.441059,-129.979511
 71.441059,-129.979511 51.175092,-144.397672 51.175092,-158.815832
 51.175092,-173.233993 51.175092,172.347846 51.175092,172.347846
 71.441059))')));

The && operator is not comparing the things you think. These aren't squares drawn on a map, they are volumes in R3 that contain the shapes in S2 described by your geographies. If you need a correct result, use the actual ST_Intersects() function.

comment:2 by OlegSerov, 7 years ago

Let's assume that you are right in the fact that && should somehow work differently from "ST_Intersects".

But there is still an issue with ST_Intersection:

SELECT ST_Intersection(ST_GeogFromText('POLYGON((-116.614 61.7534,-109.804 61.7534,-109.804 57.5029,-116.614 57.5029,-116.614 61.7534))'),
 (ST_GeogFromText(' POLYGON((172.347846 71.441059,-173.233993
 71.441059,-158.815832 71.441059,-144.397672 71.441059,-129.979511
 71.441059,-129.979511 51.175092,-144.397672 51.175092,-158.815832
 51.175092,-173.233993 51.175092,172.347846 51.175092,172.347846
 71.441059))')));
ERROR:  Error performing intersection: TopologyException: Input geom 1 is invalid: Self-intersection at or near point -19284319.888845518 6619014.660591769 at -19284319.888845518 6619014.660591769
CONTEXT:  SQL function "st_intersection" statement 1

comment:3 by OlegSerov, 7 years ago

Resolution: invalid
Status: closedreopened

in reply to:  1 comment:4 by OlegSerov, 7 years ago

Replying to pramsey:

The fact that the 3-space volumetric bounds of alaska and alberta intersect (true) does not mean that the actual geographies do, as can be seen by running the exact test.

SELECT ST_Intersects(ST_GeogFromText('POLYGON((-116.614 61.7534,-109.804
 61.7534,-109.804 57.5029,-116.614 57.5029,-116.614 61.7534))'),  
 (ST_GeogFromText(' POLYGON((172.347846 71.441059,-173.233993
 71.441059,-158.815832 71.441059,-144.397672 71.441059,-129.979511
 71.441059,-129.979511 51.175092,-144.397672 51.175092,-158.815832
 51.175092,-173.233993 51.175092,172.347846 51.175092,172.347846
 71.441059))')));

The && operator is not comparing the things you think. These aren't squares drawn on a map, they are volumes in R3 that contain the shapes in S2 described by your geographies. If you need a correct result, use the actual ST_Intersects() function.

Please, direct me to the documentation / code where that is described. EDIT: Ok, I found the function: https://github.com/postgis/postgis/blob/0706464c2e384bb05be3eb4af5bd909d1dcb7843/postgis/gserialized_gist_nd.c#L450 So it is actually compares if two bounding boxes intersects. BUT! I still think that does not work because it cannot get proper bounding box for this polygon.

Last edited 7 years ago by OlegSerov (previous) (diff)

comment:5 by pramsey, 7 years ago

You're right, there's not any explicit documentation on the meaning of && in the geography context, mostly because it's damnable hard to explain.

With respect to ST_Intersection(), note that it's a bad hack on top of transforms to planar (have a look at the SQL definition for that function), so the fact that it doesn't work over the dateline doesn't entirely surprise me.

If your conclusion is that "geography implementation in postgis isn't very complete/good" I have no strong argument with you. It's… better that not having one. But only somewhat.

comment:6 by OlegSerov, 7 years ago

Thanks for the reply. I think, these cases should be added to the unit tests.

comment:7 by pramsey, 7 years ago

Milestone: PostGIS 2.3.4PostGIS 2.3.5

comment:8 by pramsey, 7 years ago

Resolution: wontfix
Status: reopenedclosed
Note: See TracTickets for help on using tickets.