OpenTreeMap SQL Summaries

readme.md

#Count Trees and display by wards descending

SELECT
COUNT(trees.gid) AS trees,
wards.gid AS ward_num
FROM edmonton_trees AS trees
JOIN edmonton_wards AS wards
ON ST_Intersects(trees.the_geom, wards.the_geom)
GROUP BY wards.gid 
ORDER BY trees DESC

#Count Trees and display by Neighborhoods descending

SELECT
hoods.hoodname,
COUNT(trees.gid) AS tree_count
FROM edmonton_trees AS trees
JOIN neighborhoods AS hoods
ON ST_Intersects(trees.the_geom, hoods.the_geom)
GROUP BY hoods.hoodname ORDER BY tree_count DESC

#Calculating density of trees per neighborhood

SELECT
hoods.gid,
hoods.the_geom,
hoods.hoodname,
COUNT(trees.gid) / hoods.area_km2 AS trees_km2
FROM edmonton_trees AS trees
JOIN neighborhoods AS hoods
ON ST_Intersects(trees.the_geom, hoods.the_geom)
GROUP BY hoods.hoodname, hoods.gid, hoods.the_geom
ORDER BY trees_km2 DESC

#Calculating density of trees per ward

SELECT
wards.gid,
wards.the_geom,
wards.ward,
COUNT(trees.gid) / wards.area_km2 AS trees_km2
FROM edmonton_trees AS trees
JOIN edmonton_wards AS wards
ON ST_Intersects(trees.the_geom, wards.the_geom)
GROUP BY wards.ward, wards.gid, wards.the_geom, wards.gid
ORDER BY trees_km2 DESC

#Widest Tree by Neighborhood

SELECT
hoods.hoodname,
MAX(trees.diameter) AS tree_diam
FROM edmonton_trees AS trees
JOIN neighborhoods AS hoods
ON ST_Intersects(trees.the_geom, hoods.the_geom)
GROUP BY hoods.hoodname, hoods.gid, hoods.the_geom
ORDER BY tree_diam DESC

#Widest Tree by Neighborhood by Species

SELECT *
FROM edmonton_trees,
	(SELECT 
		MAX(trees.gid) AS treeid,
		hoods.hoodname,
		trees.species__c,
		MAX(trees.diameter) AS tree_diam
	FROM edmonton_trees AS trees
	JOIN neighborhoods AS hoods
	ON ST_Intersects(trees.the_geom, hoods.the_geom)
GROUP BY hoods.hoodname, trees.species__c) AS sub
WHERE edmonton_trees.gid = sub.treeid
AND WHERE edmonton_trees.species__c = 'Pine'