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?

  • Can you share the code of what you already did?
    – tilt
    Aug 22, 2017 at 12:05
  • Share our table schemas. Or sample schemas. Or, CREATE TABLE statements. Something. Aug 22, 2017 at 15:42

2 Answers 2


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.



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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.