John Pickford IBM

1. H11 Practical Applications of IDS Extensibility (Part 2 of 2) John Pickford IBM Wednesday, October 4, 2006 • 13:30. – 14:30. Platform: Informix

2. Key Points • What is Spatial Data • Spatial coordinate systems • What is the Spatial Datablade • What are Spatial Applications • Other uses

3. What is Spatial Data ? • Data related to a location • Location • Data relating to that location or a set of locations • Location is specified as a point on a co-ordinate system • Flat Earth • More realistic shaped Earth

4. Spatial co-ordinate systems • Flat Earth • Conic • A portion of the Earth's surface is projected onto a cone. The cone is then flattened • Mercator • The Earth's sphere is flattened out. In this projection the further away from the axis a point is, the larger it seems

5. Spatial co-ordinate systems

6. +90(90° N) R latitude longitude 0 -90(90° W) Spatial co-ordinate systems • Geodetic Data • Points specified by longitude and latitude • Much more complex • what is a straight line ? • what is the distance between 2 points ?

7. Where is Spatial Data Used ? • Geographical Information Systems (GIS) • Store spatial data • Analyse spatial data • Display spatial data • Could be used in CAD/CAM • Others ?

8. Some data types Basic type is a point x and y co-ordinate system z (optional) m (optional) Spatial types Point(s) Line(s) Polygon(s) Some functions Standard OGC functions Additional functions Support for different co-ordinate systems Spatial Index Support Some utilities Load shapes What is the Spatial DataBlade ?

9. Spatial Data Types • Point(s) • Line(s) • They can cross (themselves and each other) • Polygon(s) • A polygon must have the same start and end point • They can not cross themselves or each other but can touch

10. Spatial Data Types • Each point can have • A z co-ordinate (height, depth, …) • A measure (e.g. time) • The 'measure' can be interpolated

11. Spatial Functions • Convert geometries from/to: • Text • Well Known Binary (WKB) • ESRI Shape files • Convert geometries to: • GML

12. Spatial Functions • Get parameters • Dimensions of coordinates and geometry • Data type • Number of points • SRID • Max/Min of x, y, z, m • Manipulation • Get the x, y, z, m of a point • Get the first, mid, nth, last point of a line • Get the area, centre, perimeter • Get the nth geometry in a in a "multi"

13. Spatial Functions • Relationships between geometries: • Contain each other • Cross • Intersect • Overlap • Touch • Disjoint • Equal

14. Spatial Functions • Create new from existing: • Union • Intersection • Difference • Perpendicular projection • Buffer • Boundary • Envelope • Generalize

15. Spatial Functions • Nearest neighbour • SRID management • Get the SRID of a geometry • Transform from one SRID to another • Administration • Get sizes

16. Spatially Enabling a Database • Two easy steps: • Get the spatial DataBlade • Use "Blade Manager" to add it to the database

17. Inserting Spatial Data • Create table with spatial column create table gpsDemo (seRowId char(10), path st_linestring); • Insert data insert into gpsDemo values ('a', st_linefromtext('linestring zm( 0. 0. 0. 100, 1. 1. 0. 110, 2. 2. 0. 120, 3. 3. 0. 130 )', 4));

18. Selecting Spatial Data • Select: select * from gpsDemo; seRowId path a 4 LINESTRING ZM (0. 0. 0. 100, ….. select seRowId, st_envelope(path) from gpsDemo; seRowId path a 4 POLYGON ((0. 0., 3. 0., 3. 3., …..

19. File System Proprietary data format 1st Generation: Spatial Features GIS Application RDBMS SQL Attributes Proprietary GIS API RDBMS GIS Data Engine GIS Application 2nd Generation: SQL Spatial features, indexes in BLOBs Proprietary spatial structures Proprietary GIS API Open or proprietary Spatially enabled DBMS GIS Data Engine GIS Application SQL 3rd Generation: Open “Spatial” Application Spatial types functions indexes Spatial business logic Evolution of GIS Data Management

20. Service-Oriented Architecture Data Business Logic Client Service Extensible RDBMS AppServer Thin-Client Web Service Intranet Internet Spatial data Java App Browser HTTPServer Web Service EJBs Java App Extensible RDBMS Java App Spatial data Corporate Applications

21. Spatial Geodetic Grid Your idea goes here Server Subsystems Connectivity Backup Restore DataBlade Component Technology DB2/IDS

22. New Extender/ DataBlade DataBlade Elements Types Functions Casts Aggregates Indexes Tables Client Code

23. R-Tree Indexing • Not part of the Spatial Datablade but key to Spatial Data Management • Index still organised as a tree structure but they point to Regions • Not limited to 2 dimensions

24. Y X Start time Time period End time R-Tree indexing • Not limited to spatial data • Consider times and time periods

25. Spatial Clients • There are different ways to access the data from a client program • Java Class Library • ESQL/C • ODBC/JDBC

26. Server Access • Spatial data can be access in the server • SPL • UDF

27. A mini Spatial Application • A small demo to show how spatial data can be stored and displayed • Base GIS demo data distributed free with the datablade as ESRI shape files • Data loaded into IDS tables using the loadshp program • Data read as GML into a php script started by accessing the php file from a browser (Firefox 1.5.0.4 with xfrorms extension) • GML data transformed to SVG and display as graphics

28. A mini Spatial Application – Results

29. A mini Spatial Application – Results

30. A mini Spatial Application – Results

31. H11 Practical Applications of IDS Extensibility (Part 2 of 2) John Pickford IBM John.Pickford@uk.ibm.com