Transcript Lesson 7 - Object Relational Model

Object Relational Model
Spatial Queries
Query Model
Spatial Layer
Data
Table where
coordinates are
stored
Primary
Filter
Reduced
Data Set
Secondary
Filter
Spatial Index
Spatial
Functions
Index retrieves
area of interest
(window)
Procedures
that determine
exact relationship
Exact
Result
Set
Spatial Operators vs Functions
• Spatial operators:
– Take advantage of spatial indexes
– Require that spatial index exists on the first
geometry specified in the operator.
•
Spatial Functions:
– Do not take advantage of spatial indexes
– Could be used on small tables that are not
spatially indexed
Spatial Operators vs Functions
Operators
•
SDO_RELATE
–
•
•
Performs a primary and
secondary filter
SDO_WITHIN_DISTANCE
–
•
Functions
Gets all geometries that are
within some distance from a
selected geometry
SDO_NN
–
Gets the ‘n’ closest
geometries
•
SDO_GEOM.RELATE
–
To determine the relationship
between two geometries
–
To perform a spatial query
without using a spatial index
(I.e. on a small table)
SDO_GEOM.WITHIN_DISTANCE
–
Generates a buffer around a
geometry and performs a
secondary filter
Spatial Operators
The SDO_RELATE operator
boolean := SDO_RELATE
( <geometry-1>,
<geometry-2>,
‘MASK=<mask>
QUERYTYPE=<querytype>
[other optional parameters]’
)
• Performs an exact query (primary and secondary
filter)
•
Returns TRUE or FALSE
Required arguments
•
GEOMETRY-1
– A column of type SDO_GEOMETRY
•
GEOMETRY-2
– Variable or column of type SDO_GEOMETRY
•
MASK
– Identify spatial relationship to test
•
QUERYTYPE
– Valid values are JOIN or WINDOW
Topological relationships
B
A
A
A
B
B
B Inside A
A Contains B
A
B Covered by A
A Covers B
Touch
A
A red
B
B
Overlap
Boundaries
Intersect
Overlap
Boundaries
Disjoint
Equal
A
B
Disjoint
B green
Topological relationships
1. DISJOINT: boundaries and interiors do not intersect
2. TOUCH: boundaries intersect but interiors do not intersect
3. OVERLAPBDYDISJOINT: interior of one object intersects
boundary and interior of other object, but two boundaries
do not intersect (example: a line originates outside a
polygon and ends inside the polygon)
4. OVERLAPBDYINTERSECT: boundaries and interiors of the
two objects intersect
5. EQUAL: the two objects have the same boundary and interior
Topological relationships (cont.d)
6. CONTAINS: interior and boundary of one object is
completely contained in the interior of other object
7. COVERS: interior of one object is completely contained in
interior of other object and their boundaries intersect
8. INSIDE: opposite of CONTAINS; A INSIDE B implies B
CONTAINS A
9. COVEREDBY: opposite of COVERS; A COVEREDBY B
implies B COVERS A
10. ANYINTERACT: the objects are non-disjoint
SDO_RELATE - An example
•
Find all cities in a selected rectangular area
select c.city, c.pop90
from cities c
where sdo_relate (
c.location,
mdsys.sdo_geometry (2003, null, null,
mdsys.sdo_elem_info_array (1,1003,3),
mdsys.sdo_ordinate_array (-109,37,-102,40)),
'mask=ANYINTERACT') = 'TRUE';
SDO_RELATE - A window query
•
Find all counties in the state of New Hampshire
select c.county, c.state_abrv
from counties c,
states s
where s.state = 'New Hampshire'
and sdo_relate (c.geom, s.geom,
'mask=INSIDE+COVEREDBY querytype=WINDOW') = 'TRUE';
SDO_RELATE - Another window query
•
Find all counties around county Passaic
select c1.county, c1.state_abrv
from counties c1,
counties c2
where c2.state = 'New Jersey'
and c2.county = 'Passaic'
and sdo_relate (c1.geom, c2.geom,
'mask=TOUCH querytype=WINDOW') = 'TRUE';
SDO_RELATE - join vs window query
•
Find all interstates that cross county Passaic in NJ
select
from
where
and
i.interstate
interstates i, counties c
c.state = 'New Jersey' and c.county = ‘Passaic’
sdo_relate (i.geom, c.geom,
'mask=ANYINTERACT querytype=WINDOW') = 'TRUE';
•
Find all interstates that cross a county in Arizona
with pop density <10
select
from
where
and
i.interstate
interstates i, counties c
c.state = 'Arizona' and c.poppsqmi < 10
sdo_relate (i.geom, c.geom,
'mask=ANYINTERACT querytype=JOIN') = 'TRUE';
This requires compatible indexes on the layers !
Optional arguments
•
IDXTAB1
– Index table to associate with first geometry in
operator.
– By default, the primary index table is used.
•
IDXTAB2
– Index table to associate with the second
geometry in operator
– By default, the primary index table is used.
– Only supported if QUERYTYPE=JOIN
The SDO_WITHIN_DISTANCE operator
boolean := SDO_WITHIN_DISTANCE
( <geometry-1>,
<geometry-2>,
‘DISTANCE=<n>,
QUERYTYPE=<querytype>
[other optional parameters]’
)
• Performs an exact or approximate query
• Euclidean distance only
• Returns TRUE or FALSE
Arguments
•
GEOMETRY-1
– A column of type SDO_GEOMETRY
•
GEOMETRY-2
– Variable or column of type SDO_GEOMETRY
•
DISTANCE (required)
– The distance (expressed in the units used for
the coordinate system)
•
QUERYTYPE (optional)
SDO_WITHIN_DISTANCE Examples
•
Find all cities within a distance from an interstate
select
from
where
and
•
c.city, c.state_abrv
cities c, interstates i
highway = ‘I4’
sdo_within_distance (
c.location, i.geom,‘distance=0.5') = 'TRUE';
Find interstates within a distance from a city
select
from
where
and
i.highway
interstates i, cities c
city = 'Tampa'
sdo_within_distance (
i.geom, c.location,'distance=0.5') = 'TRUE';
SDO_WITHIN_DISTANCE Examples
•
Find all cities within a distance from a state
select c.city,
c.state_abrv
from cities c,
states s
where s.state='Florida'
and sdo_within_distance (
c.location, s.geom, 'distance=1.5') = 'TRUE';
Also returns all cities in Florida !
The SDO_NN operator
boolean := SDO_NN
( <geometry-1>,
<geometry-2>,
‘SDO_NUM_RES=<n>,
[other optional parameters]’
)
• Calculates the N closest geometries
• Euclidean distance only
• Returns TRUE or FALSE
Arguments
•
GEOMETRY-1
– A column of type SDO_GEOMETRY
•
GEOMETRY-2
– Variable or column of type SDO_GEOMETRY
•
SDO_NUM_RES (required)
– Number of objects to select
SDO_NN Example
•
Find the 5 nearest cities from Orlando
select
from
where
and
c1.city, c1.state_abrv
cities c1, cities c2
c2.city = 'Orlando'
sdo_nn (
c1.location, c2.location,
'sdo_num_res = 6') = 'TRUE'
and c1.rowid <> c2.rowid;
SDO_NN Example
•
Find the 10 nearest cities from Orlando ordered by
distance
select c1.city, c1.state_abrv,
sdo_geom.sdo_distance (
c1.location, c2.location, 0.005) distance
from cities c1, cities c2
where c2.city = 'Orlando'
and sdo_nn (
c1.location, c2.location,
'sdo_num_res = 11') = 'TRUE'
and c1.rowid <> c2.rowid
order by distance;