ST_Intersects with the whole world doesn’t bring back all results

postgisst-intersects

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?

Best Answer

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.

Related Question