https://bit.ly/2JYfqCA

Everything
About
PostGIS●●“spatial”
database
7crunchydata.com

●○

Database
●
○
●
○
○
○
8

crunchydata.com

Spatial
Database● Spatial○● Spatial○● Spatial○○○

9

crunchydata.com

is toas

is to●●●●●●
crunchydata.com

crunchydata.com

ST_Function()

ISO SQL/MM Part 3: Spatial
13249-3
exceptcrunchydata.com

Spatial

FGDB

Spatial●●●●Spatial
●
○
○
○

●
○
○

●
○

crunchydata.com

crunchydata.com

crunchydata.com

crunchydata.com

ServerRuby, C, C++, DCOM, JDBC, ODBC, node.js, Scala, Groovy,Mono, Haskell, Rust, ...

Internet

Load Balancer

DatabaseYour own
managed cluster?

● Database-as-aservice● Crunchy
PostgreSQL“operator” fork8s● Deploy on anycloudHistory
31

crunchydata.com

crunchydata.com

“Managingchanging
data inshape ﬁles
is a pain inthe _____!”

FGDB

crunchydata.com

&crunchydata.com

GIS
without theGIS
35

crunchydata.com

●●●CREATE EXTENSION postgis;

crunchydata.com

it’s featurefre y!!! (ac !)freedo !

is the ultimate
hotness

crunchydata.com

YeSQL!
crunchydata.com

GIS
without theGIS
43

crunchydata.com

44

crunchydata.com

00
01crunchydata.com

46

SELECT owner_phone
FROM parcels
WHERE ST_DWithin(geom,'POINT()',1000);

crunchydata.com

47

crunchydata.com

crunchydata.com

SELECT Sum(ST_Length(geom))
FROM vehicle_paths
WHERE id = 12
AND date > Now() - '7d';49

crunchydata.com

50

crunchydata.com

crunchydata.com

SELECT t.*
FROM trucks t
ORDER BY t.geom <-> (SELECT geom
FROM transformersWHERE trans_id = 12)
LIMIT 1;52

crunchydata.com

53

crunchydata.com

crunchydata.com

SELECT yards.id, trucks.*
FROM trucks tJOIN yards y
ON ST_Contains(y.geom,t.geom)
WHERE y.code = ’service’;55

crunchydata.com

Spatial
Database
Basicscrunchydata.com

CREATE TABLE my_table (id INTEGER SERIAL PRIMARY KEY,
name VARCHAR(28),geom GEOMETRY(Point, 26910))
CREATE TABLE my_table (geog GEOGRAPHY(Point, 4326))

●●●

GIS
without theGIS
66

crunchydata.com

Waldo Tobler’s First Law of Geography“Everything is related
to everything else,
but near things are
more related than
distant things.”

Spatial is theforeign primary
universal key

ST_Intersects(geom, geom)
ST_Contains(geom, geom)
ST_DWithin(geom, geom, radius)70

crunchydata.com

71

crunchydata.com

72

crunchydata.com

73

crunchydata.com

SELECT
census.*, customers.*
FROM census
JOIN customers
ON ST_Contains(census.geom,customers.geom );74

crunchydata.com

ﬁnd thenearest
crunchydata.com

SELECT *FROM customers
ORDER BY
geom <->
ST_MakePoint(-124, 42)
LIMIT 176

crunchydata.com

crunchydata.com

2,082,965 GNIS Points

crunchydata.com

SELECT id, name, state, kind
FROM geonames
ORDER BYgeom <->(SELECT geom FROM geonames
WHERE id = 4781416)
LIMIT 10
2,082,965 GNIS Points

10ms

crunchydata.com

SELECT c.*, s.store_id
ST_Distance(c.geom, s.geom) AS dist
FROMcustomers AS c
CROSS JOIN LATERAL(SELECT store_id, geom
FROM stores
ORDER BY
customers.geom <-> stores.geom
LIMIT 1) AS s80

crunchydata.comPostGISInternal
Architecture
crunchydata.com

CREATE FUNCTION st_area
RETURNS float8
AS 'postgis.so', 'st_area'
LANGUAGE 'c';

PG_FUNCTION_INFO_V1(st_area);
Datum st_area(PG_FUNCTION_ARGS) {}crunchydata.com

CREATE FUNCTION test
RETURNS text
AS ‘extension.so’, ‘test’
LANGUAGE ‘c’;

PG_FUNCTION_INFO_V1(test);
Datum test(PG_FUNCTION_ARGS) {}crunchydata.com

crunchydata.com

Longitude

Latitude(𝚹, 𝚽)(𝚹1, 𝚽1)

ForwardInverse

Pipeline(x, y)(𝚹2, 𝚽2)
crunchydata.com

