3

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?

Improve this question
3
  • 5
    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. Commented Mar 24, 2024 at 22:00
  • -0.33 does not fall between -0.13 and -0.13 Commented Mar 25, 2024 at 9:40
  • 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 Commented Mar 25, 2024 at 10:20

1 Answer 1

Reset to default
3

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.

Improve this answer
5
  • 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? Commented Mar 26, 2024 at 19:39
  • @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. Commented Mar 26, 2024 at 20:08
  • 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(). Commented Mar 26, 2024 at 20:15
  • 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. Commented Mar 26, 2024 at 20:30
  • 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 Commented Mar 26, 2024 at 20:55

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.