the state of postgis paul ramsey [email protected] paul ramsey [email protected]
TRANSCRIPT
![Page 2: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/2.jpg)
{
![Page 3: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/3.jpg)
Spatial Database?
![Page 4: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/4.jpg)
‣ Types
‣ string, float, date
‣ Indexes
‣ b-tree, hash
‣ Functions
‣ strlen(string), pow(float, float), now()
Database
![Page 5: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/5.jpg)
‣ Spatial Types
‣ geometry, geography
‣ Spatial Indexes
‣ r-tree, quad-tree, kd-tree
‣ Spatial Functions
‣ ST_Length(geometry), ST_X(geometry)
Database
![Page 6: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/6.jpg)
Open Geospatial Consortium (OGC)
Simple Features for SQL (SFSQL)
![Page 7: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/7.jpg)
![Page 8: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/8.jpg)
![Page 9: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/9.jpg)
![Page 10: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/10.jpg)
![Page 11: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/11.jpg)
![Page 12: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/12.jpg)
‣ Open Source (BSD)
‣ “Enterprise” Database‣ ACID, hot backup, replication,
partitioning
‣ triggers, constraints, foreign keys, user functions
‣ PL/PGSQL, PL/Perl, PL/TCL, PL/Java, PL/R
‣ Corporate support‣ Enterprise DB
‣ Red Hat
‣ Open Source (BSD)
‣ “Enterprise” Database‣ ACID, hot backup, replication,
partitioning
‣ triggers, constraints, foreign keys, user functions
‣ PL/PGSQL, PL/Perl, PL/TCL, PL/Java, PL/R
‣ Corporate support‣ Enterprise DB
‣ Red Hat
![Page 13: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/13.jpg)
What does PostGIS do?
![Page 14: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/14.jpg)
“What parcels are within 1km of this
fire?”
![Page 15: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/15.jpg)
SELECT owner_phoneFROM parcelsWHERE ST_DWithin( geom, ‘POINT()’, 1000 );
![Page 16: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/16.jpg)
“How far did the bus travel last
week?”
![Page 17: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/17.jpg)
SELECT Sum(ST_Length(geom))FROM vehicle_pathsWHERE (v_id = 12) AND (v_date > Now() - ‘7d’);
![Page 18: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/18.jpg)
History!
![Page 19: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/19.jpg)
![Page 20: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/20.jpg)
“Managing changing data in shape files is a
pain in the _____!”
![Page 21: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/21.jpg)
History!!!
![Page 22: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/22.jpg)
![Page 23: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/23.jpg)
![Page 24: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/24.jpg)
![Page 25: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/25.jpg)
More History!!!
![Page 26: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/26.jpg)
![Page 27: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/27.jpg)
![Page 28: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/28.jpg)
“Why are these companies supporting PostGIS?”
![Page 29: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/29.jpg)
![Page 30: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/30.jpg)
BDuni
![Page 31: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/31.jpg)
• Can DB handle 100M spatial features?
• Can DB do spatial transactions?
• PostGIS? DB2? Oracle?
• Yes! Yes! Yes!
DB Evaluation
![Page 32: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/32.jpg)
Transactional Maintenance
Check in editsCheck in edits
Check out working areas
Check out working areas
![Page 33: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/33.jpg)
![Page 34: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/34.jpg)
GSMGSM
![Page 35: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/35.jpg)
GSMGSM
GSMGSM
GSMGSM
GSMGSM
GSMGSM
![Page 36: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/36.jpg)
“Enterprise”
1 Dual-Core 2 Quad-Core
Oracle $40,000 $160,000
IBM DB2 $36,400 $145,600
MS SQL Server
$25,000 $50,000
IBM Informix $50,000 $200,000
PostGIS $0 $0
Scalability
![Page 37: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/37.jpg)
Functionality
Price
VALUE
![Page 38: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/38.jpg)
What’s New?
![Page 39: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/39.jpg)
PostGIS 1.4
• January 2009
• Prepared geometry
• Cascaded union
• Curves
• GeoJSON
![Page 40: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/40.jpg)
Prepared geometry
![Page 41: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/41.jpg)
Point in Polygon
![Page 42: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/42.jpg)
Point in Polygon = O(n)
![Page 43: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/43.jpg)
Line in Polygon = O(n•m)
![Page 44: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/44.jpg)
Build spatial index on edges!Point-in-polygon == O(log(n))Line-in-polygon == O(m•log(n))
But, Building an index takes O(n)So,Cache index and re-use it!
![Page 45: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/45.jpg)
Prepared geometry makes repeated tests on large geometries
very fast.
![Page 46: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/46.jpg)
SELECT ... FROM points, polygons WHERE ST_Contains( polygons.geom, points.geom )
![Page 47: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/47.jpg)
Cascaded union
![Page 48: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/48.jpg)
Cascaded union
![Page 49: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/49.jpg)
• CURVESTRING
• COMPOUNDCURVE
• CURVEPOLYGON
Curves
![Page 50: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/50.jpg)
PostGIS 1.5
• February 2010
• Geography type
• GUI shape file loader
• Faster distance calculation
• KML/GML format readers
![Page 51: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/51.jpg)
ST_AsGeoJSON()
ST_AsKML()ST_GeomFromGML
()ST_GeomFromKML()
ST_AsGML()
![Page 52: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/52.jpg)
![Page 53: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/53.jpg)
![Page 54: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/54.jpg)
![Page 55: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/55.jpg)
![Page 56: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/56.jpg)
![Page 57: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/57.jpg)
![Page 58: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/58.jpg)
![Page 59: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/59.jpg)
GeoNewbiesWho is
geography for?
“I want to find all the address points within
one mile. My data is in lat/lon. Google Maps
rocks.”
![Page 60: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/60.jpg)
GeoHugies
“Yeah, I own a freaking satellite, you got a
problem with that?”
Who is geography
for?
![Page 61: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/61.jpg)
• Indexes spherical data• ST_Intersects()• ST_Distance()• ST_DWithin()• ST_Area()• Casts to/from
GEOMETRY
Geographyfunctions?
![Page 62: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/62.jpg)
shp2pgsql -D -s 4326 \ -i \ countries.shp \ countries \| psql -U pramsey \ -d geodatase
shp2pgsql
shp2pgsql
![Page 63: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/63.jpg)
shp2pgsql
shp2pgsql
![Page 64: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/64.jpg)
PostGIS 2.0• December 2010• “typmod” support• Raster support (see other talks!)• 3D objects (polyhedra, TIN)• New index support
• 3D, 4D, Nearest-neighbor (PgSQL 9.1)
• Breaking changes!! (yay!)
![Page 65: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/65.jpg)
geometry_columns
typmod?
typmod?
![Page 66: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/66.jpg)
CREATE TABLE my_spatial_table ( id INTEGER, name VARCHAR );
typmod?
typmod?
![Page 67: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/67.jpg)
SELECT AddGeometryColumn( 'my_schema', 'my_spatial_table', 'the_geom', 26910, 'POINT', 2);
typmod?
typmod?
![Page 68: The State of PostGIS Paul Ramsey pramsey@opengeo.org Paul Ramsey pramsey@opengeo.org](https://reader035.vdocuments.us/reader035/viewer/2022062314/56649e965503460f94b9a301/html5/thumbnails/68.jpg)
CREATE TABLE my_spatial_table ( id INTEGER, name VARCHAR, geo GEOMETRY(Point,26910) );
typmod?
typmod?