ST_DWithin Optimization Postgis

Question

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?

some of this info might help with optimizing: http://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

score 1 · Answer 1 · answered Dec 20 '16 at 18:42

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.

answered Dec 20 '16 at 18:42

kingzing1

183
1
8

ST_DWithin Optimization Postgis

1 Answers1