0

The ST_DWithin query queries all geometries within a certain radius from another geometry (in my case a point). For performance optimization of my application I want to query in a square. So that the query gives me back all geometries within a certain square of my point. Is this possible? Because I don't find any kind of query to this. My geometries are polygons/multipolygons.

  • Yeah, it's possible. This is just a trivial envelope search. – Vince Mar 16 '15 at 14:04
  • @Vince: Indeed, that's exactly what I need! Thanks – beirre Mar 16 '15 at 15:54
1

If we look at postgis source code(geography,geometry) we see that the function ST_DWithin is defined as:

CREATE OR REPLACE FUNCTION ST_DWithin(geom1 geometry, geom2 geometry, float8)
    RETURNS boolean
    AS 'SELECT $1 && ST_Expand($2,$3) AND $2 && ST_Expand($1,$3) AND _ST_DWithin($1, $2, $3)'
    LANGUAGE 'sql' IMMUTABLE;

the _ST_DWithin($1, $2, $3) is the expensive part so just use ST_EXPAND in your query like:

WHERE geom && ST_EXPAND(geom2,dist)

The original query has a check with both being expanded to avoid an edge case so you might want to add it if you want to be really precise. because we don't know which one can use index (as noted by @LR1234567 below).

If really do want it to be an actual square and not a general box2d it might not perform well.

  • 2
    The double expand is not to avoid an edge case. It's there because only one version can take advantage of an index, and we can't know which one that would be without knowing the details of the query. – LR1234567 Mar 16 '15 at 14:30
  • @LR1234567 Hmm...Yes, you are correct. – Jakub Kania Mar 16 '15 at 14:37
0

Thanks guys, I found and easy and quick solution using the following query: SELECT * FROM mytable WHERE mytable.geom && ST_MakeEnvelope(minX, minY, maxX, maxY);

My question was already answered somewhere else but I didn't use the correct search terms.

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.