3

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)
  • Can you show the ST_AsEWKT(GEOG) for a couple of points? – BradHards Nov 27 '13 at 22:29
6

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.