In PostGIS, the function ST_DWithin automatically includes a bounding box comparison that will make use of any indexes that are available on the geometries. This is done using the mechanism of inlining SQL functions. The definition of the function is as follows.
CREATE OR REPLACE FUNCTION ST_DWithin(geom1 geometry, geom2 geometry, float8)
RETURNS boolean
AS 'SELECT $1 OPERATOR(@extschema@.&&) @extschema@.ST_Expand($2,$3)
AND $2 OPERATOR(@extschema@.&&) @extschema@.ST_Expand($1,$3)
AND @extschema@._ST_DWithin($1, $2, $3)'
LANGUAGE 'sql' IMMUTABLE _PARALLEL;
Why it is necessary to call twice the ST_Expand function, one for each geometry? Wouldn't a single call like in the following definition be enough?
CREATE OR REPLACE FUNCTION ST_DWithin(geom1 geometry, geom2 geometry, float8)
RETURNS boolean
AS 'SELECT $1 OPERATOR(@extschema@.&&) @extschema@.ST_Expand($2,$3)
AND @extschema@._ST_DWithin($1, $2, $3)'
LANGUAGE 'sql' IMMUTABLE _PARALLEL;
geom1
or the table forgeom2
. We can't take advantage of a spatial index for ageom
that's inside of a function call (likeST_Expand($2, $3)
, so writing the condition both ways allows a query to use an index on eithergeom1
orgeom2
's table. – dbaston Sep 28 '17 at 14:46