2

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) ?

David Leimbach
  • 21
  • 2

1 Answers1

1

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.

Paul Ramsey
  • 839
  • 6
  • 8
  • It's really hard to imagine all this shapes, but at least one program must be wrong: PostGIS or the command-line-tool from NASA that reprojected the files to GeoTif, since both produce different borders for the same file (and then different results for intersect with the second shape). The problem is that I can't see what PostGIS is really doing, I can only use the dumper to get a shapefile, but of course that doesn't have to be the same shapes PostGIS is working with internally when using geography. – David Leimbach Mar 06 '13 at 08:29
  • If I'm interpreting what you're saying, the shapes themselves are just squares. Or more precisely, they are objects defined by four corners. What differs between software is how it interprets the lines that join those corners. – Paul Ramsey Mar 06 '13 at 20:57
  • Yes, I have 4 cornerpoints. And as far as I understood, geometry should connect them with direct lines like on a plane, while geography should connect them with parts of big circles around the globe. The dumper gives me a shapefile in which the points are connected with direct lines, while the reprojected files show borders that are part of big circles. What I dont know is what PostGIS is doing internally when doing the intersect with 2 geographys. – David Leimbach Mar 08 '13 at 11:59
  • The lines between the points are interpreted by software, they aren't inherent in the file format. The dumper to shapefile gives you four (well, five) points. Whatever software you view that file in interprets the connections between those points however it likes. – Paul Ramsey Mar 08 '13 at 17:34
  • Give me the corner coordinates of two squares that you think PostGIS geography is misinterpreting. – Paul Ramsey Mar 08 '13 at 17:36
  • I cant reproduce them right now, but maybe I found an explanation: "ST_Intersection — (T) Returns a geometry that represents the shared portion of geomA and geomB. The geography implementation does a transform to geometry to do the intersection and then transform back to WGS84." ( http://postgis.refractions.net/docs/ST_Intersection.html ) If ST_Intersects() also does a transform into geometry, it would explain everything. – David Leimbach Mar 27 '13 at 15:08
  • It does not. ST_Intersects(geom, geom) works in spherical space. No transform. – Paul Ramsey Mar 28 '13 at 13:48