Applying and Extending Oracle Spatial Ebook Lubliniec

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 Ô¨Ånally see that spatial is not special: it is a small, fun, and clever part of a much larger whole. Spis treści: 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