4

I have a table of devices with a column last_known_coordinates (geography(Point,4326)).

I want to find devices within a bounding box with a query. Some bounding boxes are working, but others are not - it seems the bigger they get, at some point they stop working. To test this, I tried to search (almost) the whole world:

SELECT "device".* 
FROM   devices AS "device" 
WHERE  (ST_Intersects("device"."last_known_coordinates", ST_MakeEnvelope(179, -89, -179, 89)::geography))

This brings back none of the records, where I would expect all to be returned. Why aren't all records returned?

Improve this question

1 Answer 1

Reset to default
6

The whole world polygon is not supported by the PostGIS geography type as documented in https://postgis.net/docs/postgis_usage.html#PostGIS_Geography_AdvancedFAQ

4.1.2.3.3.

What is the longest arc you can process?

We use great circle arcs as the "interpolation line" between two points. That means any two points are actually joined up two ways, depending on which direction you travel along the great circle. All our code assumes that the points are joined by the shorter of the two paths along the great circle. As a consequence, shapes that have arcs of more than 180 degrees will not be correctly modelled.

Improve this answer
2
  • 1
    Just adding for OPs reference: this does not apply to the GEOMETRY type. But that type has plenty other limitations.
    – geozelot
    Commented Sep 1, 2021 at 8:26
  • Thank you. So for arcs of more than 180, I suppose we'll need to break the bounding box in (at least) two.
    – Tony
    Commented Sep 1, 2021 at 14:27

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.