I have a PostgreSQL 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.


3 Answers 3


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.

  • 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, 2014 at 18:36
  • What's the fastest ...? : OP
    – Magno C
    Dec 12, 2016 at 15:53
  • 3
    Be aware: && and @ don't seem to work when intersecting with polygon Geometry. In this case, use ST_Intersects(latlng_column,ST_GeomFromText('Polygon ((...))',4326)) or alternatively ST_Contains
    – Alex
    Feb 1, 2018 at 11:50
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)

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!

  • 7
    Actually, if you create the right indexes min_x, max_x, min_y and max_y will be much faster. I have a very large dataset (over 3 million polygons) and did both INDEX over ST_MakeEnvelope and (ST_XMax, ST_XMin, ST_YMax, ST_YMin) and the difference is hugely in favor to math. Math took me less than 20s (INDEX + Query) while Envelope intersection took over 2min (I gave up when it reached 2min, 40s only for Spatial indexing) Jul 3, 2017 at 18:15
  • @caiohamamura "create the right indexes" would you elaborate with an answer to this question?
    – Him
    Jan 31 at 14:01
  • I'm not sure if this is still true, when I first comment it was still in PostgreSQL 10 and not sure which PostGIS version. Probably this still holds true, since envelope will perform more checks than simply math. The indexes you need to create are BTREE for the x, y coordinates, and for the coordinates (provided you convert it to geometry with ST_GeomFromText) your only option is GIST. Jan 31 at 16:36

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.