Visualizing information when click in polygon‏ - Spatial join

Question

I am new to cartodb, right now I am trying to do something similar to the cartodb tutorial: Create your first map with CartoDB.js, but with polygons using my own map.

For example:

I did a spatial join with two tables, one has the coordinates and the other one the polygons, once I do the spatial join, three or two polygons appear in the screen of the map, one of them containing 10 rows from the table and the other 20 rows. I want to visualize the count based on this after I click the polygon.

I was wondering if you could help me with some information or tutorial to be able to visualize it?

Thanks a lot.

Best regards,

Walter

Answer 1

Depending on what exactly your starting query is will change the right answer quite a bit. As will whether you are hoping to get the result using the CartoDB dashboard or if you are using CartoDB.js to host your map.

If your starting query is as follows

SELECT 
    a.cartodb_id, count(*) as count_of_b, a.the_geom, a.the_geom_webmercator 
FROM 
    table_1 a, table_2 b 
WHERE 
    ST_Intersects(a.the_geom, b.the_geom) 
GROUP BY 
    a.cartodb_id, a.the_geom, a.the_geom_webmercator

The answer is easy and will work in either CartoDB dashboard and CartoDB.js. For inside the CartoDB dashboard, simply run the query then inside the map view, modify your infowindow to include the newly returned 'count_of_b' value. count_of_b will be the sum of all table_2 rows that intersect the given table_1 geometry.

Now, if you're doing something like this,

SELECT 
    a.cartodb_id, b.cartodb_id as b_cartodb_id, a.the_geom, a.the_geom_webmercator 
FROM 
    table_1 a, table_2 b 
WHERE 
    ST_Intersects(a.the_geom, b.the_geom)

We have something different. Because you say each table_1 data is 'containing 10 rows' from table_2, I think this is what you are doing. It isn't necessarily wrong, but realize that you are stacking duplicate versions of table_1 for each time it intersects a row from table_2. To now know how many rows it contains can only be done with a follow-up SQL statement and so can only be done with CartoDB.js

You would run a follow-up sql statement after your on-click event. You could use the cartodb_id of table_1 to filter it, so pretending a user clicked on the polygon where cartodb_id = 1, your follow-up sql request would be,

SELECT 
    count(b.cartodb_id) 
FROM 
    table_1 a, table_2 b 
WHERE 
    a.cartodb_id = 1 
    AND 
    ST_Intersects(a.the_geom, b.the_geom)

Hope that helps