Oracle Spatial - SQL based Geographic data - Intro

 Oracle Spatial is a free component of various versions of Oracle database, including on-premises or cloud versions and of course the Autonomous Oracle Database. 

The Spatial option helps developers and analysts get started easily with location intelligence analytics and mapping services. It enables Geographic Information System (GIS) professionals to successfully deploy advanced geospatial applications. 

In the later post I will talk about Spatial Studio and an easy, no code, UI (User Interface) to work with spatial data. I will start with few core in-database posts first.

Few links:

Here is the spatial page from Oracle.

Here are the links to the documentation of release 19 and release 21 of the Developer Guide.

Oracle LiveLabs training of "Work with Spatial Data in Oracle Autonomous Database".

Spatial Performance Doc is here.

Geocoder Guide (Converting Address to geographic coordinates - Long/Lat) is here.

 

With Oracle Spatial we can run SQL statement such as:

Show me stores within 30 kilometers of a specific warehouse

 

Find Regions that have any interaction with Tornado Paths

 

 

Tedious Intro with Links

Oracle Spatial supports:

 

 various types of spatial data: 

• Vector data ⎻ Points, Lines, Polygons
• Raster data ⎻ Digital Imagery and Gridded Data
• GPS Tracking data ⎻ For Coinciding track analysis / GeoFence analysis
• LIDAR Data ⎻ Point Cloud / LIDAR data
• Network Model ⎻ Drive Time / Connectivity Analysis

Various types of Geometry (from point, lines and polygons to multi-polygons, solid/cuboid and multi-solid).  Here is a list of SGO_GTYPEs of 3D spatial objects.

Various types of Coordination Systems such as Cartesian, Geodetic, Projected, Local...

Various types of Operators. For example few operators for, the most common, vector data: 

Topological Relations

• Relate - Most of the following  can be covered by this function with relevant relate parameter
  
• Any Interact 
  
• Contains
• Inside
  
• Touch
• Overlaps
  
• Overlaps Disjoint
• Overlaps By Intersect
  
• Covers
• Covered By
• Equal
• Filter - To identify non-disjoint pairs
• On 
• Points In Polygon
  

Operators

• Within Distance
• Nearest Neighbor
• Nearest Neighbor Distance 
• Geom_MBR -  returns a single rectangle that minimally encloses the geometry 
• Join - Performs a spatial join based on one or more topological relationships
  

SDO_GEOM package contains subprograms for working with geometry object to check Relationship, Validations and single/two-object operations...  

• Relationship (True/False) between two objects: RELATE, WITHIN_DISTANCE

• Validation: VALIDATE_GEOMETRY_WITH_CONTEXT, VALIDATE_LAYER_WITH_CONTEXT, SDO_SELF_UNION

• Single-object operations: SDO_ALPHA_SHAPE, SDO_ARC_DENSIFY, SDO_AREA, SDO_BUFFER, SDO_CENTROID, SDO_CONVEXHULL, SDO_CONCAVEHULL, SDO_CONCAVEHULL_BOUNDARY, SDO_DIAMETER, SDO_DIAMETER_LINE, SDO_LENGTH, SDO_MBC, SDO_MBC_CENTER, SDO_MBC_RADIUS, SDO_MAX_MBR_ORDINATE, SDO_MIN_MBR_ORDINATE, SDO_MBR, SDO_POINTONSURFACE, SDO_TRIANGULATE, SDO_VOLUME, SDO_WIDTH, SDO_WIDTH_LINE

• Two-object operations: SDO_CLOSEST_POINTS, SDO_DISTANCE, SDO_DIFFERENCE, SDO_INTERSECTION, SDO_MAXDISTANCE, SDO_MAXDISTANCE_LINE, SDO_UNION, SDO_XOR

 

• Various types of Aggregate functions. Just one example - SDO_AGGR_CENTROID that returns the center of a set of objects.
• Various types of Transformers.
• Various types of Utilities.
  
• Various types of Web Services.
• And even SDO_SAM Package for Spatial Analysis and Mining (with functions like SIMPLIFY_LAYER, SPATIAL_CLUSTERS, TILED_BINS... ).
  
• Various types of Interfaces, such as SQL, PL/SQL, Java, Python, .Net, Node.js and REST.
  

 Working with SQL - Creating Table and Index

 Basically they are 4 steps setting Spatial data.

1. Have you data in a table.
2. Set Spatial column.
3. Update the USER_SDO_GEOM_METADATA table about the new spatial data source.
4. Set Spatial Index.

Steps 1&2 can be combined into one. 

We will use SDO_Geometry function to set spatial data in a column.

SDO_GEOMETRY( [geometry type] -- ID for points/lines/polygons , [coordinate system] -- ID of coordinate system , [point coordinate] -- used for points only , [line/polygon info] -- used for lines/polygons only , [line/polygon coordinates] -- used for lines/polygons only )

For example:

geometry type = 2001. The 2 indicates two-dimensional, and the 1 indicates a single point. Two-dimensional examples we will use:

ID	Type
2001	Point
2002	Line
2003	Polygon

coordinate system =4326. Represents spatial data using longitude and latitude coordinates on the Earth's surface. 

there are several thousand options of Coordination Systems we can use. You can list all of them using the following SQL:

 SELECT srid, COORD_REF_SYS_NAME from sdo_coord_ref_sys order by srid;

 

Next I will post examples of all 3 types (Points, Line and Polygon) with some variations listed bellow:

• Data points 
• Example (2 columns of Longitude/Latitude)
• Example with Function instead of Physical Geometry column
• Polygon/Area 
• Simple Example - Manual creation 
• Simple Example - Using a list of points (Long/Lat) String
• JSON based example
• Line 
• Simple Example - Manual creation 
• JSON based example