1

Clearly missing a basic concept. Using ST_Covers, I'm getting "true" for a geography point that clearly exists outside of the polygon. Looking at a number of points, it appears latitudes are vertically shifted, whereby points a bit North of the polygon are considered "in" and points just above the Southern border are erroneously considered "out".

For example, a single point in the database is defined as geography(Point,4326), with latitude longitude (45º, -79º).

Using ST_MakeEnvelope, create a polygon ranging N/S 40 to 42º, and E/W from -102º to -50º. The point "should" be just north of the resulting polygon.

However ST_Covers(ST_MakeEnvelope(-102, 40, -50, 42, 4326), location) returns true.

Yet ST_Covers(ST_MakeEnvelope(-102, 40, -50, 42, 4326), ST_Point(-79, 45, 4326)) returns false as expected.

You can see this in the following... shouldn't the location cast as geography(Point,4326) result in the same answer as the non-cast ST_Point?

(I'm storing global aviation positions & store locations as geography data types rather than geometry.)

    select 
      PostGIS_Version(),
      ST_Covers(ST_MakeEnvelope(-102, 40, -50, 42, 4326), ST_Point(-79, 45, 4326)) as AsPoint,
      ST_Covers(ST_MakeEnvelope(-102, 40, -50, 42, 4326), ST_Point(-79, 45)::geography(Point,4326)) as AsPointGeo

Results in:

version aspoint aspointgeo
3.2<...> false true
2
  • 3
    Great ellipse routes.
    – Vince
    Commented Feb 19, 2023 at 4:40
  • That seems so obvious, gonna check that!
    – pbuck
    Commented Feb 19, 2023 at 5:35

1 Answer 1

1

Not surprising, @Vince provided the useful prompt.

ST_Covers(a, b) works with geometry and with geography types. Both a & b must be (i.e., will be converted to be) the same type.

Geometry types would yield (my) expected results: If the envelope has a northern edge of 42º, then any point north of 42º would be "outside" the envelope.

Geography types are treated differently, though the envelope appears to be the same (i.e., maxy or max Latitude is 42º), the upper bound of the envelope is the great-circle curve between the two lat/lon points (42º, -102º) and (42º, -50º). The curve extends north of the 42º between the two endpoints and happens to cover my point (45º, -79º).

I didn't type the ST_Envelope (which is really just a Polygon). But, automatic type conversion causes:

ST_Cover(Polygon, point:geometry) => geometry calculation
   -but-  
ST_Cover(Polygon, point:geography) => geography calculation

So, one way to force the intended calculation, especially if the type of point is not know (or varies) is to explicitly cast:

ST_Cover(ST_MakeEnvelope(-102, 40, -50, 42, 4326), location::geometry)
   -or-
ST_Cover(ST_MakeEnvelope(-102, 40, -50, 42, 4326), location::geography)

With the location set to your preferred type, the Polygon is coerced to geometry or geography and the resulting ST_Covers uses the appropriate bounds.

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.