250 likes | 397 Views
Spatial Database Tips & Tricks. Paul Ramsey pramsey@opengeo.org. It’s not dead, it’s just resting…. Motivation. Spatial databases are powerful Godlike, really You do not need “GIS software” Your database is “GIS software” You do not need “spatial middleware” See above. Standard Database.
E N D
Spatial DatabaseTips & Tricks Paul Ramseypramsey@opengeo.org
Motivation • Spatial databases are powerful • Godlike, really • You do not need “GIS software” • Your database is “GIS software” • You do not need “spatial middleware” • See above
Standard Database • Has data types • varchar • integer • real • date
Spatial Database • Has spatial data types • point • linestring • polygon • multipoint • multilinestring • multipolygon
Standard Database • Has one-dimensional indexes • b-tree • hash
Spatial Database • Has spatial indexes • r-tree • quad-tree • grid
Standard Database • Has functions • Work against standard types • lower() • round() • substring() • trim() • dayofweek ()
Spatial Database • Has spatial functions • Work against spatial types • ST_Area(geometry) • ST_Distance(geometry,geometry) • ST_Intersects(geometry,geometry) • ST_DWithin(geometry,geometry,radius) • ST_Union(geometry,geometry)
Spatial Locator
L O C A T O R • No buffer operation • No union operation • No intersection operation • No centroid point • No area or length calculation
S P A T I A L • Linear referencing system (LRS) support • Spatial analysis and mining functions and procedures (SDO_SAM package) • Geocoding support (SDO_GCDR package) • GeoRaster support • Topology data model • Network data model
SFSQL compliant • New release, not as many features • No coordinate reference system transforms • Windows only • Grid index
SELECT*FROM the_table WHERE ST_Intersects( the_geom, ST_GeomFromText('POINT(0 0)',0) ); SELECT*FROM the_table WHERE the_geom.STIntersects( geometry::STGeomFromText('POINT(0 0)',0) );
PostgreSQL / PostGIS • SFSQL compliant • Open source (GPL) • Proprietary / open source clients • “geographic” coordinates require care