1 / 118

2009.foss4g.org

2009.foss4g.org. Spatial Database Tips & Tricks. Paul Ramsey pramsey@opengeo.org. Housekeeping. Copy workshop from DVD Download from http://xx.xx.xx.xx/xxx/spdb-003.zip Install or not Ignore me or not Examples also at http://xx.xx.xx.xx:8080/spatialdbtips. Impatient People.

dior
Download Presentation

2009.foss4g.org

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 2009.foss4g.org

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

  3. Housekeeping • Copy workshop from DVD • Download fromhttp://xx.xx.xx.xx/xxx/spdb-003.zip • Install or not • Ignore me or not • Examples also athttp://xx.xx.xx.xx:8080/spatialdbtips

  4. Impatient People They try to install without reading instructions When you see an error box during PostGIS install, click “Ignore” Remember to create “medford” (not “postgis”) database during PostGIS install

  5. Impatient People

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

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

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

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

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

  11. 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)

  18. Open Geospatial Consortium (OGC) Simple Features for SQL (SFSQL)

  19. Spatial Locator

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

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

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

  23. 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) );

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

  25. ST_Distance(‘POINT(0 0)’,’POINT(1 1)’) • What units? • ST_Distance_Spheroid() • ST_Distance_Sphere() • Indexes are not sphere aware • Spherical distance functions defined on points only

  26. Installation

  27. Installation

  28. Installation

  29. Installation

  30. Data

  31. Data • Shape files • .shp, .shx, .dbf, .prj • shp2pgsql • Other? • FME • ogr2ogr

More Related