I've got two datasets - 1) a set of buildings in LA and 2) fiber optic cable maps from a company called Zayo which covers the whole of the US. I'd like to find the buildings within a certain distance to the fiber. The SRID is 4326 and it's stored as a geometry, not geography (as far as I can tell).

Here's my code:

select *
from la_records
inner join zayo_fiber
on ST_DWithin(zayo_fiber."geom", la_records."geom", .001);

The code looks to work, but it takes forever - 10 minutes. Here's the Explain:

Nested Loop  (cost=0.21..43.81 rows=1 width=3167517)
->  Seq Scan on zayo_fiber  (cost=0.00..1.05 rows=5 width=3167299)
->  Index Scan using sidx_la_records_geom on la_records  (cost=0.21..8.54 rows=1 width=218)
    Index Cond: (geom && st_expand(zayo_fiber.geom, '0.001'::double precision))
    Filter: ((zayo_fiber.geom && st_expand(geom, '0.001'::double precision)) AND _st_dwithin(zayo_fiber.geom, geom, '0.001'::double precision))
Time: 0.045s

To speed up this query, should I break up the zayo fiber into smaller subsegments, then index it?


So the very long multi-line-segment from zayo_fiber was causing the efficiency issue. I ended up using ST_Dump to break it up into 55,000 rows, which I then indexed. The query now runs in less than a second.

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.