Find points within a box bound using PostGIS

Question

I'm trying to use PostgreSQL and PostGIS to return locations within a specific box bound.

Example

• User has a map view on Google Maps

• Google API returns box bounds using map.getBounds() - in my specific issue, the bounds from google maps are

  north: "51.52070000000002", 
  south: "51.52070000000002", 
  west: "-0.13450000000000184", 
  east: "-0.13450000000000184"
  

  and I'm using ST_MakeEnvelope() like so:

  ST_MakeEnvelope(-0.13450000000000184, 51.52070000000002, 
                  -0.13450000000000184, 51.52070000000002, 
                  4326)
  

• I have a locations table in my db with columns longitude, latitude and geom where geom is indexed with GIST and created by ST_SetSRID(ST_MakePoint(longitude::numeric,latitude::numeric), 4326);

• The SQL query just to sense check is

  select geom && ST_MakeEnvelope(-0.13450000000000184, 51.52070000000002, 
                                 -0.13450000000000184, 51.52070000000002, 
                                 4326)
  

  where geom is SRID=4326;POINT(-0.1309 51.5162) and which should return true but returns false instead

• I also had this query

  SELECT   id,     
           ST_Y(ST_MakePoint(longitude, latitude)) AS y,
           ST_X(ST_MakePoint(longitude, latitude)) AS x 
  FROM     map_locations ml
  WHERE    ST_SetSRID(ST_MakeBox2D(ST_Point(latitude, longitude), 
                                   ST_Point(longitude, latitude) ),          
                      4326)  
        && ST_MakeEnvelope(-0.13450000000000184, 51.52070000000002, 
                           -0.13450000000000184, 51.52070000000002, 
                           4326) 
  

  but these created box bounds in the incorrect places.

• I also tried ST_WITHIN() and ST_CONTAINS() with the same result

What am I doing wrong?

Answer 1

I formatted the question body to make this a bit more visible: the coordinates you said you got from map.getBounds() make up a box with zero area - it's effectively a point, as immediately pointed out by @user30184.

For that to match with &&, the geom in your map_locations would have to happen to be that exact same point, a line (more or less) running through it, a polygon that covers that spot or a collection that includes one of those.

---

That ST_MakeBox2D() could cover 12.5%-25% of the surface of the globe in some cases:

ST_MakeBox2D(ST_Point(latitude, longitude), 
             ST_Point(longitude, latitude) )

ST_Point() accepts lon/lat:

geometry ST_Point(float x, float y);
…
For geodetic coordinates, X is longitude and Y is latitude

So on the North Pole:

with cte as (select st_setsrid(ST_MakeBox2D(ST_Point(90,0),
                                            ST_Point(0,90)),
                               4326) AS geom )
select st_astext(geom),
       st_area(st_transform(geom,3857)) as area_in_3857,
       st_area(geom::geography(Polygon,4326)) as area_as_geography
from cte;

st_astext	area_in_3857	area_as_geography
POLYGON((0 0,0 90,90 90,90 0,0 0))	2.429835233887958e+15	63758202715511.07

It still won't necessarily && with your geom, and if it does, I'm not sure that's really what you wanted to find.