Spatial Database Tips & Tricks

1. Spatial DatabaseTips & Tricks Paul Ramseypramsey@opengeo.org

2. It’s not dead, it’s just resting…

3. 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

4. Standard Database • Has data types • varchar • integer • real • date

5. Spatial Database • Has spatial data types • point • linestring • polygon • multipoint • multilinestring • multipolygon

6. Standard Database • Has one-dimensional indexes • b-tree • hash

7. Spatial Database • Has spatial indexes • r-tree • quad-tree • grid

12. Find intersecting shapes…

13. Start with all boxes,

14. find intersecting boxes,

15. then find intersecting shapes.

16. Standard Database • Has functions • Work against standard types • lower()‏ • round()‏ • substring()‏ • trim()‏ • dayofweek ()‏

17. 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)‏

20. Spatial Locator

21. L O C A T O R • No buffer operation • No union operation • No intersection operation • No centroid point • No area or length calculation

22. 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

23. SFSQL compliant • New release, not as many features • No coordinate reference system transforms • Windows only • Grid index

24. 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)‏ );

25. PostgreSQL / PostGIS • SFSQL compliant • Open source (GPL)‏ • Proprietary / open source clients • “geographic” coordinates require care