How could you calculate the distance between multiple associated points, specifically a list of structures serving specific addresses within CartoDB?

Question

I have loaded two point datasets into CartoDB. The first is a list of structures serving specific addresses, each structure has a unique code associated to it as well as the location of the structure itself. The other point dataset is a list of the addresses and their locations, each address having the code of the structure that serves it.

I know how to calculate the distance of each structure from each address, however this seems quite inefficient when all I require is the distance between the structure serving the address in question (the other structures I'm not worried about for any given address).

Note I only require the great circle (haversine) distance. Also, if the same query can determine which of the addresses lie X distance (in km) away from the structure serving it that would be great.

Any ideas on how you'd go about this within CartoDB/PostgreSQL?

Answer 1

You can use Connect with Lines BUILDER analysis:

• First, add this analysis to the structures layer.
• Secondly, set the addresses layer as TARGET.
• Third, select All to all option.
• Fourth, group by the common field (unique code). Apply.

A new length column will be added to your layer. You can export the results or add some widgets to gain more insights. I have built a simple map to show the result with country capitals as structures and the rest of cities as addresses using populated_places dataset from the Data Library.

Answer 2

I think what you want is

SELECT *, ST_Distance(a.point, s.point)
FROM addresses AS a
JOIN structure AS s
  ON a.serviced_by_structure = s.serviced_by_structure
  AND ST_DWithin(a.point,s.point,x) -- in meters!
;