ST_Transform(geometry, srid)

86

crunchydata.com

●
●
●
●
●
○
87

crunchydata.com

crunchydata.com

Geometry
EngineOpen
Sourcecrunchydata.com

●●crunchydata.com

Geometry
EngineOpenSource● ST_Intersects(A, B)○ = NOT ST_Disjoint(A, B)● ST_Contains(A, B)○ = ST_Within(B, A)● ST_Touches(A, B)● ST_Overlaps(A, B)● ST_Covers(A, B)● ST_Crosses(A, B)
crunchydata.com

Geometry
EngineOpen
Source

ST_Union(A, B)

ST_Intersection(A, B)

ST_SymDiﬀerence(A, B)

ST_Diﬀerence(A, B)●●●●
crunchydata.com

ST_Union(geometry[])93

crunchydata.com

Geometry
EngineOpenSource●●

ST_Buﬀer(A, +R)

ST_Buﬀer(A, -R)

crunchydata.com

DilateandErodecrunchydata.com

crunchydata.com

crunchydata.com

crunchydata.com

Geometry
EngineOpen
Source

ST_VoronoiPolygons()
ST_DelaunayTriangles()●●crunchydata.com

100

crunchydata.com

ST_GeneratePoints()
101

crunchydata.com

ST_ClusterKMeans()
102

crunchydata.com

ST_Centroid()103

crunchydata.com

ST_VoronoiPolygons()
104

crunchydata.com

ST_Intersection()105

crunchydata.com

106

crunchydata.com

Geometry
EngineOpenSource

crunchydata.com

● Another Library!
● 3D Support● New Algorithms
crunchydata.com

CREATE EXTENSION
postgis_sfcgal● ST_3DIntersection● ST_ForceLHR● ST_Tesselate● ST_Orientation● ST_3DArea● ST_Minkowski● ST_3DUnion● ST_Volume● ST_Extrude● ST_StraightSkeleton● ST_ApproximateMedialAxis
crunchydata.com

ST_ApproximateMedialAxis

crunchydata.com

ST_Extrude(geometry, x, y, z)

crunchydata.com

ST_3DIntersection(geometry, geometry)crunchydata.com

crunchydata.comcrunchydata.com

2D, 3D& 4D

TYPES &INDEXING

XYZM
XYZT

IN THE 3RD DIMENSION
crunchydata.com

POINT Z (1 2 3)
LINESTRING Z (1 3 4, 1 4 5)POLYGON Z ((0 0 2, 1 1 2,1 0 2, 0 0 2))
ST_X('POINT(1 2 3)') = 3
116

crunchydata.com

ST_AsText()
ST_AsBinary()
ST_AsGML()
ST_AsGeoJSON(){"type":"Point","coordinates":[1,1,1]}
117

crunchydata.com

ST_3dDistance(geom, geom)
ST_3dLength(geom)
ST_3dClosestPoint(geom, geom)
ST_3dPerimeter(geom)
ST_3dIntersects(geom, geom)
ST_3dDWithin(geom, geom, tolerance)
118

crunchydata.com

ST_Length(geom, geom)
vsST_3dLength(geom, geom)
119

crunchydata.com

CREATE INDEX my_nd_x
ON my_table
USING GIST (geom gist_geometry_ops_nd);120

crunchydata.com

SELECT *FROM starships
ORDER BY
geom <->'POINT Z (534 492 -166)'
LIMIT 1121

crunchydata.com

M

crunchydata.com

POINT ZM (1 2 3 4)
LINESTRING M (1 2 3, 1 4 3)
LINESTRING Z (1 3 4, 1 4 5)
ST_M('POINT(1 2 3 4)') = 4
123

crunchydata.com

hwy

brdg

loc

12

101

10.5

crunchydata.com

crunchydata.com

LINESTRING M (x1 y1 m1, x2 y2 m2)
ST_LineInterpolatePoint(geom, ﬂoat)
ST_LineLocatePoint(geom, geom)
ST_LocateAlong(geom, ﬂoat, ﬂoat)
ST_LocateBetween(geom, ﬂoat, ﬂoat)
ST_LineSubstring(geom, ﬂoat, ﬂoat)
126

crunchydata.com

crunchydata.com

LINESTRING M (x1 y1 t1, x2 y2 t2)

crunchydata.com

LINESTRING M (x1 y1 t1, x2 y2 t2)● ST_ClosestPointOfApproach(geom, geom)● ST_IsValidTrajectory(geom)● ST_DistanceCPA(geom, geom)
crunchydata.com

ST_CurveToLine(geometry)130

crunchydata.com

ST_LineToCurve(geometry)131

crunchydata.com

ST_MakeLine({point})
132

crunchydata.com

