[GIS] Maximum size on the bounding box with ST_MakeEnvelope and && and geography column in PostGIS

extentsgeography-data-typeintersectionpostgis

I've done quite a few google searches, but haven't managed to find anything that has helped me directly. I've got a few features in a PostGIS table with a geography column. The features are focused around (latitude, longitude) 0, 100 and 1, 101 (those are literally the points for any POINTS or LINESTRINGS in that column).

I'm making a query like this:

SELECT GUID, ATTRS, ST_AsGML(GEOG), ACTIVE, CREATED, FTIME 
FROM FEATURES 
WHERE GEOG && ST_MakeEnvelope(99, -3, 103, 3, 4326) 
 AND ACTIVE = TRUE ORDER BY CREATED DESC LIMIT 100

And it returns all the data

If I change the query to be much larger, then nothing comes back:

SELECT GUID, ATTRS, ST_AsGML(GEOG), ACTIVE, CREATED, FTIME 
FROM FEATURES 
WHERE GEOG && ST_MakeEnvelope(-75, -80, 110, 3, 4326) 
  AND ACTIVE = TRUE ORDER BY CREATED DESC LIMIT 100

So is there a limit I'm hitting somewhere here I don't know about it?

If I change the query to this:

SELECT GUID, ATTRS, ST_AsGML(GEOG), ACTIVE, CREATED, FTIME 
FROM FEATURES 
WHERE GEOG && ST_MakeEnvelope(-70, -80, 109, 3, 4326) 
  AND ACTIVE = TRUE ORDER BY CREATED DESC LIMIT 100

I still get all my data back!

EDIT

Added the ST_AsEWKT(GEOG) as requested:

select ST_AsEWKT(GEOG) from features;
             st_asewkt             
-----------------------------------
 SRID=4326;POINT(100 0)
 SRID=4326;POINT(100 0)
 SRID=4326;POINT(100 0)
 SRID=4326;POINT(100 0)
 SRID=4326;POINT(100 0)
 SRID=4326;POINT(100 0)
 SRID=4326;POINT(100 0)
 SRID=4326;LINESTRING(100 0,101 1)
 SRID=4326;POINT(100 0)
 SRID=4326;POINT(100 0)
 SRID=4326;LINESTRING(100 0,101 1)
 SRID=4326;LINESTRING(100 0,101 1)
 SRID=4326;LINESTRING(100 0,101 1)
 SRID=4326;LINESTRING(100 0,101 1)
 SRID=4326;LINESTRING(100 0,101 1)
 SRID=4326;LINESTRING(100 0,101 1)
 SRID=4326;POINT(100 0)
 SRID=4326;POINT(100 0)

Best Answer

Envelopes and the geography type are almost always a bad mixture, since they are not comparable (i.e., Cartesian vs spherical). Your results are probably expected if you see them on a round globe.

With the smaller envelope, I see Sumatra: enter image description here

But, with the larger envelope, it goes through the South Pacific: enter image description here

If you need to select features within a box-shaped envelope, cast it to geometry, e.g.:

SELECT guid, attrs, ST_AsGML(geog), active, created, ftime
FROM features
WHERE geog::geometry && ST_MakeEnvelope(-75, -80, 110, 3, 4326) AND active
ORDER BY created DESC
LIMIT 100;

Do this if your map is flat, an you expect envelopes to behave in Cartesian space.

Related Question