Database

Database

Initialize a database

psql
CREATE DATABASE seattle;
CREATE EXTENSION postgis;
\q

Rebuild Table

Create Incidents Table and Add Data

git checkout develop
cd <something>/project/data/
psql -d seattle
DROP TABLE incidents;
/q
psql -d seattle -a -f create_incidents_table.sql 
psql -d seattle
\copy incidents(units,date_time,incident_type,address,incident_number,latitude,longitude,major_category,minor_category) FROM 'datav2_incident_cat.csv' DELIMITERS ',' CSV QUOTE AS E'"';
\q
psql -d seattle -a -f create_incidents_point_geom.sql

Create Neighborhood Table and Add Data

git checkout develop
cd <something>/project/data/
shp2pgsql -I -s 4326 ZillowNeighborhoods-WA/ZillowNeighborhoods-WA.shp  neighborhoods_wa | psql -d seattle

If you have older than Feb 24th 12:30p, you don't need todo this. Alter table columns names.

alter table incidents rename type to incident_type;
alter table incidents rename datetime to date_time;

Queries

Select by Bounding box

Selects all incidents within the specified bounding box.

SELECT *
FROM incidents
WHERE 
  incidents.geom && 
  ST_MakeEnvelope(minLon, minLat, maxLon, maxLat, 4326);

via https://gis.stackexchange.com/questions/60700/postgis-select-by-lat-long-bounding-box

Select by Circle

Selects all incidents within the specified circle with radius, R.

SELECT * 
FROM incidents 
WHERE ST_Point_Inside_Circle(incidents.the_geom, <long>, <lat>, <radius>);
ie:
WHERE ST_Point_Inside_Circle(incidents.the_geom, -122.336072, 47.623636, 0.001);

via http://gis.stackexchange.com/questions/67836/how-to-select-all-points-within-a-circle-in-postgis

Hexbins

• Hexbins!
• Hexbinning
• Hexbins out of PostGIS

Additional resources

Postgres & PostGIS

• Introduction to PostGIS
• Importing large datasets into postgis
• Populating PostGIS databases with the copy command
• Adding spatial indexes to PostGIS tables
• Understanding PostGIS Spatial Queries
• Postgres Guide

Data

• SFD Unit descriptions