Search code examples
postgis

ST_intersects geography

I am trying to figure out something, I am using postgis 2.3 on postgres 9.6

ST_intersects on Geography returns false although two geographies are intersecting. In order to debug I tried the same query using Geometry and the two intersected.

Can someone help me to explain that?

Example:

I am running this query:

select ST_intersects(ST_GeogFromText('POLYGON((12.856 41.890,12.856 41.935,12.811 41.935,12.811 41.890,12.856 41.890))'), ST_GeogFromText('POLYGON((-170 -47,174 -47,174 77,-170 77,-170 -47))'))  from table limit 1;

I get:

 st_intersects 
---------------
 f
(1 row)

and when I run:

 select ST_intersects(ST_GeomFromText('POLYGON((12.856 41.890,12.856 41.935,12.811 41.935,12.811 41.890,12.856 41.890))'), ST_GeomFromText('POLYGON((-170 -47,174 -47,174 77,-170 77,-170 -47))')) from  table limit 1;

I get:

 st_intersects
 ---------------
  t
 (1 row)
Solution

  • ST_Intersects -- returns True if Geometries/Geography spatially intersect in 2D. Your queries should return same result if you convert from POLYGON (in your case with 5 vertices) to LINESTRING. Test: 

    SELECT 
 ST_Intersects(
 ST_GeomFromText('SRID=4326;LINESTRING(12.856 41.890,12.856 41.935,12.811 41.935,12.811 41.890,12.856 41.890)'), 
 ST_GeomFromText('SRID=4326;LINESTRING(-170 -47,174 -47,174 77,-170 77,-170 -47)'));


SELECT 
 ST_Intersects(
 ST_GeogFromText('SRID=4326; LINESTRING(12.856 41.890,12.856 41.935,12.811 41.935,12.811 41.890,12.856 41.890)'), 
 ST_GeogFromText('SRID=4326; LINESTRING(-170 -47,174 -47,174 77,-170 77,-170 -47)'));

    Update::

    Actually, using Polygon is just fine for ST_Intersects. I realize that Linestring isn't what you want -- returns false because one is completely inside the other. The issue is that these two polygons are far apart in spherical coordinate system. Check:

    SELECT 
 ST_Distance(
 ST_GeomFromText('SRID=4326;POLYGON((12.856 41.890,12.856 41.935,12.811 41.935,12.811 41.890,12.856 41.890))'), 
 ST_GeomFromText('SRID=4326;POLYGON((-170 -47,174 -47,174 77,-170 77,-170 -47))'));

    ==> 0

    SELECT 
 ST_Distance(
 ST_GeogFromText('SRID=4326;POLYGON((12.856 41.890,12.856 41.935,12.811 41.935,12.811 41.890,12.856 41.890))'), 
 ST_GeogFromText('SRID=4326;POLYGON((-170 -47,174 -47,174 77,-170 77,-170 -47))'));

    ==> 6743507.32

    You can of course cast geography to geometry type as follows to get the desired output:

    SELECT 
 ST_Intersects(
 ST_GeogFromText('SRID=4326;POLYGON((12.856 41.890,12.856 41.935,12.811 41.935,12.811 41.890,12.856 41.890))')::geometry, 
 ST_GeogFromText('SRID=4326;POLYGON((-170 -47,174 -47,174 77,-170 77,-170 -47))')::geometry);