Counting event points by categories and matching to polygons, normalized by population

Question

I'm new to CartoDB, but I'm a seasoned user of ArcGIS. My university was sorely lacking in GIS training; I only learned how to create static maps, not web content. After graduating 3 years ago, I'm playing major catch-up.

Anyhow, I have two primary datasets: Events (points) and categories (table).

The events are points in New York City of events such as "Yoga Meetup, Location: Central Park, 10 am on 6/1/2014" or "Python Pals, Location: Tech Center, 6 pm on 6/15/2014."

Each unique ID from the event table is listed in the concept table. The concept table tags every event by category - for example "Sports and Recreation" (category 1) or "Technology" (category 2) and so on. Some events have null categories because they haven't been tagged yet.

I want to link the categories to the events, and then count how many events fall under each category.

Next, I want to link these points to 5 different polygon datasets: NYC boroughs, City Council districts, Census tracts, and so on. There is a column in each table for the population of each polygon.

The goal is to count how many event points of each category are within each type of polygon -- normalized by population.

The end result is to see which neighborhoods are hosting the most events for each issue. Which borough has the most "Sports" events by population? Breaking it down further, which Census tracts have the most events by population?

Thanks for any help you can provide!

-S.G.

Answer 1

you could do something like this:

SELECT a.neighborhood_name,count(b.event_id) AS event_count
FROM "tblNeighborhoods"  a
LEFT JOIN "tblEvents" b USING (neighborhood_id)
WHERE a.neighborhood_id = x 
GROUP BY a.neighborhood_name

and so on depending on what group you're trying to count

another possibility would be to create additional linkage tables on the containing the ids of the tables containing the categories and the instances you're trying to count. in this case you'd create your linkage table with 2 columns, one to contain each id. then you could select a count from the linkage table like so:

SELECT b.category_name, count(a.instance_id) as instance_count 
FROM "linktable" a
LEFT JOIN "categorytable" b USING(category_id) 
WHERE a.category_id = x
GROUP BY b.category_name