1

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?

  • how far is .001? – DPSSpatial Dec 19 '16 at 19:10
  • some of this info might help with optimizing: gis.stackexchange.com/a/194576/72697 – kttii Dec 19 '16 at 19:29
  • As per the Tour there should be only one question asked per question. – PolyGeo Dec 19 '16 at 19:43
  • PolyGeo - Edited the question - thanks for pointing that out to me – kingzing1 Dec 19 '16 at 21:20
1

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.