Queries for OTM Webinar

queries.sql

"Beautiful In the Fall"
SELECT * FROM tree_export WHERE plot__address_zip = 19103 AND species__fall_conspicuous = TRUE

"Count Trees Per Neighborhood"
SELECT
hoods.mapname,
hoods.the_geom_webmercator,
hoods.cartodb_id,
COUNT(trees.cartodb_id) AS tree_count
FROM tree_export AS trees
JOIN neighborhoods_philadelphia AS hoods
ON ST_Intersects(trees.the_geom_webmercator, hoods.the_geom_webmercator)
GROUP BY hoods.mapname, hoods.the_geom_webmercator, hoods.cartodb_id
	
"Trees per Square Mile Per Neighborhood"
SELECT
hoods.cartodb_id,
hoods.the_geom_webmercator,
hoods.mapname,
COUNT(trees.cartodb_id) / (hoods.shape_area / 5280 / 5280) AS trees_mi2
FROM tree_export AS trees
JOIN neighborhoods_philadelphia AS hoods
ON ST_Intersects(trees.the_geom_webmercator, hoods.the_geom_webmercator)
GROUP BY hoods.mapname, hoods.cartodb_id, hoods.the_geom_webmercator

"Widest Tree per Species by Neighborhood"
SELECT *
FROM tree_export,
(SELECT 
MAX(trees.cartodb_id) AS treeid,
MAX(trees.diameter) AS tree_diam
FROM tree_export AS trees
JOIN neighborhoods_philadelphia AS hoods
ON ST_Intersects(trees.the_geom_webmercator, hoods.the_geom_webmercator)
GROUP BY hoods.mapname, trees.species__common_name) AS sub
WHERE tree_export.cartodb_id = sub.treeid