Counting points within range using CartoDB?

Question

I have a dataset of users location and want to assign per user how many other users they have within a given range, and updating this value into a table.The dataset name is "users_20160410" and the column to be updated is "countdistance"

I have manage to get the number of total users within 10km for a given point, which is a unique value:

UPDATE users_20160410 SET countdistance = (select count(*) from users_20160410 where st_distance(the_geom,st_GeomFromText('POINT(-3.703550 40.417192)', 4326), true) < 10000)

What I want is to know for every single user how many other user he has within a range of 10km (this means get a value per user, not 1 unique as per my previous query). Also as the distance is not a fixed given point, the st_Geom_From_Text could not be used as it should take the exact location per user.

Answer 1

You'll want to update from the results of another query. For performance it's best to do within the context of an indexed column, so you end up with some ugliness.

WITH sums AS (
 SELECT a.cartodb_id AS cartodb_id, Count(*) as count, sum(a.popn) as popn
 FROM populated_places_esp a
 JOIN populated_places_esp b 
 ON ST_DWithin(
 a.the_geom_webmercator, 
 b.the_geom_webmercator, 
 10000 / cos(radians(st_y(a.the_geom))))
 GROUP BY a.cartodb_id
)
UPDATE populated_places_esp 
 SET countdistance = sums.count
 FROM sums
 WHERE sums.cartodb_id = populated_places_esp.cartodb_id;

The normalization of the distance by cos(latitude) is to make up for distortions from the mercator projection.

• Alejandro: Query from @ramiroaznar works perfect thought is not updating the table. Paul, your query is giving me an error "column cartodb_id" is ambiguous. Here the used query: WITH sums AS ( SELECT a.cartodb_id AS cartodb_id, Count(*) as count, sum(a.id) as id FROM users_20160410 a JOIN users_20160410 b ON ST_DWithin( a.the_geom_webmercator, b.the_geom_webmercator, 10000 / cos(radians(st_y(a.the_geom)))) GROUP BY a.cartodb_id ) UPDATE users_20160410 SET countdistance = sums.count FROM sums WHERE sums.cartodb_id = cartodb_id

Answer 2

Using Spanish cities from populated_places as an example table, the following SQL query count the number of closest (100 Km) neighbors of each city:

SELECT 
p.name, COUNT(*) AS num_neigh 
FROM
populated_places_esp n,
populated_places_esp p 
WHERE
ST_DWithin(p.the_geom::geography, n.the_geom::geography, 100000) 
AND
p.cartodb_id != n.cartodb_id 
GROUP BY p.name 
ORDER BY num_neigh DESC