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.