foss4gna2013 postgis frenzy
Paul Ramsey presents....
PostGIS Feature Frenzy!!! Spatial Database
Widely Supported
Corporations
Governments
Start the Frenzy!!!
! m o d ee r F
Li be r
! L s SQ nes d o Go
NoSQL?
ty
!
ia t a p S
ia t a p S
! L Q S l
! L Q S l
Aw Po es we om r! en es s
!
Aw Po es we om r! en es s
!
ia t a p S
a p S
! L Q S l
! L Q S l SELECT a ti
Aw Po es we om r! en es s
!
Aw Po es we om r! en customers.* es s
!
census.*, FROM census JOIN customers ON ST_Contains( census.geom, customers.geom );
, d e d s! t s e ie o C ann uer Pl l Q iaSELECT ... t a p FROM geotable_a a S JOIN ON JOIN ON JOIN ON
e h T
Tr yt My his SQ on L!
geotable_b b ST_Intersects(b.geo,a.geo) attrtable_c c (b.id = c.id) attrtable_d d (a.id = d.id)
! s ic s Ba
ST_Length(A) ST_Distance(A, B) ST_DWithin(A, B, r) ST_Area(A) ST_Intersects(A, B)
e h T
! s c i s a B
ST_AsText(A) ST_AsBinary(A) ST_GeomFromText() ST_GeomFromBinary()
n s! u F at m r Fo
ST_AsGeoJSON() ST_AsGML() ST_AsKML() ST_GeomFromGML() ST_GeomFromKML()
ry n! t e io m t o c Ge stru n o ST_Buffer() C
ST_MakeLine() ST_Polygonize() ST_BuildArea() ST_Union()
ry n! t e io m ct o Ge stru n o C
ST_MakeLine({point})
ry n! t e io m t o c Ge stru n o C
ST_BuildArea(multilinestring)
ry n! t e io m ct o Ge stru n o C
ST_Union(geometry[])
d e d a c n! s Ca Unio
d e d a c n! s Ca Unio
No t un jus ion t !
ed ! r a try p e e r P om Ge SELECT
In No te t j rs us ec t ts !
... FROM points, polygons WHERE ST_Intersects ( polygons.geom, points.geom )
d e r y! a p etr e Pr om Ge
ed ! r a try p e e r P om Ge
d e r y! a p etr e Pr om Ge
Point in Polygon
Point in Polygon = O(n)
ed ! r a try p e e r P om Ge
Prepared geometry makes repeated tests on large geometries very fast. (ST_Intersects, ST_Contains)
r g! a e in n Li enc er f Re A
hwy brdg 12
B
hwy 12
“The bridge is at mile 10.5 on Highway 12”
101
loc 10.5
geom
r g! a ne cin i L en er f Re A
rvr 9
fsh from to 101
3
5
B rvr
geom
9
“The salmon habitat is from 3km to 5k above the confluence”
r g! a e in n Li enc er f ST_LocateAlong() Re
ST_LocateBetween() ST_AddMeasure() ST_Line_Locate_Point()
v r u
! s e
C • CURVESTRING
C
•
COMPOUNDCURVE
•
CURVEPOLYGON
•
ST_CurveToLine()
•
ST_LineToCurve()
v r u
! s e
ST _C To u Lin rve e() !
C
R
v r u
ST _L Cu ineT rve o ()!
! s e
ec j o r ep
n o ti
ST _T ran sfo rm
()
n it o
R
ec j o Albers r • ep
• Lambert • Mercator • Sinusiodal • Stereographic • UTM • Gnomic • Orthographic
! y h p a r g o Ge
ST _T ran sfo rm • Robinson
• Miller • Krovak • Azimuthal
Equidistant
•And
more...
()
! y h p a r g o Ge
! y h p a r g o Ge
! y h p a r g o Ge
! y h p a r g o Ge
! y h p a r g o Ge
! y h p a r g o Ge
! y h p a r g o GeoNewbies e G
Who is geography for?
“I want to find all the address points within one mile! My data is in lat/lon! Google Maps rocks!”
! y h p a r g o Ge
GeoHugies
Who is geography for?
“Yeah, I own a freaking satellite, you got a problem with that?”
! y h p a r g o Ge • Indexes spherical data • ST_Intersects() • ST_Distance() • ST_DWithin() • ST_Area() • Casts to/from GEOMETRY
p h s
g p 2
Geography functions?
l q s
shp2pgsql -D -s 4326 \ -i \ countries.shp \ countries \ | psql -U pramsey \ -d geodatase
p h s
p h s
g p 2
g p 2
l q s
l q s
p h s
g p 2
l q s
od
N ew 2. to 0!
m p Ty CREATE TABLE my_spatial_table ( id INTEGER, name VARCHAR(64), geom Geometry(Point,26910) );
m p Ty
od
N ew 2. to 0!
ALTER TABLE my_spatial_table ALTER COLUMN geom SET DATA TYPE Geometry(Point, 4326) USING ST_Transform(geom, 4326)
m p Ty
od
N ew 2. to 0!
SELECT * FROM geometry_columns WHERE f_table_name = ‘my_spatial_table’
m p Ty
N ew 2. to 0!
od
------------------+----------------f_table_catalog | my_database f_table_schema | public f_table_name | my_spatial_table f_geometry_column | geom coord_dimension | 2 srid | 4326 type | POINT
3D
N ew 2. to 0!
t! r o p p u S
In the 3rd Dimension
t! r o p up
3D
3D
S•
ST_3dDistance(geom, geom)
N ew 2. to 0!
• ST_3dLength(geom) • ST_3dClosestPoint(geom, geom) • ST_3dPerimeter(geom) • ST_3dIntersects(geom, geom) • ST_3dDWithin(geom, geom, tolerance) t! r o p p u New S
3D Types!
• TRIANGLE • TIN • POLYHEDRALSURFACE
N ew 2. to 0!
t! r o p up
3D
New 3D Formats!
S
N ew 2. to 0!
• ST_AsX3D(geom) • ST_AsGML(3, ...) • Also... • •
ND
e d -In
ST_AsText(geom) ST_AsBinary(geom)
x!
N ew 2. to 0!
CREATE INDEX my_index ON my_spatial_table USING GIST ( geom gist_nd_geometry_ops );
ND
e d -In
x!
N ew 2. to 0!
SELECT * FROM my_spatial_table WHERE geom &&& ‘LINESTRING Z (0 0 0, 10 10 10)’
r e t s s a e R p Ty
N ew 2. to 0!
er t s s a R pe Ty
N ew 2. to 0!
r e t s s a e R p Ty
N ew 2. to 0!
er t s s a R pe Ty
N ew 2. to 0!
r e t s s a e R p Ty
N ew 2. to 0!
N ew 2. to 0!
er t s s a R pe Ty
o p To
g o l
y!
Topology
N ew 2. to 0!
p o T
o ol
o p To
g
g o l
y!
N ew 2. to 0!
y!
N ew 2. to 0!
d e x st e d re uIndexed r n KNN I ea o N ghb i e N • KNN = K Nearest Neighbour
N ew 2. to 0!
• Index-based tree search • Restricted to index keys (a.k.a. bounding boxes)
• Points: exact answer • Others: box-based answer d e x st e d r In eare ou N ghb i e N
2,082,965 GNIS Points
N ew 2. to 0!
d e x st e d re ur n I ea o N ghb i e N
N ew 2. to 0!
N d ew e t x e es r 2. to d 0! In ear ou N ghb i e N SELECT id, name, state, kind FROM geonames ORDER BY geom (SELECT geom FROM geonames WHERE id = 4781416) LIMIT 10
N d ew e x st e 2. to d r e 0! In ear ou id |b name | state | kind N h ---------+-----------------------------------+-------+-----ig | Reedy Creek 4781416 | VA | STM e N 4794583 | Woodland Heights Baptist Church | VA | CH 4759577 | 6495576 | 7239038 | 4778121 | 4746788 | 4794519 | 4780425 | 4774149 | (10 rows)
Forest Hill Park Fairfield Inn And Stes Rich Nw Greater Brook Road Baptist Church Patrick Henry Elementary School Berryman United Methodist Church Woodland Park Progressive Holiness Church Mount Calvary Cemetery
| | | | | | | |
Time: 9.723 ms
ty g i id tin l Va or p e R
ESRI
OGC
VA VA VA VA VA VA VA VA
| | | | | | | |
PRK HTL CH SCH CH PPL CH CMTY
ty g i id tin ST_IsValidReason() l Va or ST_IsValid() p e R Interior is disconnected[-2 0]
Ring Self-intersection[2 0]
ty i id ir l Va epa ST_MakeValid() R
N ew 2. to 0!
g in ! om .1 C in 2
e ! v r ce u C an t s Di
SELECT ST_Distance( ‘CIRCULARSTRING(...)’, ‘CURVEPOLYGON(...)’ );
g in ! om .1 C in 2
!! e e or anc M m r• Raster ST_Union(), native implementation, o f 10x faster r e
P
• Geography ST_Distance() and ST_DWithin (), internally indexed, 20-30x faster
• ST_DumpPoints(), native implementation, 10x faster
• New R-Tree splitter, 20-30% faster • New N-D and geography statistics calculations, 20-30% faster
Do you have questions about...?
PostGIS Feature Frenzy!!!
PostGIS Feature Frenzy!!! Spatial Database
Widely Supported
Corporations
Governments
Start the Frenzy!!!
! m o d ee r F
Li be r
! L s SQ nes d o Go
NoSQL?
ty
!
ia t a p S
ia t a p S
! L Q S l
! L Q S l
Aw Po es we om r! en es s
!
Aw Po es we om r! en es s
!
ia t a p S
a p S
! L Q S l
! L Q S l SELECT a ti
Aw Po es we om r! en es s
!
Aw Po es we om r! en customers.* es s
!
census.*, FROM census JOIN customers ON ST_Contains( census.geom, customers.geom );
, d e d s! t s e ie o C ann uer Pl l Q iaSELECT ... t a p FROM geotable_a a S JOIN ON JOIN ON JOIN ON
e h T
Tr yt My his SQ on L!
geotable_b b ST_Intersects(b.geo,a.geo) attrtable_c c (b.id = c.id) attrtable_d d (a.id = d.id)
! s ic s Ba
ST_Length(A) ST_Distance(A, B) ST_DWithin(A, B, r) ST_Area(A) ST_Intersects(A, B)
e h T
! s c i s a B
ST_AsText(A) ST_AsBinary(A) ST_GeomFromText() ST_GeomFromBinary()
n s! u F at m r Fo
ST_AsGeoJSON() ST_AsGML() ST_AsKML() ST_GeomFromGML() ST_GeomFromKML()
ry n! t e io m t o c Ge stru n o ST_Buffer() C
ST_MakeLine() ST_Polygonize() ST_BuildArea() ST_Union()
ry n! t e io m ct o Ge stru n o C
ST_MakeLine({point})
ry n! t e io m t o c Ge stru n o C
ST_BuildArea(multilinestring)
ry n! t e io m ct o Ge stru n o C
ST_Union(geometry[])
d e d a c n! s Ca Unio
d e d a c n! s Ca Unio
No t un jus ion t !
ed ! r a try p e e r P om Ge SELECT
In No te t j rs us ec t ts !
... FROM points, polygons WHERE ST_Intersects ( polygons.geom, points.geom )
d e r y! a p etr e Pr om Ge
ed ! r a try p e e r P om Ge
d e r y! a p etr e Pr om Ge
Point in Polygon
Point in Polygon = O(n)
ed ! r a try p e e r P om Ge
Prepared geometry makes repeated tests on large geometries very fast. (ST_Intersects, ST_Contains)
r g! a e in n Li enc er f Re A
hwy brdg 12
B
hwy 12
“The bridge is at mile 10.5 on Highway 12”
101
loc 10.5
geom
r g! a ne cin i L en er f Re A
rvr 9
fsh from to 101
3
5
B rvr
geom
9
“The salmon habitat is from 3km to 5k above the confluence”
r g! a e in n Li enc er f ST_LocateAlong() Re
ST_LocateBetween() ST_AddMeasure() ST_Line_Locate_Point()
v r u
! s e
C • CURVESTRING
C
•
COMPOUNDCURVE
•
CURVEPOLYGON
•
ST_CurveToLine()
•
ST_LineToCurve()
v r u
! s e
ST _C To u Lin rve e() !
C
R
v r u
ST _L Cu ineT rve o ()!
! s e
ec j o r ep
n o ti
ST _T ran sfo rm
()
n it o
R
ec j o Albers r • ep
• Lambert • Mercator • Sinusiodal • Stereographic • UTM • Gnomic • Orthographic
! y h p a r g o Ge
ST _T ran sfo rm • Robinson
• Miller • Krovak • Azimuthal
Equidistant
•And
more...
()
! y h p a r g o Ge
! y h p a r g o Ge
! y h p a r g o Ge
! y h p a r g o Ge
! y h p a r g o Ge
! y h p a r g o Ge
! y h p a r g o GeoNewbies e G
Who is geography for?
“I want to find all the address points within one mile! My data is in lat/lon! Google Maps rocks!”
! y h p a r g o Ge
GeoHugies
Who is geography for?
“Yeah, I own a freaking satellite, you got a problem with that?”
! y h p a r g o Ge • Indexes spherical data • ST_Intersects() • ST_Distance() • ST_DWithin() • ST_Area() • Casts to/from GEOMETRY
p h s
g p 2
Geography functions?
l q s
shp2pgsql -D -s 4326 \ -i \ countries.shp \ countries \ | psql -U pramsey \ -d geodatase
p h s
p h s
g p 2
g p 2
l q s
l q s
p h s
g p 2
l q s
od
N ew 2. to 0!
m p Ty CREATE TABLE my_spatial_table ( id INTEGER, name VARCHAR(64), geom Geometry(Point,26910) );
m p Ty
od
N ew 2. to 0!
ALTER TABLE my_spatial_table ALTER COLUMN geom SET DATA TYPE Geometry(Point, 4326) USING ST_Transform(geom, 4326)
m p Ty
od
N ew 2. to 0!
SELECT * FROM geometry_columns WHERE f_table_name = ‘my_spatial_table’
m p Ty
N ew 2. to 0!
od
------------------+----------------f_table_catalog | my_database f_table_schema | public f_table_name | my_spatial_table f_geometry_column | geom coord_dimension | 2 srid | 4326 type | POINT
3D
N ew 2. to 0!
t! r o p p u S
In the 3rd Dimension
t! r o p up
3D
3D
S•
ST_3dDistance(geom, geom)
N ew 2. to 0!
• ST_3dLength(geom) • ST_3dClosestPoint(geom, geom) • ST_3dPerimeter(geom) • ST_3dIntersects(geom, geom) • ST_3dDWithin(geom, geom, tolerance) t! r o p p u New S
3D Types!
• TRIANGLE • TIN • POLYHEDRALSURFACE
N ew 2. to 0!
t! r o p up
3D
New 3D Formats!
S
N ew 2. to 0!
• ST_AsX3D(geom) • ST_AsGML(3, ...) • Also... • •
ND
e d -In
ST_AsText(geom) ST_AsBinary(geom)
x!
N ew 2. to 0!
CREATE INDEX my_index ON my_spatial_table USING GIST ( geom gist_nd_geometry_ops );
ND
e d -In
x!
N ew 2. to 0!
SELECT * FROM my_spatial_table WHERE geom &&& ‘LINESTRING Z (0 0 0, 10 10 10)’
r e t s s a e R p Ty
N ew 2. to 0!
er t s s a R pe Ty
N ew 2. to 0!
r e t s s a e R p Ty
N ew 2. to 0!
er t s s a R pe Ty
N ew 2. to 0!
r e t s s a e R p Ty
N ew 2. to 0!
N ew 2. to 0!
er t s s a R pe Ty
o p To
g o l
y!
Topology
N ew 2. to 0!
p o T
o ol
o p To
g
g o l
y!
N ew 2. to 0!
y!
N ew 2. to 0!
d e x st e d re uIndexed r n KNN I ea o N ghb i e N • KNN = K Nearest Neighbour
N ew 2. to 0!
• Index-based tree search • Restricted to index keys (a.k.a. bounding boxes)
• Points: exact answer • Others: box-based answer d e x st e d r In eare ou N ghb i e N
2,082,965 GNIS Points
N ew 2. to 0!
d e x st e d re ur n I ea o N ghb i e N
N ew 2. to 0!
N d ew e t x e es r 2. to d 0! In ear ou N ghb i e N SELECT id, name, state, kind FROM geonames ORDER BY geom (SELECT geom FROM geonames WHERE id = 4781416) LIMIT 10
N d ew e x st e 2. to d r e 0! In ear ou id |b name | state | kind N h ---------+-----------------------------------+-------+-----ig | Reedy Creek 4781416 | VA | STM e N 4794583 | Woodland Heights Baptist Church | VA | CH 4759577 | 6495576 | 7239038 | 4778121 | 4746788 | 4794519 | 4780425 | 4774149 | (10 rows)
Forest Hill Park Fairfield Inn And Stes Rich Nw Greater Brook Road Baptist Church Patrick Henry Elementary School Berryman United Methodist Church Woodland Park Progressive Holiness Church Mount Calvary Cemetery
| | | | | | | |
Time: 9.723 ms
ty g i id tin l Va or p e R
ESRI
OGC
VA VA VA VA VA VA VA VA
| | | | | | | |
PRK HTL CH SCH CH PPL CH CMTY
ty g i id tin ST_IsValidReason() l Va or ST_IsValid() p e R Interior is disconnected[-2 0]
Ring Self-intersection[2 0]
ty i id ir l Va epa ST_MakeValid() R
N ew 2. to 0!
g in ! om .1 C in 2
e ! v r ce u C an t s Di
SELECT ST_Distance( ‘CIRCULARSTRING(...)’, ‘CURVEPOLYGON(...)’ );
g in ! om .1 C in 2
!! e e or anc M m r• Raster ST_Union(), native implementation, o f 10x faster r e
P
• Geography ST_Distance() and ST_DWithin (), internally indexed, 20-30x faster
• ST_DumpPoints(), native implementation, 10x faster
• New R-Tree splitter, 20-30% faster • New N-D and geography statistics calculations, 20-30% faster
Do you have questions about...?
PostGIS Feature Frenzy!!!
