3

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.

  • 1
    Your query is on a cast. This means a full table scan to evaluate each row. – Vince Jun 16 '16 at 23:20
8

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) );

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.