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.
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:
SELECT * 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 my_srid) 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 coordinates 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.
What's the fastest ...? : OP && 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 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!
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) x, y coordinates, and for the coordinates (provided you convert it to geometry with ST_GeomFromText) your only option is GIST.