Geometry vs geography vs spheroid for polygon-contains-point (PostgreSQL)

Question

Many operations behave differently in geometry vs geography, e.g. the distance between two points.
But what if I only need to check whether a polygon contains a point?

Does that operation differ between geometry vs geography?
Does that operation differ between use_spheroid being false vs true?

user30184 · Accepted Answer

If you mean function ST_Contains https://postgis.net/docs/ST_Contains.html or ST_Within https://postgis.net/docs/ST_Within.html by the documentation they do not have a switch for use_spheroid and they can be used only with geometries, not with geographies. If you mean something else, please explain.
The question is relevant and the following query returns "false" even the point would be inside the polygon if they were geographies.
select
ST_Contains(
ST_GeomFromText('POINT ( -0.847 24.615 )'),
ST_GeomFromText('POLYGON (( 26.314 -32.134, -26.556 -9.337, 24.373 53.233, 69.24 16.855, 26.314 -32.134 ))'));

geozelot · Answer

Yes, and the issue is where the plane is bound to fail to represent the sphere.

As an overview of both types:

Is the Geometry(4326) an alias for Geography datatype?

As an example:
WITH
    ply(wkt) AS (
        VALUES ('SRID=4326;POLYGON((-45 0, 0 90, 45 0, -45 0))')
    ),
    pt(wkt) AS (
        VALUES ('SRID=4326;POINT(-45 45)')
    )
SELECT ST_Intersects(ply.wkt::GEOMETRY, pt.wkt::GEOMETRY) AS "with GEOMETRY(4326)",
       ST_Intersects(ply.wkt::GEOGRAPHY, pt.wkt::GEOGRAPHY) AS "with GEOGRAPHY(4326)"
FROM   ply, pt
;

with GEOMETRY | with GEOGRAPHY 
---------------+----------------
 f             | t

We  know that, on a sphere the POINT(-45 45) does, in fact, lie on the edge of the triangle defined by POLYGON((-45 0, 0 90, 45 0, -45 0)) - and the GEOGRAPHY data type is aware of that, too!
This, however, is what the GEOMETRY data type assumes:

where

the red dot is POINT(-45 45)
the red line is LINESTRING(-45 0, -45 90)
the red dashed line is LINESTRING(-45 0, 0 90)

Note that the two lines end in seemingly different end points, and the GEOMETRY type does take the different coordinates into acocunt; but of course, any point with latitude = 90° is the same point on a sphere!
Thus, on a sphere, the red line and the red dashed line are equal, and the POINT(-45 45) an element in both! But treated as planar coordinates, spherical equality is destroyed!

Some considerations:

the perceived magnitude of this effect is scale dependent: the smaller the scale, the larger the perceived impact
there are projection types that try to overcome this issue on a larger, regional scale

in theory, this effect may have a different impact when used with a spheroid (i.e. a geoid)

except for ST_Intersects, no spatial relationship function in PostGIS supports the GEOGRAPHY type; ST_Intersects uses a sphere as reference

Geometry vs geography vs spheroid for polygon-contains-point (PostgreSQL)

2 Answers

Add your own answers!

Ask a Question