0

According to ChatGPT (which obviously could be very wrong) geometry type will always calculate based on Cartesian space even when the CRS is 4326 coordinates, whereas geography type will calculate based on polar coordinates on a spheroid which should be more accurate to the real world. But some tests I've done show the opposite. enter image description here

Here I drew a large polygon (white) in the far north and dropped some pins to test if they intersect. Using geography type none of them did except the one in the top left which is in the red outline polygon. The red outline is actually the exact same polygon feature just with no styling defined so google earth just goes from point to point. So it seems geography type is using this red outline for its calculations.

So which is more accurate? It seems that geometry is actually better and geography should be used sparingly. Is that correct?

PostGIS queries to check for pins intersecting polygon:

 SELECT a.*
 FROM non_customer_sensitive_pins2 a
 LEFT JOIN non_customer_sensitive_polys2 b
 ON ST_Intersects(a.geometry::geography, b.geometry::geography)
 WHERE b.geometry IS NULL

 SELECT a.*
 FROM non_customer_sensitive_polys2 a
 LEFT JOIN non_customer_sensitive_pins2 b
 ON ST_Intersects(a.geometry::geography, b.geometry::geography)
 WHERE b.geometry IS NULL

This kml should replicate the red rectangle in google earth (but if you click out the vertices to draw the poly then it will look like the white one):

<?xml version="1.0" encoding="utf-8" ?>
<kml xmlns="http://www.opengis.net/kml/2.2">
<Document id="root_doc">
<Folder><name>super_big_polys</name>
  <Placemark>
<name>poly_89</name>
<Style><LineStyle><color>ff0000ff</color></LineStyle><PolyStyle> 
   <fill>0</fill></PolyStyle></Style>
  <Polygon><outerBoundaryIs><LinearRing> 
   <coordinates>-130.976710772947,58.6412935354303,0 
    -56.9909440674777,52.1429295601783,0 
    -56.9836026755277,54.9142638530325,0 
    -130.821268166601,59.9586613053789,0 
    -130.976710772947,58.6412935354303,0</coordinates></LinearRing> 
   </outerBoundaryIs></Polygon>
  </Placemark>
    </Folder>
    </Document></kml>
2
  • Since PostGIS can't plot, how did you generate this graphic, and what exact SQL did you use to do these tests?
    – Vince
    Jun 27, 2023 at 17:14
  • @Vince I just added the queries into the question. As for the visualization, the white polygon was drawn in google earth just by clicking the 4 vertices. the red one was from taking that kml and modifying it so that google earth doesn't curve the edges, so same polygon but the edges go from vertex t vertex on a flat screen (not sure why google does this).
    – tbob
    Jun 27, 2023 at 17:22

1 Answer 1

3

There a several misconceptions here:

  • Google Earth is not "flat". It uses some transformation so the earth looks like a sphere. Consequently, we can't compare a flat map with it. The red polygon that you have displayed would be a curved polygon on a flat map.

To illustrate the spher-ish display, see how western Canada looks like when the view is centered on eastern Canada.

enter image description here

  • When using geographies, a line is drawn as a great circle, not a straight line. A great circle is the line at the intersection of earth surface and an imaginary plan going through the 2 coordinates and earth center. Therefore, on the northern hemisphere, the curvature is toward the north. The following pictures shows your polygon. In brown it is displayed as geography, in grey as geometry (straight lines). Back to the previous point, they are the same polygons as in google earth screenshot, but put on a flat sheet instead of on a sphere!

enter image description here

  • Computation in Postgis when using geographies is not always done with geographies... and this point is a huge problem in my opinion. Some functions will first transform the geographies to geometries using "the best" possible projection. This "best" projection can be UTM but quickly degrades to world projection when the source geography is too wide. It is therefore possible to have a true st_intersects (done in geography), but nevertheless an empty st_intersection (done after transforming to geometry). This is poorly documented and you would have to look at the code to find out is the function you are using is done in geography or in geometry via the "best" SRID (search for the function of interest and look if it uses @extschema@._ST_BestSRID or not).
with src(geog1,geog2) as (
 select 'POLYGON((-150 50, 0 50,  0 60, -150 60, -150 50))'::geography, 
 'POLYGON((-150 61, 0 62,  0 62, -150 62, -150 61))'::geography)
SELECT st_intersects(geog1,geog2), st_asText(st_intersection(geog1,geog2))
FROM src;
    
 st_intersects |   st_astext
---------------+---------------
 t             | POLYGON EMPTY
(1 row)

So to answer your question, both geometry and geography are as accurate, but they model different things on earth. Before deciding which one to use, you must understand the difference between the two, their limitations, and compare that with your needs.

PS: This post shows alternatives such as segmenting the geometries or using a gnomonic projection)

1
  • Thanks, I was familiar with how the display differs from google earth to a flat projection, but thank you for going through that explanation. That last point is really what I was wondering about. I'm wondering what the real benefit to using geography is besides the instances where I need to buffer in meters. It seems like geometry type is better for doing other calculations, intersects in particular.
    – tbob
    Jun 27, 2023 at 20:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.