0

I've already checked all previous similar question. I'm asking again just because I need to be more specific by describing the database structure I am working with.

CREATE TABLE stores (name varchar, geom geometry);
CREATE INDEX geoid ON stores USING gist((geom::geography))

INSERT INTO stores VALUES ('Store name', 'ST_SetSRID(ST_MakePoint(16.123, 51.321), 4326)')
/* where 16.123 is longitude and 51.321 is latitude */

Until now we only perform search-in-radius (circle) queries, with the following sintax

SELECT ST_X(geom) as longitude, ST_Y(geom) as latitude FROM stores WHERE
ST_DWithin("stores"."geom", ST_MakePoint(15, 50)::geography, 10000)
/* where 15 is longitude, 50 is latitude and 10000 is the radius in meters */

As stated in the title, now we also need to perform some query to get all stores within a bounding box, starting from lat_north, lng_east, lat_south, lng_west.

Should I use the ST_DWithin function again? Maybe with the ST_MakeEnvelope. I'm also confused by the use of the geography cast in the "radius" query.

2
  • 3
    With one of the geometries passed in as GEOGRAPHY, the other is auto-cast to GEOGRAPHY under the hood. This allows for proximity searches in meter rather than unit of CRS (degrees in this case). This is reflected by the index you added.
    – geozelot
    Jun 10, 2020 at 10:59
  • It's faster to load the data and then index it
    – Ian Turton
    Jun 10, 2020 at 12:06

1 Answer 1

2

Please, please make sure you add a spatial index to your table, then you can use the && operator.

SELECT ST_X(geom) as longitude, ST_Y(geom) as latitude FROM stores 
WHERE geom && ST_MakeEnvelope(left, bottom, right, top, 4326)
5
  • I would emphasize that && makes comparison by bounding boxes and while it gives exact result for points it may select some additional geometries from other geometry types.
    – user30184
    Jun 10, 2020 at 10:44
  • Actually I forgot to mention the spatial index. Check the question, I've just edit
    – radar155
    Jun 10, 2020 at 10:56
  • could you please clarify how to use the && operator in this particular situation? Unfortunately, the example in the link you provided is not clear to me
    – radar155
    Jun 10, 2020 at 11:32
  • @user30184 - OP has points and boxes && will work just fine
    – Ian Turton
    Jun 10, 2020 at 12:03
  • 2
    Sure yes but it does not make harm to know that with linestrigs and polygons && may lead to extra hits. Some other user may apply this answer to another problem in the future.
    – user30184
    Jun 10, 2020 at 12:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.