6

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;
  • This is more of a database than GIS question, and would likely be better suited to Database Administrators or a PostgreSQL developer forum. – Vince Sep 28 '17 at 11:26
  • Good question. It looks like it's been this way since the function was introduced: trac.osgeo.org/postgis/changeset/2647 – dbaston Sep 28 '17 at 11:46
  • Bring it up on their mailing list. – lynxlynxlynx Sep 28 '17 at 12:35
  • 3
    It's written this way because we don't know if we could have a spatial index on the table for geom1 or the table for geom2. We can't take advantage of a spatial index for a geom that's inside of a function call (like ST_Expand($2, $3), so writing the condition both ways allows a query to use an index on either geom1 or geom2's table. – dbaston Sep 28 '17 at 14:46
  • 5
    This question is as GIS as it can be IMO. – tinlyx Sep 28 '17 at 15:58
2

The reason I have understood it is, this:

The index forks by using 1 geometry from 1 of the input data sets and testing it against the index of the other data set. So, the expanded geometry is tested against the index of the other data set.

If there were only 1 of the gometries that was expanded the planner would not be able to use the index if it decides to test the other way. If it takes the not expanded geometry and test against the expanded geometry it will not be able to use the index (there is no indexes on the expanded boxes).

So, it is about what the planner chooses to put in the inner and outer loop when testing.

Let's say you have one data set with 3 geometries and one with 1 million. Then PostgreSQL will try to check the 3 geometries against the index in the data set of 1 million. Not the other way. Then if the data set with 1 million geometries happen to be the one with 1 million geometries, the planner have to decide if it should check all 1 million geometries against an index with 3 entries. Since the index makes no sense when there it only 3 geometries to search it will probably go the other way and check 1 by 1 of the tree geometries against the 1 million (expanded) geometries even if it cannot use the index.

With the construction in PostGIS now the planner is free to choose both ways with index search.

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.