!doctype html>
Taking Advantage of SQL Queries for Enterprise Databases
Spring NEARC - May 15, 2015
We are not alone, data and big data winter is coming
Esri Documentation on PostGIS
Which Version of PostGIS/PostgreSQL am I running?
Example 1 : PostgreSQL & PostGIS VersionSELECT PostGIS_full_version();
-- v2.1.7SELECT version();
-- v9.4.3
Parameter | Explanation | Data Type |
---|---|---|
input_database | The database that contains the tables used to construct the view. This database is also where the view will be created. | Workspace |
view_name | The name of the view that will be created in the database. | String |
view_definition | An SQL statement used to construct the view. String | String |
guidos.github.io
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
SELECT table_alias.field_name AS field_alias FROM table_name AS table_alias;
-- Select SyntaxSELECT 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
SELECT table_alias.field_name AS field_alias FROM table_name AS table_alias WHERE field_name = value;
-- Where SyntaxSELECT r.neighborhood AS name FROM rodent AS r WHERE r.neighborhood = 'Dorchester';
-- Filter results to include only where neighborhood is Dorchester
SELECT table_alias.field_name AS field_alias FROM table_name AS table_alias WHERE field_name = value GROUP BY field_name;
-- Group By SyntaxSELECT 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 SyntaxSELECT 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
WKT GEOMETRY | DESCRIPTION |
---|---|
POINT (30 10) | (X Y) |
MULTILINESTRING(
(10 10, 20 20, 10 40), (40 40, 30 30, 40 20, 30 10) ) |
(
(X Y, X Y), (X Y, X Y) ) |
MULTIPOLYGON (
((30 20, 45 40, 10 40, 30 20)), ((15 5, 40 10, 10 20, 5 10, 15 5)) ) |
(
((X Y, X Y),(X Y, X Y)), ((X Y, X Y)) ) |
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 exampleSELECT * FROM boundary WHERE name = 'Dorchester'
boolean ST_Intersects( geometry geomA , geometry geomB );
text ST_AsText(geometry g1);
geometry ST_Buffer(geometry g1, float radius_of_buffer);
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)