[GIS] ST_DWithin: Why does query not use the Spatial Index

postgis

I want my query to output all street segments, that are within a distance of 20 meters of those segments which have the attribute clazz of 11 (motorways).

I have this query from a user on this site:

SELECT DISTINCT s.id, s.clazz
FROM berlin_2po_4pgr s
INNER JOIN berlin_2po_4pgr m 
ON ST_DWithin(s.geom_way::geography, m.geom_way::geography, 20)
WHERE m.clazz = '11' AND s.clazz != '11';

I have a index:

CREATE INDEX gist_index_berlin_2po_4pgr ON berlin_2po_4pgr USING gist(geom_way);

The query takes a very long time (more than two minutes). Probably the reason is that the index isn't used:

"HashAggregate  (cost=8076781.04..8076781.13 rows=9 width=8)"
"  ->  Nested Loop  (cost=0.00..8076781.00 rows=9 width=8)"
"        Join Filter: (((s.geom_way)::geography && _st_expand((m.geom_way)::geography, 20::double precision)) AND ((m.geom_way)::geography && _st_expand((s.geom_way)::geography, 20::double precision)) AND _st_dwithin((s.geom_way)::geography, (m.geom_way)::g (...)"
"        ->  Seq Scan on berlin_2po_4pgr s  (cost=0.00..2267.43 rows=49871 width=101)"
"              Filter: (clazz <> 11)"
"        ->  Materialize  (cost=0.00..2270.24 rows=563 width=93)"
"              ->  Seq Scan on berlin_2po_4pgr m  (cost=0.00..2267.43 rows=563 width=93)"
"                    Filter: (clazz = 11)"

Why? The SRID is 4326, so unit is degrees. But the cast to geography changes the units to meters, right?

The output is correct, so unit can't be the problem.

Best Answer

You index is on geometry not geography and you are casting your geometry to geography.

If you want it to use an index, you need to add a geography index like so:

CREATE INDEX gist_geog_berlin_2po_4pgr ON berlin_2po_4pgr USING gist( (geom_way::geography) );

Related Solutions

[GIS] Improve performance of a PostGIS st_dwithin query

The explain doesn't show an index coming into play, which could be for two reasons:

You don't have one. So make one with CREATE INDEX tree_gix ON trees USING GIST (geom)
Your data is in geographic coordinates, so your spatial join isn't really doing anything selective (it's joining every tree to all other trees, every time). In that case, either (a) change to using the geography type or (b) move your data to an appropriate planar projection (I recommend (b)).

Finally, yeah, the LEFT JOIN is not doing anything useful for you unless there are lonely trees with no partners in the radius you need to keep in the result set. I'd remove the geometric and species GROUP BY as well, since you already have a unique id in there, the other variables are just noise.

SELECT 
  a.tree_id, a.species, avg(b.age) as age_avg, 
  count(*) as samples, a.geom
FROM trees a 
JOIN trees b
ON ST_DWithin(a.geom, b.geom, 100) AND a.species = b.species
WHERE a.age IS NULL
GROUP BY a.tree_id;

Other than the first two possible errors above, the query itself looks pretty neat and clean.

[GIS] How to use St_intersects with different geometry type

Fast query results for ST_Intersects hinge on the fact that not every pair of inputs needs to be tested. PostGIS avoids testing every pair of geometries by implicitly testing the arguments to ST_Intersects with the bounding box intersection operator &&, so that only geometries whose bounding boxes intersect need to be passed to ST_Intersects. When your geometry columns are indexed, PostgreSQL can use the index to fetch only geometries that pass the && filter, significantly reducing the number of comparisons.

Here's the problem. The index provides the bounding boxes of a.geom and b.geom, but not ST_Centroid(a.geom). You and I know that whenever a.geom && b.geom is true, then ST_Centroid(a.geom) && b.geom must also be true, but PostgreSQL has no way to know this.

You can fix this by manually forcing an a.geom && b.geom comparison, which can take advantage of the index.

SELECT a.id, b.id 
FROM PolygonLayer1 a, PolygonLayer2 b
WHERE a.geom && b.geom AND ST_Intersects(ST_Centroid(a.geom), b.geom)

This doesn't explain why you're getting good performance in Case 1, because I have no idea.

Best Answer

Related Solutions

[GIS] Improve performance of a PostGIS st_dwithin query

[GIS] How to use St_intersects with different geometry type

Related Question