[GIS] Performing bounding box query in PostGIS

Question

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.

Answer 1

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.