https://goo.gl/a92MDH
paul.ramsey@crunchydata.ca
SQL =
Groovy �SQL!
Popular SQL!
Far Out �Spatial SQL!
Groovy SQL!
Motivation: Campaign Data & Analysis
Groovy �SQL!
Groovy SQL!
Groovy SQL!
nullif(middle_name, ‘’)� coalesce(middle_name, ‘’)
Groovy SQL!
concat_ws(‘ ’, � first_name, � middle_name, � last_name)
Groovy SQL!
concat_ws(‘, ’, � concat_ws(‘ -- ’, � apt_no, � concat_ws(‘ ’, � street_num, � street_name,� street_dir) � city, � state)
Groovy SQL!
Sum() FILTER (WHERE ...)
Groovy SQL!
BI “fact table” - long, denormalized
name | text |
age | integer |
income | real |
gender | text |
city | text |
state | text |
sales | real |
Groovy SQL!
SELECT � Avg(income) AS income,� Avg(income) AS inc_male� FILTER (WHERE gender = ‘male’),
Avg(income) AS inc_older� FILTER (WHERE age > ‘55’)
FROM demographics�GROUP BY city;
Groovy SQL!
string_agg(name, ‘,’ � ORDER BY …)
ST_MakeLine(gps_pt ORDER BY gps_time)
Groovy SQL!
Groovy SQL!
SELECT
gps_track_id,� ST_MakeLine(� gps_pt ORDER BY gps_time� ) AS geom
FROM gps_pts
GROUP BY gps_track_id;
DISTINCT ON
Groovy SQL!
http://files.boundlessgeo.com/workshopmaterials/postgis-workshop-201401.zip
Groovy SQL!
http://files.boundlessgeo.com/workshopmaterials/postgis-workshop-201401.zip
Groovy SQL!
http://files.boundlessgeo.com/workshopmaterials/postgis-workshop-201401.zip
Groovy SQL!
SELECT
DISTINCT ON (sbw.cartodb_id)
str.*
FROM nyc_streets str
JOIN nyc_subway_stations sbw
ON ST_DWithin(
str.the_geom_webmercator,
sbw.the_geom_webmercator,
200)
ORDER BY
sbw.cartodb_id,
ST_Distance(
str.the_geom_webmercator,
sbw.the_geom_webmercator)
Groovy SQL!
subway | street | distance |
B | 32nd Street | 123.5 |
A | 23nd Street | 23.4 |
B | Reed Avenue | 12.4 |
A | 24th Street | 34.4 |
B | 25th Street | 102.2 |
A | Reed Avenue | 45.5 |
C | State Street | 32.1 |
B | State Street | 56.4 |
Groovy SQL!
SELECT
DISTINCT ON (sbw.cartodb_id)
str.*
FROM nyc_streets str
JOIN nyc_subway_stations sbw
ON ST_DWithin(
str.the_geom_webmercator,
sbw.the_geom_webmercator,
200)
ORDER BY
sbw.cartodb_id,
ST_Distance(
str.the_geom_webmercator,
sbw.the_geom_webmercator)
Groovy SQL!
subway | street | distance |
A | 23nd Street | 23.4 |
A | 24th Street | 34.4 |
A | Reed Avenue | 45.5 |
B | Reed Avenue | 12.4 |
B | State Street | 56.4 |
B | 25th Street | 102.2 |
B | 32nd Street | 123.5 |
C | State Street | 32.1 |
Groovy SQL!
SELECT
DISTINCT ON (sbw.cartodb_id)
str.*
FROM nyc_streets str
JOIN nyc_subway_stations sbw
ON ST_DWithin(
str.the_geom_webmercator,
sbw.the_geom_webmercator,
200)
ORDER BY
sbw.cartodb_id,
ST_Distance(
str.the_geom_webmercator,
sbw.the_geom_webmercator)
Groovy SQL!
subway | street | distance |
A | 23nd Street | 23.4 |
B | Reed Avenue | 12.4 |
C | State Street | 32.1 |
Popular SQL!
Popular SQL!
https://gis.stackexchange.com/questions� /tagged/postgis?� sort=votes&� pageSize=15
What are the most popular PostGIS questions on Stack Exchange?
Popular SQL!
http://workshops.boundlessgeo.com� /postgis-intro
https://github.com/CartoDB� /carto-workshop/tree/master/04-database
Popular SQL!
Popular SQL!
Popular SQL!
Popular SQL!
Box–Muller transform
Popular SQL!
CREATE TABLE nyc_subway_stations_gaussian AS
WITH u AS (
SELECT random() AS u1,
random() AS u2
FROM generate_series(1,1000)
),
off AS (
SELECT
sqrt(-2*ln(u1))*cos(2*pi()*u2) AS x_off,
sqrt(-2*ln(u1))*sin(2*pi()*u2) AS y_off
FROM u
)
SELECT row_number() OVER () AS cartodb_id, name, routes,
ST_Translate(the_geom_webmercator, 150*x_off, 150*y_off) � AS the_geom_webmercator
FROM nyc_subway_stations sub
CROSS JOIN off
WHERE sub.name IN ('Grand St', 'Bowery', 'Essex St')
UNIFORM
GAUSSIAN
JOIN/OFFSET
Popular SQL!
ST_ClusterWithin(the_geom_webmercator, 25)
WITH c AS (
SELECT
unnest(ST_ClusterWithin(the_geom_webmercator, 23)) � AS the_geom_webmercator
FROM nyc_subway_stations_gaussian
),
d AS (
SELECT row_number() OVER () AS cluster_id, � ST_Dump(the_geom_webmercator) AS dump
FROM c
)
SELECT � row_number() OVER () AS cartodb_id, � cluster_id,� (dump).geom AS the_geom_webmercator
FROM d
Popular SQL!
Popular SQL!
ST_ClusterDBScan(the_geom_webmercator, 25)
SELECT
ST_ClusterDBSCAN(the_geom_webmercator, 23, 100) � OVER (),
the_geom_webmercator,
cartodb_id
FROM nyc_subway_stations_gaussian
Popular SQL!
Window Functions FTW!
Popular SQL!
ST_ClusterKMeans(the_geom_webmercator, 3)
Popular SQL!
Geography Pros
Geography Cons
Popular SQL!
Geography Pros
Geography Cons
Popular SQL!
ST_Intersects(geom, geom) = TRUE
ST_Intersects(geog, geog) = FALSE
Popular SQL!
ST_Segmentize(geog, 10000)
Popular SQL!
ST_AsMVTGeom(geom, b2dBounds, intRes)
ST_AsMVT(row)
Popular SQL!
Popular SQL!
“when I intersect a parcel dataset with 329,152 multipolygons with a jurisdiction dataset containing 525 multipolygons”
Popular SQL!
Popular SQL!
SELECT
a.id, b.id,
CASE
WHEN ST_Contains(a.geom, b.geom)
THEN b.geom
ELSE ST_Intersection(a.geom, b.geom)
END AS geom
FROM a, b
WHERE ST_Intersects(a.geom, b.geom);
Popular SQL!
Far Out �Spatial SQL!
Far Out Spatial SQL!
ST_Subdivide()
Far Out Spatial SQL!
Far Out Spatial SQL!
Far Out Spatial SQL!
Far Out Spatial SQL!
Far Out Spatial SQL!
Far Out Spatial SQL!
30x
Far Out Spatial SQL!
<->
Far Out Spatial SQL!
SELECT * �FROM nyc_subway_stations
ORDER BY
the_geom <-> � CDB_LatLng(40.72,-73.94)
LIMIT 2;
Far Out Spatial SQL!
Far Out Spatial SQL!
“Find the nearest that is not yourself.”
Far Out Spatial SQL!
SELECT
row_number() OVER () AS cartodb_id,
ST_Transform(ST_MakeLine(a.the_geom, b.the_geom),3857) � AS the_geom_webmercator,
ST_DistanceSphere(a.the_geom, b.the_geom) � AS distance,
a.name AS name_a,
b.name AS name_b
FROM nyc_subway_stations AS a
CROSS JOIN LATERAL (
SELECT subq.the_geom, subq.name
FROM nyc_subway_stations subq
WHERE a.cartodb_id != subq.cartodb_id
ORDER BY
a.the_geom <-> subq.the_geom
LIMIT 1) AS b
Far Out Spatial SQL!
SELECT
...
FROM table_a AS a
CROSS JOIN LATERAL (
SELECT table_b.*
FROM table_b
ORDER BY
a.the_geom <-> table_b.the_geom
LIMIT 1) AS b
Far Out Spatial SQL!
SELECT
row_number() OVER () AS cartodb_id,
ST_Transform(ST_MakeLine(a.the_geom, b.the_geom),3857) � AS the_geom_webmercator,
ST_DistanceSphere(a.the_geom, b.the_geom) � AS distance,
a.name AS name_a,
b.name AS name_b
FROM nyc_subway_stations AS a
CROSS JOIN LATERAL (
SELECT subq.the_geom, subq.name
FROM nyc_subway_stations subq
WHERE a.cartodb_id != subq.cartodb_id
ORDER BY
a.the_geom <-> subq.the_geom
LIMIT 1) AS b
Far Out Spatial SQL!
Far Out Spatial SQL!
Groovy SQL!
Popular SQL!
Far Out �Spatial SQL!
https://goo.gl/a92MDH
paul.ramsey@crunchydata.ca