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 '17 at 12:05
  • Share our table schemas. Or sample schemas. Or, CREATE TABLE statements. Something. – Evan Carroll Aug 22 '17 at 15:42

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, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.