Tyler Dahlberg TDahlberg

#What is PostGIS and why should I care? ###PostGRES An open-source relational database management system (RDBMS). ###PostGIS An extension for PostGRES databases that adds the ability to store spatial data, and special functions to query spatial data.

###Advantages Using a PostGIS-enabled PostGRES database to store spatial data allows you to:

Store massive amounts of data
Work on files concurrently without corrupting them

#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

#How to add Google Streetview images to CartoDB points ##Add points to CartoDB ##Run the following code in the SQL editor:

SELECT *, 
'http://maps.googleapis.com/maps/api/streetview?size=300x190&location='||
ST_Y((the_geom))||','||ST_X((the_geom))||'&sensor=false&fov=110' as image 
FROM tablename

##Set the infowindow popup as "image header" type, select "image" as the title field

What Has Data Analytics Been Up To?

##Blogs

#Boundless Geo Exercise 11 ##What neighborhood and borough is Atlantic Commons in? Solution:

SELECT name, boroname
FROM nyc_neighborhoods
WHERE ST_Intersects(
  the_geom,(SELECT the_geom FROM nyc_streets WHERE name = 'Atlantic Commons'))

	"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

	#! /usr/bin/env python

	#######################################
	# GDALCalcNDVI.py
	#
	# A script using the GDAL Library to
	# create a new image containing the LST
	# of the original DN value from Landsat
	# 5 or 7 imagery.
	#