Postgis: Query ST_DWithin in square instead of circle

Question

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.

Answer 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.

Answer 2

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.