E-book details

Applying and Extending Oracle Spatial. 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

Applying and Extending Oracle Spatial. 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

Siva Ravada, Simon Greener

Ebook
Spatial applications should be developed in the same way that users develop other database applications: by starting with an integrated data model in which the SDO_GEOMETRY objects are just another attribute describing entities and by 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. Sometimes solving a business problem using a PL/SQL function can be more powerful, accessible, and easier to use than trying to use external software. Because Oracle Spatial's offerings are standards compliant, this book shows you how Oracle Spatial technology can be used to build cross-vendor database solutions. Applying and Extending Oracle Spatial 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.
  • Applying and Extending Oracle Spatial
    • Table of Contents
    • Applying and Extending Oracle Spatial
    • Credits
    • About the Authors
    • About the Reviewers
    • www.PacktPub.com
      • Support files, eBooks, discount offers and more
        • Why Subscribe?
        • Free Access for Packt account holders
        • Instant Updates on New Packt Books
    • Preface
      • What this book covers
      • What you need for this book
      • Who this book is for
      • Conventions
      • Reader feedback
      • Customer support
        • Downloading the example code
        • Downloading the color images of this book
        • Errata
        • Piracy
        • Questions
    • 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
    • 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
    • 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
    • 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
    • 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
    • 6. Implementing New Functions
      • Background to programming SDO_GEOMETRY
        • Exposing additional SDO_GEOMETRY properties
        • Permissions
      • Examining an SDO_GEOMETRYs dimensionality
        • First principles
        • Reusing the existing ST_GEOMETRY ST_Dimension method
      • Understanding and using SDO_ORDINATES
        • Rounding ordinate values
        • Background
          • Situations where applying precision may be necessary
          • What is a suitable precision of an ordinate?
          • Implementing an ordinate rounding function
          • 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
            • Implementing an ordering function
      • Packaging summary
      • Summary
    • 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
    • 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
    • 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
    • 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
            • Choosing a geometry type hierarchy implementation
              • Candidate 1 the JTS Topology Suite
              • Candidate 2 deegree
              • Alternate candidates
              • Deciding between JTS Topology Suite and deegree
          • Converting between SDO_GEOMETRY and Java geometry
            • Considering product release cycles
          • Sourcing existing spatial algorithms
            • Useful 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
            • Checking by querying Oracle's Java metadata tables
            • Checking by trying to actually execute something
      • Creating Java Stored Procedures
        • Our first Java function buffering a single geometry
          • Converting SDO_GEOMETRY TO JTS Geometry
          • Implementing ST_Buffer
          • Executing ST_Buffer
      • 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
            • PL/SQL implementation
            • Implementing _nodeLinestrings
            • Testing the implementation
          • Step 3 build polygons
            • Implementing ST_Polygonizer
            • Implementing _polygon=Builder
          • Putting it all together forming land parcel polygons
      • A collection of useful functions
      • Performance of Java-based SQL processing
        • Compilation to native code
      • Summary
    • 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
            • Accessing singly inherited subtype properties
            • Examining function or method execution style
          • 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
    • A. Table Comparing Simple Feature Access/SQL and SQL/MMSpatial
    • B. Use of TREAT and IS OF TYPE with ST_GEOMETRY
      • Understanding the TREAT operator
      • Understanding the IS OF TYPE comparison operator
    • Index
  • Title: Applying and Extending Oracle Spatial. 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
  • Author: Siva Ravada, Simon Greener
  • Original title: Applying and Extending Oracle Spatial. 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.
  • ISBN: 9781849686372, 9781849686372
  • Date of issue: 2013-09-25
  • Format: Ebook
  • Item ID: e_3awi
  • Publisher: Packt Publishing