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?