Transcript Spatial functions

TECNOLOGIAS DE ARMAZENAMENTO
DE INFORMAÇÃO ESPACIAL
Spatial data and IT
Coexistence or integration ?
• Specialty Servers For
Different Kinds Of Data
• Data Isolation
• Multiple proprietary
formats

• High Systems Admin and
 Management Costs

• Scalability Problems
• High Training Costs
• Complex Support
Problems
GIS
GIS
Applications
Enterprise IT
Database
Applications
From GIS to Spatial to Location
Yesterday
Today
Application
Monolithic
GIS
Proprietary
or Generic
DBMS
Connection
Application
Proprietary
APIs
Spatial DB
Middleware
Traditional
DBMS
Application
Open
APIs
Mapping
Server
Spatially-Aware
Database
GML
SQL
WMS
WFS
…
Where does Oracle fit in a Spatial/GIS
System
Rendering
Storage
Capture
Data
Analysis
Key Technologies
Data
Manage commercial and
customer geospatial data
(Oracle Database includes NAVTEQ
boundary data for 60+ countries)
Geocode
Convert addresses
into coordinates
Analyze
Find Proximity,
Location, Containment
Display
Add Maps & Reports
to your Application
Oracle Locator
Included in Oracle Database –
All Editions
• Support for all geometry types
– Points, lines, polygons, solids
• 2D data support
• All Spatial Searches
• Measurement functions: distance, area,
length
• Utility, tuning and validation functions
• Full Coordinate Systems support
•
•
•
•
Long Transactions
Parallel spatial query & index builds
Table Partitioning
Object Replication
Oracle Spatial
A cost option of Oracle
Database Enterprise Edition
Includes all Locator features +
• Spatial processing functions
– Buffer, centroid, convex hull, etc
– Overlays: union, intersect, minus,
…
• Spatial Aggregates
• Geocoder Engine
• Linear Referencing
• Network Data Model
• Routing engine
• GeoRaster Data Type
• Topology Data Model
• Spatial Analytical Functions
• Specialized 3D types (LIDAR, TINS)
• OGC Web Services (WFS, OpenLS)
ORACLE Spatial
SDO_GEOMETRY Object
– SDO_GEOMETRY
object:
SDO_GTYPE
NUMBER
SDO_SRID
NUMBER
SDO_POINT
SDO_POINT_TYPE
SDO_ELEM_INFO
SDO_ELEM_INFO_ARRAY
SDO_ORDINATES
SDO_ORDINATE_ARRAY
SQL> CREATE TABLE us_states (
state
VARCHAR2(30),
–2Example:
3
totpop
NUMBER(9),
4
geom
SDO_GEOMETRY);
Element Example: Point
• Load the SDO_POINT field to optimize
SQL>
INSERT
INTO US_CITIES (id, city, …, location )
point
data
2>
VALUES (203, ‘Someplace’, … ,
3>
SDO_GEOMETRY (
storage.
4>
2001, 8307,
5>
6>
7>
SDO_POINT_TYPE (-75.2, 43.7, null),
null, null)
);
Element Types Summarized
Number Element Type
Interpretation
0
UNKNOWN_ELEMENT
1
POINT
# of points in collection; or
0 - oriented point
2
LINESTRING
1 - Straight lines
2 - Circular arcs
3
1003
2003
POLYGON
(Outer)
(Inner)
1 - Straight lines
2 - Circular arcs
3 - Optimized rectangle
4 - Circle
4
COMPOUND LINESTRING
# of type 2 subelements that make up
the line string
5
1005
2005
COMPOUND POLYGON
(Outer)
(Inner)
# of type 2 subelements that make up
the polygon
Spatial Operators vs. Spatial
Functions
• Spatial operators:
– Require a spatial index on the first geometry specified in the
operator
– Take advantage of spatial indexes
– Appear only in the WHERE clause
– Implicitly transform the coordinate system of the window, if
required
• Spatial functions:
– Do not take advantage of spatial indexes
– Can be used on small tables that are not spatially indexed
– Can be used in the SELECT list and the WHERE clause
– If requiring two geometries, both must exist in the same coordinate
system.
Spatial (Topological) Relationships
A
A
A
B
B
Contains
Inside
A
B
B
Contains
Inside
Covers
Coveredby
B
OverlapBdyDisjoint
A
CoversB
On
A
Disjoint
Spatial Operators vs. Spatial
Functions
•
•
•
•
Operators
SDO_FILTER:
– Performs a primary filter only
SDO_RELATE:
– Returns all geometries that have
some spatial relationship with a
geometry
SDO_WITHIN_DISTANCE:
– Returns all geometries that are
within some distance from a
geometry
SDO_NN:
– Returns the N nearest neighbors
from a geometry
Functions
• SDO_GEOM.RELATE
– Compares two geometries
• SDO_GEOM.SDO_DISTANCE
– Measures the distance
between two geometries
SDO_RELATE Example
• Find all the parks fully inside the state of
Wyoming
SELECT p.id, p.name
FROM us_parks p, us_states s
WHERE s.state = 'Wyoming'
AND SDO_INSIDE (
p.geom, s.geom
) = 'TRUE';
SDO_GEOM.RELATE Example
SELECT c.county,
sdo_geom.relate (s.geom,'determine',c.geom, 0.5)
•relationship
Determine the relationship of the state of New Jersey
FROM
us_states s,
counties:
us_counties c
WHERE s.state = 'New Jersey'
AND s.state = c.state;
COUNTY
------------------------------Atlantic
Cape May
Cumberland
Essex
…
RELATIONSHIP
-------------COVERS
COVERS
COVERS
CONTAINS
to its