Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a Postgres table, with almost 2 million rows, with a long-lat coordinates field in the form POINT(-73.4938 33.2405). Supposing there's a geospatial index on that field, what's the most efficient, fastest way to select all the rows within an arbitrary bounding box? The box is like SW long-lat: -74.0042 40.7688, NE long-lat: -73.8809 40.7984.

share|improve this question
Are your stored coordinates already long-lat or are they grid (X, Y)? – Martin F Jan 17 '14 at 21:38
Simple mathematics would do here... If point.x is bigger than SW.x and smaller than NE.x and point.y is bigger than SW.y and smaller than NE.y at the same time, the point lies inside the MBR. I do not know whether it is faster than using spatial query though. You mind to try? – Michal Zimmermann Jan 18 '14 at 8:44
@zimmi: He doesn't actually state that the items are just points; they could be complex geometries. – Martin F Jan 18 '14 at 18:00
They are just points, though ;-). They're long-lat in the form POINT(-73.4938 33.24059) stored as WKB. – Avishai Jan 19 '14 at 18:40
I edited the Q (and my A) to reflect that info. :-) – Martin F Jan 20 '14 at 18:19
up vote 8 down vote accepted

Assuming the given bounding box limits are in the same spatial reference system as the stored coordinates, and you know which spatial operator (intersects or contained by) you need:

FROM   my_table
WHERE  coordinates 
    && -- intersects,  gets more rows  -- CHOOSE ONLY THE
    @ -- contained by, gets fewer rows -- ONE YOU NEED!
    ST_MakeEnvelope (
        xmin, ymin, -- bounding 
        xmax, ymax, -- box limits

Alternatively, if you prefer the sound of "contains" (instead of "contained by") the WHERE clause should be flipped:

WHERE  ST_MakeEnvelope (...)
    ~ -- contains, gets same fewer rows 

PS: Given (by OP after the above was posted) that the records are simple points, I think that the difference between "intersects" and "containment" becomes very subtle, affecting only the points on the edges of the bounding box.

share|improve this answer
that's a good point. Contains should be fine, since you won't really be able to see a map marker if it's on the boundary (ie, the browser chrome probably). – Avishai Jan 20 '14 at 18:36

Apparently, I don't have enough points to add a comment so I am using this Answer just to say that I tried both ST_MakeEnvelope vs the maths compare of "x > min_x and x < max_x and y > min_y and y < max_y" ...on average ST_MakeEnvelope took 60ms and maths compare took 155ms on my particular bbox query.

So the spatial search ST_MakeEnvelope should be faster than maths compare!

share|improve this answer
SELECT ST_Y(the_geom) AS latitude, ST_X(the_geom) as longitude
from units u where the_geom && ST_MakeEnvelope(left, bottom, right, top, 4326)
share|improve this answer
Not necessary to say 4326 is the SRID. – Magno C Jan 17 '14 at 17:01

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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