[postgis-tickets] [PostGIS] #5437: ST_Intersects polygon geography empty results

[PostGIS]

#5437: ST_Intersects polygon geography empty results
-----------------------------------+---------------------------
  Reporter:  royjacksonalertmedia  |      Owner:  pramsey
      Type:  defect                |     Status:  new
  Priority:  medium                |  Milestone:  PostGIS 3.3.4
 Component:  postgis               |    Version:  3.3.x
Resolution:                        |   Keywords:
-----------------------------------+---------------------------
Comment (by robe):

 Unfortunately PostGIS has no function to check validity in geography
 space.  I checked in SQL Server which does have an IsValid check for
 geography and it deemed your alaskan/US boundary to be invalid. However
 running STMakeValid on it did make it intersect.  So I fear this is just a
 limitation of how we assume shortest length is line in the geography
 plumbing.

 @pramsey you see anything that can be done about this or is it just a
 hopeless case?

 I checked the usual suspect of different distances:


 {{{
 SELECT ST_Distance(shape::geography, geog),
 _ST_DistanceTree(shape::geography, geog)
 , _ST_DistanceUncached(shape::geography, geog) --, shape, geog
 FROM threat_polygon_shapes_layer_valid,
 public.ST_setSRID(public.ST_GeogFromWKB('\x01030000000100000005000000386094f6ff7f66c000415442d9a05040386094f6ff7f66c0047fa5b145435540fcffffffff7f56c0047fa5b145435540fcffffffff7f56c000415442d9a05040386094f6ff7f66c000415442d9a05040'),'4326')
 AS f(geog);
 }}}

 {{{
  st_distance   | _st_distancetree | _st_distanceuncached
 ----------------+------------------+----------------------
  32968.11078731 |   32968.11078731 |    32968.11078730672
 (1 row)
 }}}

 and it really thinks they don't intersect regardless if I use the
 validated one or original invalid. I get the same answer using the
 original geometry as well as the one I had converted to valid in SQL
 Server using


 {{{
 select objectid,shape.MakeValid().STDistance(
 geography::STGeomFromWKB(0x01030000000100000005000000386094f6ff7f66c000415442d9a05040386094f6ff7f66c0047fa5b145435540fcffffffff7f56c0047fa5b145435540fcffffffff7f56c000415442d9a05040386094f6ff7f66c000415442d9a05040,'4326')
 ) from threat_polygon_shapes_layer where
 (id='faee6fc1d8f2f583577f85c54da3900b') AND
 shape.MakeValid().STIntersects(geography::STGeomFromWKB(0x01030000000100000005000000386094f6ff7f66c000415442d9a05040386094f6ff7f66c0047fa5b145435540fcffffffff7f56c0047fa5b145435540fcffffffff7f56c000415442d9a05040386094f6ff7f66c000415442d9a05040,'4326')
 ) = 1 ORDER BY objectid ASC;
 }}}

 SQL Server says they should intersect and distance is 0.
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5437#comment:2>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.