Geodatabase Programming With SQL Dale Brooks
Presentation Outline
Introduction - 5 min Presentation - 30 min Q&A - 10 min Post Session Q&A – I’m available.
2/15/2017
DevSummit DC - Geodatabase Programming With SQL
2
Subject Matter
This presentation is about using SQL to access and process geodata. Oracle is used for ST_Geometry examples. (explicit or derived)
For various leading RDBMS platforms, the Esri ST_Geometry implementation and/or the RDBMS provider’s own Geometry Type implementation exist.
2/15/2017
DevSummit DC - Geodatabase Programming With SQL
3
RDBMS Platforms and Spatial Data Types Choices
Oracle – Esri ST_Geometry, Oracle SDO_Geometry PostgreSQL – Esri ST_Geometry, PostGIS Geometry SQLite – Esri ST_Geometry
SQL Server/Azure – Geometry and Geography Types IBM Informix –Informix Spatial Type IBM DB2 – IBM DB2 Spatial Type
4
Geometry Column on an RDBMS Table
2/15/2017
DevSummit DC - Geodatabase Programming With SQL
5
Geometry in a Table Column Springs Table
Geodatabase with Layers
2/15/2017
Springs Layer
DevSummit DC - Geodatabase Programming With SQL
6
Esri ST_Geometry Type for the “Shape” column SPRING_POINTS
SPRING_LINES
SPRING_AREAS
http://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/what-is-the-st-geometry-storage-type.htm 2/15/2017
DevSummit DC - Geodatabase Programming With SQL
7
The Esri ST_Geometry Type
The ST_Geometry data type implements the SQL 3 specification of userdefined data types (UDTs), allowing you to create columns capable of storing spatial data such as the location of a landmark, a street, or a parcel of land. It provides International Organization for Standards (ISO) and Open Geospatial Consortium, Inc. (OGC) compliant structured query language (SQL) access to the geodatabase and database. This storage extends the capabilities of the database by providing storage for objects (points, lines, and polygons) that represent geographic features
8
The “SHAPE” Column (Oracle) Inside the SHAPE “Column” SPRING_POINTS
SPRING_LINES
SPRING_AREAS
2/15/2017
Name
Type
ENTITY
NUMBER(38)
NUMPTS
NUMBER(38)
MINX
FLOAT(64)
MINY
FLOAT(64)
MAXX
FLOAT(64)
MAXY
FLOAT(64)
MINZ
FLOAT(64)
MAXZ
FLOAT(64)
MINM
FLOAT(64)
MAXM
FLOAT(64)
AREA
FLOAT(64)
LEN
FLOAT(64)
SRID
NUMBER(38)
POINTS
BLOB
DevSummit DC - Geodatabase Programming With SQL
9
SQL Statements That Create the Springs Table CREATE TABLE DEMO.SPRINGS ( OBJECTID NUMBER(*,0) NOT NULL ENABLE, ... SHAPE SDE.ST_GEOMETRY ); CREATE INDEX DEMO.A2_IX1 ON DEMO.SPRINGS (SHAPE) INDEXTYPE IS SDE.ST_SPATIAL_INDEX PARAMETERS ('ST_GRIDS = 7120.2184985 ST_SRID = 300002');
2/15/2017
DevSummit DC - Geodatabase Programming With SQL
10
Esri ST_Geometry Functions
2/15/2017
DevSummit DC - Geodatabase Programming With SQL
11
ESRI ST_Geometry Functions
2/15/2017
CONSTRUCTORS
ACCESSORS
OPERATORS
RELATIONSHIP
DevSummit DC - Geodatabase Programming With SQL
12 12
ST_Geometry Constructor Functions
Common ways to make geometries for the SHAPE column include: -
ST_Geometry
-
ST_Point
-
ST_Linestring
-
ST_Poly
The 34 functions address all the types shown in the ST_Geometry type diagram (earlier slide). Some functions are specific to a given RDBMS, and this is noted in the doc.
http://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/constructor-functions.htm 2/15/2017
DevSummit DC - Geodatabase Programming With SQL
13
ST_Geometry Spatial Accessor Functions
Spatial accessor functions return a given property of a geometry (explicit or derivable) : •
Geometry type
•
X,Y,Z coordinates
•
Shape envelope
•
SRID
•
Vertices, Count of Vertices
•
Length and area
•
Empty/Non-Empty Status
•
See URL below for others not listed here. http://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/spatial-accessorfunctions.htm#ESRI_SECTION1_AEAC8D180F754C87B6D40EF1A7689B26
2/15/2017
DevSummit DC - Geodatabase Programming With SQL
14
ST_Geometry Relational Functions Relational functions use predicates to test for different types of spatial relationships. These are: •
ST_Contains
•
ST_Crosses
•
ST_Disjoint
•
ST_Equals
•
ST_Intersects
•
ST_Overlaps
•
ST_Relate
•
ST_Touches
•
ST_Within http://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/relational-functions-for-st-geometry.htm
2/15/2017
DevSummit DC - Geodatabase Programming With SQL
15
Spatial Operations for ST_Geometry Spatial operations use geometry functions to take spatial data as input, analyze the data, then produce output data that is the derivative of the analysis performed on the input data. The operations include: •
Buffering geometry
•
ConvexHull
•
Difference of geometries
•
Intersection of geometries
•
Symmetric difference of geometries
•
Union of geometries
•
Minimum distance
•
Aggregates
•
Transformation http://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/spatial-operation-functions-for-st-geometry.htm
2/15/2017
DevSummit DC - Geodatabase Programming With SQL
16
Geodata Processing Performance …Another Way That’s Very Fast!
2/15/2017
DevSummit DC - Geodatabase Programming With SQL
17
Common ArcGIS Client-side Processing
Desktop Geoprocessing
Data over Network
Possible Performance Impact
Store and Retrieve
Server 18
Consider Processing on The Data Server (no processing over network)
Processing on the Server
App Servers Data Server
C C
=
RDBMS Processing Engine & Programming Language(s) Runtime (E.g. PL/SQL)
C C
Network C
C
LAN
/rdbms_data /input
2/15/2017
DevSummit DC - Geodatabase Programming With SQL
19
Geodata Processing on the Data Server
•
•
In-Database processing is typically many times faster*: -
All the data is in the database
-
No networking
-
RDBMS Processes are tuned for set processing
-
RDBMS data types -
Native
-
User Defined (UDT)
ETL vs. ELT -
Extract, Transform, and Load vs. Extract, Load, Transform
* Assume well-resourced data server
2/15/2017
DevSummit DC - Geodatabase Programming With SQL
20
Use Cases for Geodata Processing with the ST_Geometry PL/SQL API •
Text file Ingest (superior performance) -
•
•
High volume geodata processing (superior performance) -
The “T” in ELT
-
Complex report processing that includes spatial criteria – result may be simple table for non-spatial apps
Trigger based operations -
•
•
Custom replication and propagation, permanent event data monitoring, constraints
Dynamic views -
Can be registered with the Geodatabase (10.5)
-
Can be used with Query Layer technology
Dynamic Filters in ArcMap Definition Queries -
•
Example: Oracle SQLLDR
Invoke high volume filtering on the data server – reduce results sent to ArcMap
Fine grained auditing involving spatial criteria
2/15/2017
DevSummit DC - Geodatabase Programming With SQL
21
PL/SQL API Capabilities and Advanced Geodatabase Considerations
2/15/2017
DevSummit DC - Geodatabase Programming With SQL
22
DBA Perspective of The “Esri Enterprise Geodatabase”
SQL Access
SQL Access
Simple Feature Layer
Intelligent Feature Classes SQL Access
SQL Access
Non-spatial data
Versioned Feature Classes SDE
SQL Access
SQL Access
Spatially enabled business table
Esri system and metadata tables, stored procedures, and a data type 23
SQL Access - Example Capabilities Matrix
Use Case
DMBS Anatomy
SQL Access Notes
Simple Feature Layer
Business Table w/spatial column Spatial Index Simple layer metadata
Full SQL Read/Write, Can add DBMS level intelligence and behavior
Basic Feature Class - unversioned - no GDB intelligence added
Business Table w/spatial column Spatial Index Simple GDB-level metadata
Full SQL Read/Write, Can add DBMS level intelligence and behavior
Feature Class - versioned - no GDB intelligence added
Business Table w/spatial column Adds and Deletes tables Spatial Index on business table/Adds table Read/Write capabilities using Esri Versioning metadata versioned view framework.
Feature Class - versioned - GDB intelligence added
Same as above GDB Intelligence metadata Esri Geodatabase Logic
Some capabilities, but complex geodatabase rules. Must use ArcGIS to edit spatial or attribute data involved with GDB intelligence or behavior
24
DBMS Connections
2/15/2017
DevSummit DC - Geodatabase Programming With SQL
25
Common Geodatabase Programming Paradigm Python w/ArcPy
ArcGIS .NET/Java/C++ and Esri Objects Esri Geodatabase Logic ArcSDE Technology
Geodatabase/ DBMS Connection
Esri Geodatabase Data and Metadata
26
SQL Programming Paradigm
Any program using DBMS drivers, In-database procedure code
SQL Access DBMS Connection
Esri Geodatabase Data and Metadata
DBMS Data Type in a column, SQL API (functions for spatial type)
27
Both Programming Paradigms Accessing Same Data Any program using DBMS Drivers, In-Database procedure code
SQL Access
DBMS Connection
Esri Geodatabase Data and Metadata
ArcGIS .NET/Java/C++ and Esri Objects Esri Geodatabase Logic ArcSDE Technology
DBMS Data Type in a column, Geodatabase/ DBMS Connection
SQL API (functions for spatial type)
28
Known DBMS Connection Management Strategies • DBMS
Connection management
DBMS Connection
-
Connection pooling within application
-
Complex ownership/management of connections by specialized enterprise application
-
(Oracle example) Proxy User connections
-
(Oracle example) Fast session-switching
-
(Oracle example) Transparent Application Failover on Oracle RAC
29
Q&A
[email protected]
2/15/2017
DevSummit DC - Geodatabase Programming With SQL
30