0

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

1
  • Welcome to gis.SE. Questions should demonstrate what investigation you've already done, what you've already tried on the basis of that research, what worked / didn't work, and what happened instead. The reason why this is important is that we can then provide something specific. As written, your question says "tell me what I should know". That isn't reasonable - we don't have enough people answering questions to do write custom tutorials for every question. Please try to make your question more specific.
    – BradHards
    Dec 1, 2013 at 21:05

1 Answer 1

0

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

1
  • Thank you so much! it helped me to solve my problem. Best regards Mar 15, 2014 at 8:00

Your Answer

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

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