I have a two tables containing (1) all parishes in Denmark (centroids) and (2) a road network of Denmark (OSM data).
I am trying to find the nearest road for each parish centroid. To do this I'm running the following code.
Another thing that worries me is that even though the query is running, the amount of available space on the drive is not decreasing. That is, it appears that the query is not writing to a temporary file.
Any suggestions for improvements or explanations why the query is running this slow?
أكثر...
I am trying to find the nearest road for each parish centroid. To do this I'm running the following code.
create table sogne_way as
select distinct on(b.id) b.id as id_sogn, a.id as id_road, a.geom_way, a.source, a.target, b.geom
FROM sogne b join dk_2po_4pgr a
on ST_DWithin(b.geom, a.geom_way, 5000)
order by b.id, ST_Distance(a.geom_way, b.geom);
The query is, however, extremely slow. It's getting close to 24 hours (hasn't finished yet). There is about 2200 parish centroids. I've tried running it for subsets of the data and it works fine. The run time does increase with the size of the subset but.select distinct on(b.id) b.id as id_sogn, a.id as id_road, a.geom_way, a.source, a.target, b.geom
FROM sogne b join dk_2po_4pgr a
on ST_DWithin(b.geom, a.geom_way, 5000)
order by b.id, ST_Distance(a.geom_way, b.geom);
Another thing that worries me is that even though the query is running, the amount of available space on the drive is not decreasing. That is, it appears that the query is not writing to a temporary file.
Any suggestions for improvements or explanations why the query is running this slow?
أكثر...