Filter, Aggregate, and Combine

Taking Advantage of SQL Queries for Enterprise Databases

Spring NEARC - May 15, 2015

Guido Stein - Applied Geographics, Inc. / @guidos

More Presentations

Welcome

Guido Stein

Guido Like Burrito

• GIS Anlayst @ Applied Geographics
• AvidGeo Meetup Group and Conference Organizer
• Model Builder, Python, & FME Junky
• Open Source NewB
• Database NewB

Overview

Philosophy 101

We are not alone, data and big data winter is coming

Enterprise G DB

• Server Based
• Accessible from more than one place
• Multi-User Editing
• SQL Data Processing
• SQL Geospatial Data Processing

Esri Documentation on PostGIS

• PostgreSQL database requirements for ArcGIS 10.3.x
• Set up a geodatabase in PostgreSQL on Windows
• PostgreSQL data types supported in ArcGIS
• Spatial SQL queries on tables with an ST_Geometry column
•  

Which Version of PostGIS/PostgreSQL am I running?

Example 1 : PostgreSQL & PostGIS Version
                        SELECT PostGIS_full_version();-- v2.1.7
                        SELECT version();-- v9.4.3
                    

Create Database View Tool

CartoDB

http://cartodb.com

Open Source

Hands On #1

guidos.github.io

Setup an account with CartoDB

Add boundary data:
https://data.cityofboston.gov/download/af56-j7tb/application/zip

Add rodent data:
https://data.cityofboston.gov/api/views/ynt4-n6g9/rows.csv?accessType=DOWNLOAD

Add trash data:
https://data.cityofboston.gov/api/views/42qi-w8d7/rows.csv?accessType=DOWNLOAD

Structured Query Language (SQL)

                        SELECT table_alias.field_name AS field_alias
FROM table_name AS table_alias;-- Select Syntax
                        SELECT r.neighborhood AS name
FROM rodent AS r;-- returns a query instance with a field called name pulling populating
that field with the rodent table field neighborhood
                    

• *(wild card character)
• --(annotation preceding double dash)
• ;(end of statement)
• .(optional, sometimes)
• Reserved words do not need to be uppercase
• Whitespaces don't matter, readability is up to you.
• AS is optional

                        SELECT table_alias.field_name AS field_alias
FROM table_name AS table_alias
WHERE field_name = value;-- Where Syntax
                        SELECT r.neighborhood AS name
FROM rodent AS r
WHERE r.neighborhood = 'Dorchester';-- Filter results to include only where neighborhood is Dorchester
                    

• String Literal Values must be surrounded by single quotes
• WHERE expression must return a boolean value
• WHERE expression can be made of multiple statements connected with AND or OR
• WHERE expression is optional

                        SELECT table_alias.field_name AS field_alias
FROM table_name AS table_alias
WHERE field_name = value
GROUP BY field_name;-- Group By Syntax
                        SELECT r.neighborhood AS name, COUNT(*) as row_count
FROM rodent AS r
WHERE r.neighborhood = 'Dorchester'
GROUP BY r.neighborhood;-- Aggregate to Dorchester record with count of entries
                    

                        SELECT table_alias.field_name AS field_alias
FROM table_name AS table_alias
LEFT OUTER JOIN table_name AS table_alias 
  ON table_name.join_field = table_name.join_field-- Left Outer Join By Syntax
                        SELECT r.*, b.acres as _acres
FROM rodent AS r
LEFT OUTER JOIN boundary as b
  ON r.neighborhood = b.name;-- Get Acres of neighborhood for each point
                    

Hands On #2

1. sql to esri translation
1. select - make layer/view
2. where - table select
3. group by - table summary
4. left outer join - table join

Where is the geometry?

• ST_Geometry DataType
• shape
• geom
• CartoDB
• the_geom_webmercator

Thanks OGC

Geospatial Field

Geometry Joins

• ST_Contains
• ST_Crosses
• ST_Equals
• ST_Intersects
• ST_Overlaps
• ST_Within

                        SELECT r.*
FROM rodent as r, boundary as b
WHERE ST_Intersects(r.the_geom, b.the_geom)
AND b.name = 'Dorchester'--spatial join by intersection example
                        SELECT *
FROM boundary
WHERE name = 'Dorchester'
                    

Hands On #3

                        boolean ST_Intersects( geometry geomA , geometry geomB );
text ST_AsText(geometry g1);
geometry ST_Buffer(geometry g1, float radius_of_buffer);
                    

Other Resources

• esri
  • Create Database View
  • DBMS data types supported in ArcGIS
• CartoDB
  • Tips and Tricks
• Microsoft
  • OGC Methods on Geometry Instances
• Discover Spatial
  • Turbocharge your geospatial education
• Boundless
  • Introduction to PostGIS: Spatial Relationships

Filter, Aggregate, and Combine

Taking Advantage of SQL Queries for Enterprise Databases

Spring NEARC - May 15, 2015

Guido Stein - Applied Geographics, Inc. / @guidos

get the updated presentation

ALTER TABLE big_belly_locations
ADD latitude double precision

ALTER TABLE big_belly_locations
ADD longitude double precision

UPDATE big_belly_locations
SET latitude = CAST ((regexp_matches(LOCATION,
'[(](.*),(.*)[)]'))[1] AS double precision)

UPDATE big_belly_locations
SET longitude = CAST ((regexp_matches(LOCATION,
'[(](.*),(.*)[)]'))[2] AS double precision)