foss4g state of postgis
The State of PostGIS Paul Ramsey [email protected]
G EO S ERVER
G EO E XT { G EO W EB C ACHE P OST GIS
Spatial Database?
‣
Types ‣
‣
Indexes ‣
‣
string, float, date
b-tree, hash
Functions ‣
strlen(string), pow(float, float), now()
‣
Spatial Types ‣
‣
Spatial Indexes ‣
‣
geometry, geography
r-tree, quad-tree, kd-tree
Spatial Functions ‣
ST_Length(geometry), ST_X(geometry)
Open Geospatial Consortium (OGC)
Simple Features for SQL (SFSQL)
‣
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
‣
Sun
History!
“Managing changing data in shape files is a pain in the _____!”
History!!!
Type and index
0.1
WKT WKB
OGC standards
0.5
0.6
PgSQL 7.2
OGC SFSQL
Reprojection
ANALYZE
0.8
0.7
Polygonize
4D
0.9
1.0 2006
2001 Point-inpolygon
Distance
Regression tests
Index selectivity
ISO SQL/MM
Linemerge
1.1 2006 LRS
1.2
SRS performance
JTS
LWGEOM experiment
GEOS
Cascaded union
Performance
1.3
Curves
Stability
New docs
1.4
Prepared geometry
More History!!!
LWGEOM
GEOGRAPHY
1.5
Developer improvements
2011
Year
Supported By
2001
MapServer, OGR
2002
Geoserver, QGIS
2003
Safe FME
2004
Ionic Red Spider, OSSIM
2005
CadCorp SIS, GRASS, uDig, OpenJUMP
2006
ESRI Interoperability Extension, gvSIG
2007
ESRI ArcGIS Server, Manifold, GeoDjango
2008
FDO, MapGuide, AutoCAD MAP3D
2009
MapInfo, GeoConcept
“Why are these companies supporting PostGIS?”
OSSIM
BDuni
Transactional Maintenance Check in edits
Check out working areas
DB Evaluation •
Can DB handle 100M spatial features?
•
Can DB do spatial transactions?
•
PostGIS? DB2? Oracle?
•
Yes! Yes! Yes!
Scalability “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
Price
VALUE
Functionality
What’s New?
PostGIS 1.4 •
January 2009
•
Prepared geometry
•
Cascaded union
•
Curves
•
GeoJSON
Prepared geometry
Point in Polygon
Point in Polygon = O(n)
Line in Polygon = O(n•m)
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!
Prepared geometry makes repeated tests on large geometries very fast.
SELECT ... FROM points, polygons WHERE ST_Contains( polygons.geom, points.geom )
Cascaded union
Cascaded union
Curves •
CURVESTRING
•
COMPOUNDCURVE
•
CURVEPOLYGON
ST_AsGeoJSON() {"type":"LineString","coordinates":[[0,0],[1,1]]}
http://geojson.org
PostGIS 1.5 •
December 2009
•
Geography type
GeoNewbies GeoHugies
Geography •
Index spherical data
•
Include selectivity / estimation
•
ST_Distance()
•
ST_DWithin()
•
ST_Area()
•
Casts to/from GEOMETRY
PostGIS 2.0 •
December 2010
•
Typmod support •
GEOMETRY_COLUMNS as view
•
More geography, More performance
•
New index support •
•
KD-Tree, 3D, 4D, Nearest Neighbor
Breaking changes
Thanks! Paul Ramsey [email protected]
G EO S ERVER
G EO E XT { G EO W EB C ACHE P OST GIS
Spatial Database?
‣
Types ‣
‣
Indexes ‣
‣
string, float, date
b-tree, hash
Functions ‣
strlen(string), pow(float, float), now()
‣
Spatial Types ‣
‣
Spatial Indexes ‣
‣
geometry, geography
r-tree, quad-tree, kd-tree
Spatial Functions ‣
ST_Length(geometry), ST_X(geometry)
Open Geospatial Consortium (OGC)
Simple Features for SQL (SFSQL)
‣
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
‣
Sun
History!
“Managing changing data in shape files is a pain in the _____!”
History!!!
Type and index
0.1
WKT WKB
OGC standards
0.5
0.6
PgSQL 7.2
OGC SFSQL
Reprojection
ANALYZE
0.8
0.7
Polygonize
4D
0.9
1.0 2006
2001 Point-inpolygon
Distance
Regression tests
Index selectivity
ISO SQL/MM
Linemerge
1.1 2006 LRS
1.2
SRS performance
JTS
LWGEOM experiment
GEOS
Cascaded union
Performance
1.3
Curves
Stability
New docs
1.4
Prepared geometry
More History!!!
LWGEOM
GEOGRAPHY
1.5
Developer improvements
2011
Year
Supported By
2001
MapServer, OGR
2002
Geoserver, QGIS
2003
Safe FME
2004
Ionic Red Spider, OSSIM
2005
CadCorp SIS, GRASS, uDig, OpenJUMP
2006
ESRI Interoperability Extension, gvSIG
2007
ESRI ArcGIS Server, Manifold, GeoDjango
2008
FDO, MapGuide, AutoCAD MAP3D
2009
MapInfo, GeoConcept
“Why are these companies supporting PostGIS?”
OSSIM
BDuni
Transactional Maintenance Check in edits
Check out working areas
DB Evaluation •
Can DB handle 100M spatial features?
•
Can DB do spatial transactions?
•
PostGIS? DB2? Oracle?
•
Yes! Yes! Yes!
Scalability “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
Price
VALUE
Functionality
What’s New?
PostGIS 1.4 •
January 2009
•
Prepared geometry
•
Cascaded union
•
Curves
•
GeoJSON
Prepared geometry
Point in Polygon
Point in Polygon = O(n)
Line in Polygon = O(n•m)
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!
Prepared geometry makes repeated tests on large geometries very fast.
SELECT ... FROM points, polygons WHERE ST_Contains( polygons.geom, points.geom )
Cascaded union
Cascaded union
Curves •
CURVESTRING
•
COMPOUNDCURVE
•
CURVEPOLYGON
ST_AsGeoJSON() {"type":"LineString","coordinates":[[0,0],[1,1]]}
http://geojson.org
PostGIS 1.5 •
December 2009
•
Geography type
GeoNewbies GeoHugies
Geography •
Index spherical data
•
Include selectivity / estimation
•
ST_Distance()
•
ST_DWithin()
•
ST_Area()
•
Casts to/from GEOMETRY
PostGIS 2.0 •
December 2010
•
Typmod support •
GEOMETRY_COLUMNS as view
•
More geography, More performance
•
New index support •
•
KD-Tree, 3D, 4D, Nearest Neighbor
Breaking changes
Thanks! Paul Ramsey [email protected]