ST_BuildArea(multilinestring)133

crunchydata.com

ST_BuildArea(multilinestring)134

crunchydata.com

●
●
●

●
●
●

Geography
crunchydata.com

S2

R2QP

geography

geometry

GeoNewbies“I want to find all theaddress points within
one mile! My data is in
lat/lon! Google Mapsrocks!”
crunchydata.com

“GIS sucks, and I want to go home now.”

crunchydata.com

GeoHugies“Yeah, I own a freaking
satellite, you got a problem
with that?”crunchydata.com

crunchydata.com

geography
double R = 6371000; /* meters */double d_lat = lat2-lat1; /* radians */double d_lon = lon2-lon1; /* radians */double sin_lat = sin(d_lat/2);
double sin_lon = sin(d_lon/2);
double a = sin_lat * sin_lat +cos(lat1) * cos(lat2) *sin_lon * sin_lon;
double c = 2 * atan2(sqrt(a),sqrt(1-a));double d = R * c;geometry
double dx = x2 - x1;
double dy = y2 - y1;
double d2 = dx * dx +dy * dy;double d = sqrt(d2);crunchydata.com

● Indexes on spherical data
● Nearest neighbor searches● ST_Intersects()● ST_Distance()● ST_DWithin()● ST_Area()● Casts to/from GEOMETRY
crunchydata.com

crunchydata.com

crunchydata.com

crunchydata.com

crunchydata.com

😀

crunchydata.com

😀

crunchydata.com

😀

crunchydata.com

😀

crunchydata.com

crunchydata.com

SELECT b.*FROM buildings b
JOIN dem
ON ST_Intersects(b.geom,dem.rast)
WHERE ST_Value(dem.rast,ST_Centroid(b.geom)) < 30;
crunchydata.com

😀

crunchydata.com

crunchydata.com

● For coverages &shared boundaries● Parcels, adminboundaries● Good tooling requiredeg QGIS PostGIS Topology Editor
crunchydata.com

Performance
crunchydata.com

PostGIS is fasterthan...

PostGIS isslower than...

crunchydata.com

PostGIS isbetter than...

PostGIS isworse than...

crunchydata.com

Performance
Tweakscrunchydata.com

ST_Subdivide()
Recursive subdivisionPolygon with
90,000 vertices
crunchydata.com

CREATE TABLE land_subdivided ASSELECT
ST_SubDivide(geom) AS geom,base250_id
FROM land

Polygons withno more than
250 vertices

crunchydata.com

crunchydata.com

ST_Simplify()
Douglas-Pueker Algorithm
crunchydata.com

ST_SimplifyVW()
Visvalingam-Whyatt Algorithm
crunchydata.com

randomorder byST_X()

crunchydata.com

ORDER
BY geom(v2.4)ORDER
BY geom(v3.0)
crunchydata.com

clustering!
(not nodes!)168

crunchydata.com

ST_GeneratePoints(geom, n)

crunchydata.com

ST_GeneratePoints(geom,popn/100)

crunchydata.com

ST_ClusterDBScan(geom,1000, -- proximity
100 -- minsize)

crunchydata.com

ST_ClusterKMeans(geom,5 -- K)

crunchydata.com

GIS
without theGIS
173

crunchydata.com

PostGIS
Web
Architectures
crunchydata.com

crunchydata.com

ST_AsText(geometry)
ST_AsBinary(geometry)
ST_AsGML(geometry)
ST_AsSVG(geometry)
ST_AsGeoJSON(record)
ST_AsMVT(record)
176

crunchydata.com

Web Maps → MVT
Web Queries → GeoJSON
https://info.crunchydata.com/blog/dynamic-vector-tiles-from-postgis

crunchydata.com

GIS
without theGIS
178

crunchydata.com

PostGIS
PostgreSQL
Ecosystecrunchydata.com

●●Network traversals○ Djikstra○ A*, C*, Shooting*○ Travelling salesman○ Trade areas
Build any network you like
○ Utility network?
○ Edge weights, dynamicedge removal, etc

crunchydata.com

https://github.com/pgpointcloud/pointcloud
crunchydata.com

● Forward Geocoder○ Input is an address string○ Output is a address location point● Uses US Census data
○ Limited to US states and territories● Included in PostGIS○○

create extension postgis_tiger_geocoder
create extension address_standardizer

crunchydata.com

Uber H3

https://github.com/dlr-eoc/pgh3
crunchydata.com

ogr_fdw+ dozens
more

https://github.com/pramsey/pgsql-ogr-fdw
crunchydata.com

LearningMore
crunchydata.com

●
○

●
○

●
○
○

●
○
○
○
crunchydata.com

https://bit.ly/2JYfqCA

Everything
About
PostGIS