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

[OlegSerov]

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;

2. 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

[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.

[OlegSerov]

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

[OlegSerov]

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.

[pramsey]

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.

[OlegSerov]

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