Distance between two point layers

Question

I have a main addresses point layer and a areas point layer. Both layers have XYZ coordinates in the attribute table. I need to create a table based from the addresses layer which calculates the 3D distance to every area point. The output I'm after is similar to the table below

ADDRESSES | AREA1 | AREA2 | AREA3 | ...
addressA  | ..... | ..... | ..... | ...
addressB  | ..... | ..... | ..... | ...
addressC  | ..... | ..... | ..... | ...

I've written a custom script which does this but it gets very slow since I have around 15,000 address points and 6,000 areas. I've read here on GSE that PostGIS is good for large datasets so installed it. But I do not know to how to do the query to achieve what I'm after.

Answer 1

I suggest to try a distance matrix in QGIS, probably it's better optimized than your script.

http://www.qgistutorials.com/en/docs/nearest_neighbor_analysis.html Follow this guide but in "Distance Matrix" uncheck the option "Use only nearest (k) target points".

Also even if Postgis could do this faster, the difference will be not significant. Maybe breaking down data into smaller parts will be helpful.

Answer 2

you could use the distance_matrix in scipy to compute this

from scipy.spatial import distance_matrix
addr = np.random.random((15000,3))
pts = np.random.random((6000,3))

dst_mat = distance_matrix(addr,pts)
dst_mat.shape
(15000, 6000)

Not sure what types of speed you need, but the above example 3.3 seconds on my computer:

%%timeit
dst_mat = distance_matrix(addr,pts)
3.29 s ± 231 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)