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
    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 at 10:59
  • It's faster to load the data and then index it – Ian Turton Jun 10 at 12:06
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)
| improve this answer | |
  • 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 at 10:44
  • Actually I forgot to mention the spatial index. Check the question, I've just edit – user41124 Jun 10 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 – user41124 Jun 10 at 11:32
  • @user30184 - OP has points and boxes && will work just fine – Ian Turton Jun 10 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 at 12:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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