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?

• user30184: If you look at the coordinates of what you get with getBounds you'll notice that they do not make a box but a point.
• Ian Turton: -0.33 does not fall between -0.13 and -0.13
• Kenn Seangpachareonsub: 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

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.

• Kenn Seangpachareonsub: Thanks for answering! and your point about the getBounds() makes sense. With this then, how would i go about finding those matching locations within the coordinates that i get back from the getBounds function?
• Zegarek: @KennSeangpachareonsub My guess would be that you accidentally called .getNorthEast() twice (or .getSouthWest() twice) on the LatLngBounds you got from getBounds(), which is why you get the same lat/lon - could be something else, but you're not showing that part of your solution. If you fix how you get the bounds so that you don't re-use one corner, you'll get different points, which will let you construct a non-zero-area box/envelope. Then a && or st_intersects() will be able to find whatever you got in that area.
• Zegarek: If for some reason you can't fix .getBounds() getting you the same corner twice, you'll have to figure out whether that's the NE or SW and I guess you could reconstruct the bounds by generating the other corner by shifting the one you have by .getZoom(). Problem is, things like this or ST_Expand()ing around the one point you have might result in not quite the same bounds, hence not quite the same shapes getting caught by the subsequent &&. Plus, that's patching a bug you probably could remove at the source, by figuring out why you're getting just one corner from .getBounds().
• Zegarek: If you make sure you're not re-using a corner, then maybe .setZoom() was too high, or you're making the bounds dependent on a set of points containing just a single point, like here. I'm not sure max zoom would result in zero-area bounds, though.
• Kenn Seangpachareonsub: thanks for all this, actually ended up figuring it out a minute a go. For some reason getBounds returns the same corner for the west and east coordinates. Doing getBounds().getSouthWest() and .getNorthEast() ended up doing the job and returned different values for all 4 corners