slide1 n.
Download
Skip this Video
Download Presentation
Object Relational Model Spatial Queries

Loading in 2 Seconds...

play fullscreen
1 / 23

Object Relational Model Spatial Queries - PowerPoint PPT Presentation


  • 116 Views
  • Uploaded on

Object Relational Model Spatial Queries. Spatial Layer Data. Secondary Filter Spatial Functions. Primary Filter Spatial Index. Reduced Data Set. Table where coordinates are stored. Index retrieves area of interest (window). Procedures that determine exact relationship.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Object Relational Model Spatial Queries' - seth-horne


Download Now An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
query model

Spatial LayerData

Secondary

Filter

Spatial

Functions

Primary

Filter

Spatial Index

Reduced

Data Set

Table where

coordinates are

stored

Index retrieves area of interest (window)

Procedures

that determine

exact relationship

Query Model

Exact

Result

Set

spatial operators vs functions
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 functions1
SDO_RELATE

Performs a primary and secondary filter

SDO_WITHIN_DISTANCE

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

Operators

Functions

the sdo relate operator
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
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
slide8

Topological relationships

B

A

A

A

B

B

B Inside A

A Contains B

B Covered by A

A Covers B

Touch

A

A

A red

B green

B

B

Overlap

Boundaries Intersect

Overlap

Boundaries Disjoint

Equal

A

B

Disjoint

slide9

Topologicalrelationships

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

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
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
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
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
SDO_RELATE - join vs window query
  • Find all interstates that cross county Passaic in NJ
  • Find all interstates that cross a county in Arizona

with pop density <10

This requires compatible indexes on the layers !

select i.interstate

from interstates i, counties c

where c.state = 'New Jersey' and c.county = ‘Passaic’

and sdo_relate (i.geom, c.geom,

'mask=ANYINTERACT querytype=WINDOW') = 'TRUE';

select i.interstate

from interstates i, counties c

where c.state = 'Arizona' and c.poppsqmi < 10

and sdo_relate (i.geom, c.geom,

'mask=ANYINTERACT querytype=JOIN') = 'TRUE';

optional arguments
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
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
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
SDO_WITHIN_DISTANCE Examples
  • Find all cities within a distance from an interstate
  • Find interstates within a distance from a city

select c.city, c.state_abrv

from cities c, interstates i

where highway = ‘I4’

and sdo_within_distance (

c.location, i.geom,‘distance=0.5') = 'TRUE';

select i.highway

from interstates i, cities c

where city = 'Tampa'

and sdo_within_distance (

i.geom, c.location,'distance=0.5') = 'TRUE';

sdo within distance examples1
SDO_WITHIN_DISTANCE Examples
  • Find all cities within a distance from a state

Also returns all cities in Florida !

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

the sdo nn operator
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
arguments1
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
SDO_NN Example
  • Find the 5 nearest cities from Orlando

select c1.city, c1.state_abrv

from cities c1, cities c2

where c2.city = 'Orlando'

and sdo_nn (

c1.location, c2.location,

'sdo_num_res = 6') = 'TRUE'

and c1.rowid <> c2.rowid;

sdo nn example1
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;