You are previewing Applying and Extending Oracle Spatial.
O'Reilly logo
Applying and Extending Oracle Spatial

Book Description

This guide takes you straight into the attributes of Oracle Spatial and teaches you to extend, apply, and combine them with other Oracle and open source technologies. A vital manual for solving everyday problems.

  • Understand how to develop Oracle Spatial data models and applications that use PL/SQL and Java to solve common problems using practical, hands-on examples

  • Design a data model for spatial applications

  • Use standard database technologies for managing Spatial data

  • Learn how to combine spatial and non-spatial data in the database

  • Get to grips with how to use Oracle Spatial's standards compliant geometry data types to develop cross-vendor database solutions to common problems

  • In Detail

    Spatial applications should be developed in the same way that users develop other database applications: by starting with a good data model and using as many of the database features as possible for managing the data. If a task can be done using a database feature like replication, then it should be done using the standard replication technology instead of inventing a new procedure for replicating spatial data. Because Oracle Spatial's offerings are standards compliant, the book shows how Oracle spatial technology can be used to build cross-vendor database solutions.

    "Applying and Extending Oracle Spatial" assumes a basic knowledge of Oracle in terms of understanding the SDO_GEOMETRY type, creating tables, indexes and views, executing basic to moderately complex queries and some idea of PL/SQL programming of triggers and stored procedures. The book shows you the clever things that can be done not just with Oracle Spatial on its own, but in combination with other database technologies. This is a great resource book that will convince you to purchase other Oracle technology books on non-spatial specialist technologies because you will finally see that “spatial is not special: it is a small, fun and clever part of a much larger whole”.

    This book starts with a detailed data model that can be used in land management applications. It then shows different database features that can be used to manage the spatial data just like any other data in the database. It will also walk you through some of the basic concepts of Oracle Spatial that will help you manage the data better in the database. It will also show how PL/SQL and Java stored procedures programming can help you extend and apply spatial data processing to implement the many data management and processing tasks that are typically done via a GIS.

    This book starts where all database technology books should start: the data model. It then moves on to show the different tools and technologies that can be applied against that data model/database to solve problems such as additional views, replication, triggers and other programming. Finally, the book concludes by showing how standards can be used to implement a data model and processing across more than one vendor's database.

    This book on applying and extending Oracle Spatial is aimed at broadening how practitioners conceive of and implement spatial data processing. The powerful insights "Applying and Extending Oracle Spatial" offers are not definitive, but they are offerings from two experts who present a powerful understanding on how Oracle Spatial and related technologies offer a platform for spatial data storage and processing that is seldom heard.

    Table of Contents

    1. Applying and Extending Oracle Spatial
      1. Table of Contents
      2. Applying and Extending Oracle Spatial
      3. Credits
      4. About the Authors
      5. About the Reviewers
        1. Support files, eBooks, discount offers and more
          1. Why Subscribe?
          2. Free Access for Packt account holders
          3. Instant Updates on New Packt Books
      7. Preface
        1. What this book covers
        2. What you need for this book
        3. Who this book is for
        4. Conventions
        5. Reader feedback
        6. Customer support
          1. Downloading the example code
          2. Downloading the color images of this book
          3. Errata
          4. Piracy
          5. Questions

      8. 1. Defining a Data Model for Spatial Data Storage
        1. Defining a sample schema
          1. Defining the data model
          2. Creating tables in the schema
          3. Understanding spatial metadata
          4. Spatial Reference System
          5. More on Spatial Reference Systems
          6. Creating spatial metadata
          7. OGC-defined metadata views
          8. Tolerance in Oracle Spatial
          9. Managing homogeneous and heterogeneous data
          10. How metadata is used
          11. Using database check constraints
          12. Multiple representations for the same objects
          13. Point representation for polygon objects
          14. Alternate representation in a different coordinate system
          15. Using generalized representation
        2. Implementing spatial constraints in the database
          1. Constraining the geometry type
          2. Implementing more complex triggers
          3. Fixing invalid data using triggers
        3. Constraints with asynchronous triggers
          1. Creating a queue
          2. Defining the trigger
          3. Implementing rule-based constraints
          4. Defining rules for the schema
          5. Trigger for checking spatial relationships
          6. Batch processing existing data
        4. Summary
      9. 2. Importing and Exporting Spatial Data
        1. Extract, transform, and load (ETL) tools
          1. ETL processing with GeoKettle
          2. Loading Shapefiles
            1. Java Shapefile loader
          3. Using Map Builder
          4. Using SQL, Application Express, and Excel
          5. CSV files as external tables
        2. Storage resolution versus resolution used by functions
          1. Precision and accuracy
          2. Storage cost for each coordinate
          3. Storage cost for Oracle Spatial tables
          4. Effect of tolerance on performance of Oracle Spatial functions
        3. Creating spatial autocorrelation via clustering
          1. Space-filling curves
        4. Geometry validation and methods to clean imported data
        5. Coordinate system transformation techniques
        6. Spatial indexing
          1. Layer GTYPE for point data
          2. The Work tablespace
          3. DML batch size
          4. Non-leaf index table
        7. Exporting formats – GML, WKT, and GeoJSON
          1. Generating a GeoJSON format
        8. Summary
      10. 3. Using Database Features in Spatial Applications
        1. Using row-level and statement-level triggers
          1. Avoiding the mutating table problem
        2. Understanding materialized views
          1. Materialized view restrictions with geometry columns
        3. Logging changes independently of applications
        4. Flashback queries
          1. Flashback table
          2. Flashback drop
          3. Flashback query
          4. Flashback versions query
        5. AWR reports
        6. Database replay
          1. Workload capture
          2. Workload processing
          3. Workload replay
          4. Analysis and reporting
        7. Workspace Manager
          1. Version-enabling tables
          2. Creating and using workspaces
          3. Conflict resolution
          4. Workspace locking
          5. DDL operations on version-enabled tables
          6. Valid-time support
          7. Other features of the Workspace Manager
        8. SecureFiles compression
        9. Summary
      11. 4. Replicating Geometries
        1. Introducing different types of replication
        2. Replicating data with materialized views
        3. Streams based replication
          1. Setting up the database
          2. Enabling replication
          3. Extending streams to support SDO_GEOMETRY
          4. Enabling EDS for geometry tables
        4. Physical and logical standby database
          1. Physical standby
          2. Logical standby
            1. Setting up a logical standby database
            2. Skipping rules
            3. Working with logical standby
            4. Synchronizing the standby and primary databases
        5. OLTP and OLAP databases
          1. Spatial OLAP (SOLAP)
        6. Summary
      12. 5. Partitioning of Data Using Spatial Keys
        1. Introduction to partitioning
          1. Partitioning methods
        2. Partitioning of spatial tables
          1. Partitioning spatial indexes
          2. Creating spatial local indexes
        3. Spatial partitioning of tables
          1. Single column key
            1. Spatial partition pruning
          2. Multi-column partition key
          3. Combining spatial and non-spatial partitioning
        4. Space curve based partitioning
          1. Spatial partitioning versus non-spatial partitioning
        5. Parallel queries and partitioning
        6. High performance loading
          1. Loading with a staging table
          2. Loading without a staging table
        7. Summary
      13. 6. Implementing New Functions
        1. Background to programming SDO_GEOMETRY
          1. Exposing additional SDO_GEOMETRY properties
          2. Permissions
        2. Examining an SDO_GEOMETRY's dimensionality
          1. First principles
          2. Reusing the existing ST_GEOMETRY ST_Dimension method
        3. Understanding and using SDO_ORDINATES
          1. Rounding ordinate values
          2. Background
            1. Situations where applying precision may be necessary
            2. What is a suitable precision of an ordinate?
            3. Implementing an ordinate rounding function
            4. Swapping ordinates
        4. Understanding and using SDO_ELEM_INFO
          1. Unpacking SDO_ELEM_INFO in SQL and PL/SQL
          2. Dimensionality of compound objects
          3. Detecting if circular arcs exist
          4. Testing optimized rectangles
          5. Counting the rings of a polygon
            1. Examining the operation of the ST_NumRings function
          6. Counting compound subelements
        5. Extracting and filtering SDO_GEOMETRY elements
          1. Introducing the Oracle SDO_UTIL.Extract function
          2. Scenario 1 – Extracting geometries after intersection
            1. Implementing the ST_Extract function
          3. Scenario 2 – filtering rings of a polygon by area
            1. Implementing the ST_FilterRings function
          4. Scenario 3 – Extracting rings as separate geometries
            1. Implementing the ST_ExtractRings function
          5. Scenario 4 – Extracting all geometry subelements as separate geometries
            1. Fragmenting a geometry – ST_ExplodeGeometry
        6. Vectorizing geometries with linestrings
          1. Implementing vectorization – ST_Vectorize
            1. Using vectorization
        7. Packaging or encapsulation functions
          1. Calling the same processing in different ways
            1. Implementing a PL/SQL package
          2. Implementing user object types
            1. Creating a custom object type
            2. Including default SDO_GEOMETRY functions
            3. Sorting geometries
              1. Implementing an ordering function
        8. Packaging summary
        9. Summary
      14. 7. Editing, Transforming, and Constructing Geometries
        1. Inserting, modifying, and deleting coordinates
          1. Identifying an arc point – ST_inCircularArc
          2. Implementing ST_InsertVertex
            1. Testing ST_InsertVertex
          3. Implementing ST_UpdateVertex
            1. Testing ST_UpdateVertex
          4. Implementing ST_DeleteVertex
            1. Testing ST_DeleteVertex
          5. Real world example
        2. Extending a linestring
        3. Translating, rotating, scaling, and reflecting
          1. Introducing a set of transformation member functions
          2. Example one – shifting a geometry's position
          3. Example two – duplicating a road centerline using reflection
        4. Splitting linestring geometries – ST_Split
        5. Moving/shifting lines parallel to the original object
          1. Shifting a line sideways – ST_LineShift
          2. Right of carriageway alongside land parcel boundary
          3. Creating truly parallel lines – ST_Parallel
        6. Buffering one side of a linestring – ST_OneSidedBuffer
          1. Generating a square buffer – ST_SquareBuffer
        7. Tiling a vector geometry – ST_Tile
          1. Applications of tiling
            1. Case 1 – creating grid cells over an archaeological site
            2. Case 2 – creating sampling patches over a road pavement
          2. Removing steps in raster to vector data – ST_SmoothTile
        8. Adjusting coordinate dimensions
          1. Reducing 3D to 2D – ST_To2D
          2. Fixing Z ordinate problems – ST_ FixZ
        9. Summary
      15. 8. Using and Imitating Linear Referencing Functions
        1. Understanding linear referencing and measures
        2. Linear referencing functions to be developed
          1. Splitting a line at a point – ST_Split
          2. Snapping a point to a line – ST_Snap
          3. Finding the measure at a point – ST_Find_Measure
          4. Calculating the offset from a line to a point – ST_Find_Offset
            1. Applying measurement and offset in a real-world example
          5. Measuring the road – ST_Add_Measure
            1. Populating the tables
          6. Modifying a linestring's measures
          7. Examining properties of a measured linestring
          8. Reversing measures and linestring directions – ST_Reverse_Measures and ST_Reverse_Linestring
          9. Calculating a linestring's centroid – ST_Centroid
          10. Creating a point at a known measure – ST_Locate_Measure
            1. A real-world example
            2. Deriving the lamp post metric
          11. Selecting and offsetting a segment – ST_Locate_Measures
            1. Mapping road surfaces
        3. Summary
      16. 9. Raster Analysis with GeoRaster
        1. Working with GeoRaster
          1. GeoRaster physical storage
            1. Pyramiding of raster data
          2. Georeferencing
        2. Loading data into GeoRaster
          1. Using GDAL to load raster data
            1. Loading multiple files into a single raster object
          2. Verification of data after the load
        3. Working with GeoRaster
          1. Coordinating system transformations of GeoRaster
          2. Visualization applications for GeoRaster
          3. Analytical applications for GeoRaster
            1. Analyzing DEM data
            2. Analyzing land cover data
            3. Mapping from cell space to model space
            4. Converting raster cells to rectangles in model space
        4. Summary
      17. 10. Integrating Java Technologies with Oracle Spatial
        1. Why Java and Oracle Spatial?
          1. Java stored procedures are complementary rather than competitive
          2. Disclaimer
        2. Sourcing available Java spatial technologies
          1. Basic requirements for Java processing
            1. Common geometry type hierarchy
              1. Choosing a geometry type hierarchy implementation
                1. Candidate 1 – the JTS Topology Suite
                2. Candidate 2 – deegree
                3. Alternate candidates
                4. Deciding between JTS Topology Suite and deegree
            2. Converting between SDO_GEOMETRY and Java geometry
              1. Considering product release cycles
            3. Sourcing existing spatial algorithms
              1. Useful algorithms
        3. Downloading the JTS source code
          1. Modifying JASPA and JTS
          2. Compiling and building a JTS .jar file
          3. Installing the JTS .jar file
            1. Checking the installation
              1. Checking by querying Oracle's Java metadata tables
              2. Checking by trying to actually execute something
        4. Creating Java Stored Procedures
          1. Our first Java function – buffering a single geometry
            1. Converting SDO_GEOMETRY TO JTS Geometry
            2. Implementing ST_Buffer
            3. Executing ST_Buffer
        5. Packaging – source code versus .jar file
          1. Compiling and loading
        6. Functions for processing two geometries
          1. Spatially (Topological) comparing two geometries – ST_Relate
            1. Java implementation
          2. Replicating existing licensed overlay functions – ST_Union
            1. Java implementation
            2. Testing the implementation
          3. Snapping geometries together
            1. PL/SQL implementation
            2. Implementing the snapping methods
            3. Testing the implementation
        7. Processing a collection of geometries
          1. Types of collections
          2. Intersecting lines – a preliminary discussion
          3. Creating land parcels
            1. Step 1 – extending the linestrings
            2. Step 2 – noding linestrings
              1. PL/SQL implementation
              2. Implementing _nodeLinestrings
              3. Testing the implementation
            3. Step 3 – build polygons
              1. Implementing ST_Polygonizer
              2. Implementing _polygon=Builder
            4. Putting it all together – forming land parcel polygons
        8. A collection of useful functions
        9. Performance of Java-based SQL processing
          1. Compilation to native code
        10. Summary
      18. 11. SQL/MM – A Basis for Cross-platform, Inter-operable, and Reusable SQL
        1. Cross-platform representation
          1. Outlining the SQL/MM ST_GEOMETRY type
          2. How are geometry objects internally organized?
          3. Storage and function execution using a singly inherited geometry type
            1. Geometry subtype implementation via constraints
              1. Accessing singly inherited subtype properties
              2. Examining function or method execution style
            2. Subtype restriction using PostgreSQL typmod
          4. Storage and function execution using a multiply inherited geometry type
            1. Geometry type restriction using a subtype
            2. Subtype inheritance issues
        2. Geometry type implementation matrix
          1. Alternate geometry type implementation
        3. Constructing cross-database SQL
          1. Is database independence possible?
        4. Programming for cross-database deployment
          1. Querying ST_POINT geometries
          2. Querying ST_LINESTRING geometries
          3. Querying ST_CIRCULARSTRING geometries
          4. Querying ST_COMPOUNDCURVE geometries
          5. Querying ST_CURVEPOLYGON geometries
          6. Querying ST_POLYGON geometries
          7. Querying ST_MULTIPOINT geometries
          8. Querying ST_MULTILINESTRING geometries
          9. Querying ST_MULTIPOLYGON geometries
          10. Putting it all together – gridding a vector object
            1. SQL Server 2012
            2. PostgreSQL
            3. Oracle ST
        5. Summary
      19. A. Table Comparing Simple Feature Access/SQL and SQL/MM–Spatial
      20. B. Use of TREAT and IS OF TYPE with ST_GEOMETRY
        1. Understanding the TREAT operator
        2. Understanding the IS OF TYPE comparison operator
      21. Index