Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- "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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement