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.

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

2 Answers 2

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
  • 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.
    – Regina Obe
    Mar 16, 2015 at 14:30
  • @LR1234567 Hmm...Yes, you are correct. Mar 16, 2015 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 and acknowledge you have read our privacy policy.

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