SQL/MM%20Spatial

1
SQL/MM Spatial Manage Spatial Data in an ORDBMS
Knut Stolze ltstolze_at_de.ibm.comgt IBM Germany,
2003-02-26
2
Agenda

• Overview
• Spatial Data Types Methods
• Information Schema
• Demo
• Questions

3
Overview

• Part 3 of SQL/MM
• Part 1 - Framework
• Part 2 - Full Text
• Part 3 - Spatial
• Part 5 - Still Image
• Part 6 - Data Mining
• Derived from OGC Simple Feature Specification for
  SQL
• Currently progressed to FDIS
• IS expected for the end of the year

4
Overview (continued)

• Based on SQL-99
• Structured types, methods, ...
• The standard does not define
• Indexing mechanisms
• Implementation issues
• number, names, and types of attributes of the
  types
• specific algorithms
• Follows the SQL99 approach

5
Overview (continued)

• Store, manage, and analyse geometries as scalar
  (complex) values
• Points, lines, and polygons
• Collections

6
Overview (continued)

• Primary application today Geographic Information
  Systems (GIS)
• Only sparse integration into classical database
  applications
• Products
• IBM DB2 Spatial Extender
• IDS Spatial DataBlade
• Oracle Spatial

7
Spatial Data Types
ST_Geometry
ST_Surface
ST_Curve
ST_GeomCollection
ST_Point
ST_CurvePolygon
ST_MultiSurface
ST_MultiCurve
ST_MultiPoint
ST_MultiPolygon
ST_MultiLineString
ST_Polygon
ST_LineString
ST_CircularString
ST_CompoundCurve
8
Spatial Data Types - Discussion -

• Attempt to implement Composite Design Pattern
• originated from OGC class hierarchy
• pattern cannot be implemented in SQL
• ST_Point unrelated to ST_MultiPoint,
  ST_LineString unrelated to ST_MultiLineString,
  ST_Polygon unrelated to ST_MultiPolygon
• further processing of results results oftentimes
• in more complex SQL
• Types not properly mirrored between
• single-part and multi-part subtrees
• of the hierarchy

9
Spatial Data Types - Discussion - (continued)

• Inconsistent handling of empty geometries
• Possible type hierarchy to address the issues

ST_Geometry
ST_MultiSurface
ST_MultiCurve
ST_MultiPoint
ST_Empty
ST_MultiPolygon
ST_MultiCircString
ST_MultiLineString
ST_Point
ST_CircularString
ST_LineString
ST_Polygon
10
Spatial Methods
ST_Length ST_StartPoint ST_EndPoint ST_IsClosed ST
_IsRing ST_NumPoints ST_PointN ST_NumCurves ST_Cur
veN ST_Area ST_Perimeter ST_Centroid ST_PointOnSu
rface ST_ExteriorRing ST_InteriorRings ST_NumInter
iorRing ST_InteriorRingN
ST_Distance ST_Equals ST_Relate ST_Disjoint ST_Int
ersects ST_Touches ST_Crosses ST_Within ST_Contain
s ST_Overlaps
ST_Geometry ST_Point ST_LineString ST_CircularStri
ng ST_CompoundCurve ST_CurvePolygon ST_Polygon ST_
GeomCollection ST_MultiPoint ST_MultiLineString ST
_MultiPolygon ST_AsText ST_AsBinary ST_AsGML ST_T
ransform
ST_Dimension ST_CoordDim ST_GeometryType ST_SRID S
T_IsEmpty ST_IsSimple ST_IsValid ST_Boundary ST_E
nvelope ST_ConvexHull ST_Buffer ST_Intersection ST
_Union ST_Difference ST_SymDifference ST_X ST_Y
11
Spatial Methods - Format Conversion -

• Well-known Text Representation
• point (10 10)
• multipolygon (((1 1, 2 2, 1 2, 1 1)),((10 10, 10
  20, 20 20, 20 10, 10 10)))
• Well-known Binary Representation
• x'010100000000000000000024400000000000002440'
• Geography Markup Language
• ltgmlPointgtltgmlcoordgtltgmlXgt10lt/gmlXgt
• ltgmlYgt10lt/gmlYgtlt/gmlcoordgtlt/gmlPointgt
• (Additional formats in products, e.g. Shape
  format)

12
Spatial Data Methods - Discussion -

• Duplicated functionality
• ST_Overlaps, ST_Intersects, ST_Crosses
• ST_GeometryType SPECIFIC_TYPE (SQL99)
• Additional functionality already supported by
  products
• ST_ShortestPath (in current WD)
• Z/M coordinate support
• Other external data representations
• ST_Generalize to simplify geometries

13
Information Schema
associated
ST_SPATIAL_ REFERENCE_SYSTEMS
ST_GEOMETRY_ COLUMNS
ST_UNITS_OF_ MEASURE
is subset
COLUMNS (SQL99)
ST_SIZINGS
14
Information Schema - Discussion -

• Merge ST_SIZINGS view with SIZINGS (SQL99)
• additional facilities in SQL99 needed, first
• ST_SPATIAL_REFERENCE_SYSTEMS is rather primitive,
  EPSG uses
• Coordinate Axis Name, Coordinate Axis,
• Coordinate System, Coordinate Reference System,
• Coordinate Operation, Ellipsoid, Datum,
• Prime Meridian, Coord_Op Method,
• Coord_Op Parameter,
• Coord_Op Parameter Usage,
• Coord_Op Parameter Value,
• Coord_Op Path

15
DEMO
16
Questions??