1

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
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.

builder-map

|improve this answer|||||
0

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!
;
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy