PostGIS: ST_Intersects() between two geographies, sometimes returns false result

2
votes

I have a problem with an intersect between two geographies in PostGIS: I have a table1 with a geography-column, filled with multi-polygons in wgs84 (one per line). A second table2 also has a geography-column filled with multi-polygons in wgs84 (one per line). Then I do a intersect (select (..) where ST_Intersects()) between both.

In some (very rare) cases it seems to me, that it doesn't work correct because the intersect returns false where a true is expected: I have a reprojected tif representing the geography from one line in table1, it covers a big area in Tanzania. I also have a reprojected tif for one line in table2, it only covers a small area of some km². In ArcMap, both intersect, the small area is on the edge of the big, but PostGIS returns false for ST_Intsersects(geog1, geog2).

When I export table1 as a shapefile via the dumper, the corner-points of the geography are connected with direct lines, not with parts of big circles. Then I get the same result: The area from table2 now is outside the area from table1. So I guess, PostGis also calculates with this simplified polygon, but as far as I understood geography is meant to calculate with parts of big circles instead of direct lines ?

I already tried everything to ensure that both tables are really filled with geography and not with geometry, but even a explicit cast to geography didn't change the results.

Did something like this happen before or do you have a idea what I did wrong ? Could it be a problem that table2 also has an geometry and a raster-column (=3 spatial columns alltogether) ?

1
postgisgeographyintersect
what about the SRIDs? Did you check them out, are they same, for the both. - azzaxp
Yes, the srids for the geography-cols are the same in both tables, 4326 for WGS84. - David Leimbach

1 Answers

1
votes

Without seeing your actual geometries, I cannot say for certain, but almost certainly you are misinterpreting how the great circle lines will bound your area, and PostGIS is getting it right, particularly if you're dealing with relationships near the boundaries. Using Google Earth line strings (not polygons, those are not rendered using great circles) to visualize examples can be helpful sometimes in clarifying how things are working visually.