Applying and Extending Oracle Spatial Table of Contents


Table of Contents

Preface
Chapter 1: Defining a Data Model for Spatial Data Storage
Chapter 2: Importing and Exporting Spatial Data
Chapter 3: Using Database Features in Spatial Applications
Chapter 4: Replicating Geometries
Chapter 5: Partitioning of Data Using Spatial Keys
Chapter 6: Implementing New Functions
Chapter 7: Editing, Transforming, and Constructing Geometries
Chapter 8: Using and Imitating Linear Referencing Functions
Chapter 9: Raster Analysis with GeoRaster
Chapter 10: Integrating Java Technologies with Oracle Spatial
Chapter 11: SQL/MM – A Basis for Cross-platform, Inter-operable, and Reusable SQL
Appendix A: Table Comparing Simple Feature Access/ SQL and SQL/MM–Spatial
Appendix B: Use of TREAT and IS OF TYPE with ST_GEOMETRY
Index

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

                          Code Download and Errata
                          Packt Anytime, Anywhere
                          Register Books
                          Print Upgrades
                          eBook Downloads
                          Video Support
                          Contact Us
                          Awards Voting Nominations Previous Winners
                          Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
                          Resources
                          Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software