Applying and Extending Oracle Spatial

Applying and Extending Oracle Spatial
eBook: $35.99
Formats: PDF, PacktLib, ePub and Mobi formats
save 15%!
Print + free eBook + free PacktLib access to the book: $95.98    Print cover: $59.99
save 37%!
Free Shipping!
UK, US, Europe and selected countries in Asia.
Also available on:
Table of Contents
Sample Chapters
  • 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

Book Details

Language : English
Paperback : 568 pages [ 235mm x 191mm ]
Release Date : September 2013
ISBN : 184968636X
ISBN 13 : 9781849686365
Author(s) : Simon Greener, Siva Ravada
Topics and Technologies : All Books, Enterprise Products and Platforms, Enterprise, Oracle

Table of Contents

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
  • 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

                          Simon Greener

                          Simon Greener has university qualifications in geomatics, computing science, database technologies, and project management. He started his working career with mining and surveying experience. He then found his calling as a computer scientist with his first job as a database programmer on IBM mainframes for Telstra. He switched to GIS three years later through working in a multi-disciplinary GIS research team at Telecom's Research Laboratories (TRL) in Clayton, Victoria. While at TRL, he worked on projects whose outcome saw the creation of what is now Telstra's Sensis group. After leaving TRL, he worked as a lecturer and consultant for CenSIS (University of Tasmania) under Professor Peter Zwart, writing student and technical training courses. While there, he continued to consult to Telstra's Directory Services and Mobile groups. It was here that he came in contact with the Spatial DataBase Engine (SDBE) from Geographic Technologies Incorporated (GTI) and saw its potential for the management of large scale spatial databases within relational database technologies, a merging of his IT and GIS worlds. This led to the foundation of Salamanca Software Pvt Ltd (SalSoft), for which he was a Director until it was purchased by ESRI Australia in 1996. Some notable achievements while at SalSoft included helping brokers with the sale of SDBE to ESRI Inc (now ArcSDE), winning the first ArcSDE sale to Telstra to power its White pages/Yellow pages mapping portal, co-authoring a geocoding specification for Spatial Decision Systems (now Sensis), consulting for Geographic Technologies Australia on numerous projects based on Universal Press street directory data, and the creation of GeoCASE/Blueprint, the world's first data modeling tool that enabled the modeling of spatial data and relationships. In 1997, he was appointed GIS Manager for Forestry Tasmania (FT) in Hobart, Tasmania. While at FT, he architected the complete revamp of FT's GIS systems using Oracle Spatial (being one of the earliest adopters of the Sdo_Geometry implementation) as the core data management technology. He was concentrating on embedding geospatial data and processing within business systems via a value-oriented, business-centric computing model. He designed and built numerous systems during those years, the best of which was MapComposer, a three-click web-enabled business map production system that, when he left in 2005, had grown (2000-) to over 320 online uses, producing over 50,000 maps a year from a repository of over 100 different map templates (still in operation in 2013). His years at FT concluded with the writing of a GIS Strategy that saw the use of GIS increase yet the cost of the technology to the organization decrease. He left FT in September 2005 for the precarious world of self-employment. He was a sometime copyist for Directions Magazine. As a subcontractor to a Spatial distributor in Australia, he wrote a Radius Topology training course and provided Radius Topology and Oracle Spatial consulting services for them at numerous customer sites until May 2006. From May to August 2006, he was engaged by Spatial, Cambridge, UK, under a UK Government Department of Trade and Industry's GlobalWatch program to conduct research and development in relation to enhancing the export potential of their latest product, Radius Studio (this resulted in Radius Studio being integrated with Feature Data Objects – FDO technology to extend its data access capabilities). In his consulting career, he has written a spatial strategy document and conducted a database performance analysis review for a large Tasmanian Government department. He has conducted a number of Oracle spatial database best practice, tender and system, and return on investment reviews at a number of Victorian Government departments. He wrote and delivered a user requirements document for Enterprise GIS at a large Australian corporation. He also provided guidance and implementation services to an ambulance service helping integrate Oracle Spatial into a data warehouse project that used Oracle Portal, Discoverer, and Data Warehouse Builder. He delivered many solutions for a NSW water authority; and finally, he successfully completed many migration, publication, return on investment, process improvement, and database design contracts for a number of Canberra-based Federal Government departments. Simon makes available a collection of PL/SQL and Java-based sample solutions for the Oracle database via his website. He is also principal programmer for the SQL Developer spatial extension, GeoRaptor. Finally, he was awarded, the 2011 Oracle Spatial Excellence Award for Education and Research by Oracle. His technical areas of expertise include systems design and architecture (spatial and attribute), data management, and modeling in both the OLTP and OLAP spaces, and he is also an evangelist for O-RDMS-based spatial data. He is available for free-lance geospatial solutions architecture work, Java and PL/SQL programming, and he provides Oracle Spatial benchmarking and performance enhancement services. His non-technical interests are his family, friends, walking, reading, singing, and motorcycle riding.

                          Siva Ravada

                          Siva Ravada earned a PhD degree from the University of Minnesota in the field of Spatial Databases before joining Oracle's Spatial development team. He is now a Senior Director of Development at Oracle Corporation. At Oracle, Siva was one of the founding team members of the Spatial development team before taking over the team management responsibilities. Siva now manages the Spatial and MapViewer development teams at Oracle. He has more than 15 years of experience in spatial databases and application development. He has also co-authored more than 30 articles published in journals, and holds more than 30 patents. He has also presented key-note speeches at several conferences on the topics of spatial databases and GIS. Oracle is the second largest software company and the number one database company in the world.
                          Sorry, we don't have any reviews for this title yet.

                          Code Downloads

                          Download the code and support files for this book.

                          Submit Errata

                          Please let us know if you have found any errors not listed on this list by completing our errata submission form. Our editors will check them and add them to this list. Thank you.


                          - 13 submitted: last submission 06 Jan 2014

                          Errata Type: Code related

                          It is

                          From May to August 2006, he was engaged by Spatial, Cambridge, UK

                          It should be

                          From May to August 2006, he was engaged by 1Spatial, Cambridge, UK

                          Errata Type: Code related

                          It is

                          He has conducted a number of Oracle spatial database best practice

                          It should be

                          He has conducted a number of Oracle Spatial database best practice

                          Errata Type: Code related

                          It is

                          The colleagues that I would particularly like to thank include John O'Toole (Spatial Ireland)

                          It should be

                          The colleagues that I would particularly like to thank include John O'Toole (1Spatial Ireland)

                          Errata Type: Code | page: 19 

                          It is: SDO_DIM_ARRAY(SDO_DIM_ARRAY(SDO_DIM_ELEMENT(‘X’, 5900000, 

                            6100000, 0.05), SDO_DIM_ELEMENT(‘Y’,2000000, 2200000, 0.05)), 


                          It should be: SDO_DIM_ARRAY(

                          SDO_DIM_ELEMENT('X',5900000,6100000, 0.005),

                          SDO_DIM_ELEMENT('Y',2000000,2200000, 0.005)),

                          Errata Type: Code | page: 20

                          It is: SDO_DIM_ARRAY((SDO_DIM_ELEMENT(

                          It should be: SDO_DIM_ARRAY(SDO_DIM_ELEMENT(

                          Errata Type: Typo| page: 31

                          It is : supportand

                          It should be support and


                          Errata Type: Typo| page: 63

                          It iscentimter

                          It should be centimeters


                          Errata Type: Code | page: 77

                          It is Select SDO_UTIL.To_Kmlgeometry(geom) gml_feature

                          It should be

                          Select SDO_UTIL.To_Kmlgeometry(geom) kml_feature

                          Errata Type: Typo | page: 83

                          It isBefore Isert

                          It should be :  Before Insert


                          Errata Type:  Typo | Page:  148

                          It is:

                          Once the CUSTOMER_HISTOR_DATA

                          It should be:

                          Once the CUSTOMER_HISTORY_DATA

                          Errata Type:  Typo | Page:  153

                          It is created as UNUSBALE

                          It should becreated as UNUSABLE


                          Errata Type:  Typo | Page:  172

                          It is: 

                          Insert Into USER_SDO_GEOM_METADATA Values ('BAE_ADDRESSES_PART_20'

                          It should be:

                          Insert Into USER_SDO_GEOM_METADATA Values ('BASE_ADDRESSES_PART_20'

                          Errata Type:  Typo | Page:  222

                          It is : Select row_number() OVER (ORDER BY 1) As rin,


                          It should be: 

                          Select row_number() OVER (ORDER BY 1) As ring,

                          Sample chapters

                          You can view our sample chapters and prefaces of this title on PacktLib or download sample chapters in PDF format.

                          Frequently bought together

                          Applying and Extending Oracle Spatial +    Oracle Siebel CRM 8 Installation and Management =
                          50% Off
                          the second eBook
                          Price for both: £34.45

                          Buy both these recommended eBooks together and get 50% off the cheapest eBook.

                          What you will learn from this book

                          • Design a data model for a spatial application
                          • „Build and use database triggers that manage data validation and other tasks
                          • „Use Oracle's materialized view, replication, and PL/SQL technologies with a spatial database
                          • „Understand and use Oracle's raster data management technology to implement solutions within a range of applications
                          • „Explore advanced queuing for managing spatial data processing
                          • „Apply linear referencing programming to solve business problems
                          • „Discover Oracle's OGC and SQLMM-compliant geometry types when implementing solutions capable of cross-vendor database deployment
                          • „Use partitioning to manage large spatial datasets in the database
                          • „Load spatial data (raster and vector) from different data sources in the database

                          In Detail

                          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".


                          This book is an advanced practical guide to applying and extending Oracle Spatial.

                          Who this book is for

                          This book is for existing users of Oracle and Oracle Spatial and so assumes you have 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.

                          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
                          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