Transcript Lesson 4 - Object Relational Model

Object Relational Model
Creating Spatial Tables
Oracle spatial – Creating spatial tables
Concepts
•
Describe the schema associated with a
spatial layer
•
Explain how spatial data is stored using
the Oracle Spatial object-relational model
•
Create the table associated with a spatial
layer
Oracle spatial – Creating spatial tables
SDO_GEOMETRY Object
SDO_GEOMETRY Object
sdo_gtype
sdo_srid
sdo_point
sdo_elem_info
sdo_ordinates
NUMBER
NUMBER
SDO_POINT_TYPE
SDO_ELEM_INFO_ARRAY
SDO_ORDINATE_ARRAY
Example
SQL> CREATE TABLE
2
state
3
totpop
4
geom
states (
VARCHAR2(30),
NUMBER(9),
MDSYS.SDO_GEOMETRY);
Oracle spatial – Creating spatial tables
SDO_GEOMETRY Object (cont.)
SDO_POINT_TYPE
x
y
z
NUMBER
NUMBER
NUMBER
SDO_ELEM_INFO_ARRAY
VARRAY (1048576) OF NUMBER
SDO_ORDINATE_ARRAY
VARRAY (1048576) OF NUMBER
Oracle spatial – Creating spatial tables
SDO_GTYPE
•
Defines the kind of geometry stored in the
object
•
d = number of dimensions e.g. 2, 3 or 4
GTYPE
Explanation
d000 UNKNOWN_GEOMETRY
d001 POINT
d002 LINESTRING
d003 POLYGON
d004 HETEROGENEOUS COLLECTION
Spatial ignores this geometry.
Geometry contains one point.
Geometry contains one line string.
Geometry contains one polygon.
Geometry is a collection of elements of
different types: points, lines, polygons
Geometry has multiple points.
Geometry has multiple line strings.
Geometry has multiple polygons
d005 MULTIPOINT
d006 MULTILINESTRING
d007 MULTIPOLYGON
Oracle spatial – Creating spatial tables
SDO_SRID
•
Defines the coordinate system («Spatial
Reference System») used for this geometry
•
Integer value matching a Spatial Reference
Id defined in the MDSYS.CS_SRS table
•
If not specified, use the spatial reference
system defined at layer level (in spatial
metadata) - if any
Oracle spatial – Creating spatial tables
SDO_POINT
•
This type should not be used outside of the
SDO_GEOMETRY object.
•
Optimized space for storing points (not
point clusters).
•
Ignored if SDO_ELEM_INFO and
SDO_ORDINATES are not NULL
Oracle spatial – Creating spatial tables
SDO_ELEM_INFO
• Entries in the array should be considered in
groups of three.
•
The triplet values stored in the this array are
interpreted as:
– Ordinate offset - The position of the first
ordinate of an element in the sdo_ordinates
array. Values start at 1.
– Element type
– Interpretation - Straight line, Circular arc or
header
Oracle spatial – Creating spatial tables
SDO_ORDINATES
• This is a simple array of numbers
• Contains the ordinates that make up geometry
elements
•
Elements stored in the SDO_ORDINATES array are
defined in the SDO_ELEM_INFO array
Oracle spatial – Creating spatial tables
Element Example: Point
• Point optimized for space
• Spatial index is 2 dimensional, x and y.
• To generate a spatial index on SDO_POINT,
(x, y, z)
SDO_ELEM_INFO and SDO_ORDINATES must be NULL.
SQL> INSERT INTO telephone_poles
2>
VALUES (attribute_1, …. attribute_n,
3>
mdsys.sdo_geometry (
4>
3001, null,
5>
mdsys.sdo_point_type (43.7,-75.2,200),
6>
null, null)
7>
);
Oracle spatial – Creating spatial tables
Element Types in SDO_ELEM_INFO
Element Types
0 UNKNOWN_ELEMENT
Interpretation
1 POINT
# of points in collection
2 LINESTRING
1 - Straight lines
2 - Circular arcs
n003 POLYGON
1 - Straight lines
2 - Circular arcs
3 - Optimized rectangle
4 - Circle
4 COMPOUND LINESTRING
# of type 2 sub-elements that make up the linestring
n005 COMPOUND POLYGON
# of type 2 sub-elements that make up the polygon
Oracle spatial – Creating spatial tables
Element Example: Line String
Ordinate offset
1
Interpretation
1
Element type
2
(x2,y2)
(x1,y1)
•
(x3,y3)
(x4,y4)
Line segments that close to form a ring have no
implied interior
• Line segments must be contiguous
Oracle spatial – Creating spatial tables
Element Example: Arc String
Ordinate offset
1
Interpretation
2
Element type
2
(x2,y2)
(x4,y4)
(x1,y1)
(x5,y5)
(x7,y7)
(x3,y3)
(x6,y6)
•
Each arc is defined by three points on the
circumference of a circle
•
•
Last point from one arc is the first point of next arc
•
Arcs must be contiguous
Arcs that close to form a ring have no implied
interior
Oracle spatial – Creating spatial tables
Element Example: Polygon
Ordinate offset
1
Element type
1003
Interpretation
1
(x5,y5)
(x6,y6)
(x4,y4)
(x1,y1)
(x2,y2)
•
(x3,y3)
Interpretation 1 - All line segments are straight
lines
• Area is implied
• Line segments cannot cross each other
Oracle spatial – Creating spatial tables
In this lecture
•
See more examples of possible
geometries in Oracle Spatial
•
•
•
•
Encoding polygons
Constructing geometries
Metadata
Topology in Oracle Spatial
Oracle spatial – Creating spatial tables
Element Example: Arc Polygon
Ordinate offset
1
Interpretation
2
Element type
1003
(x6,y6)
(x5,y5)
(x7,y7)
(x8,y8)
(x4,y4)
(x9,y9)
(x1,y1)
(x3,y3)
(x8,y8)
(x2,y2)
•
Interpretation 2 - All line segments are circular
arcs
•
•
Area is implied
Arcs can not cross each other
Oracle spatial – Creating spatial tables
Element Example: Rectangle
Ordinate offset
1
Element type
1003
Interpretation
3
(x2,y2)
(x1,y1)
•
Optimal storage - Defined by lower left point, upper
right point
• Area is implied
Oracle spatial – Creating spatial tables
Element Example: Circle
Ordinate Offset
1
Interpretation
4
Element Type
1003
(x1,y1)
(x2,y2)
(x3,y3)
• Defined by any three points on the circumference
• Area is implied
Oracle spatial – Creating spatial tables
Element Example: Compound Line
String
Ordinate Offset
Element Type
1
1
5
13
4
2
2
2
Interpretation
3
1
2
1
(x4,y4)
(x5,y5)
(x3,y3)
(x6,y6)
(x9,y9)
(x7,y7)
(x8,y8)
(x2,y2)
•
•
•
•
(x1,y1)
First triplet (header) defines the number of sub-elements
Sub-elements must be contiguous
Arcs and line segments of sub-elements can cross
Element types 4 can ONLY contain element types 2
Oracle spatial – Creating spatial tables
Element Example: Compound Polygon
Ordinate Offset
Element Type
Interpretation
1
1
5
1005
2
2
2
1
2
(x6,y6)
(x4,y4)
(x7,y7)
(x1,y1)
(x5,y5)
(x3,y3)
(x2,y2)
•
•
•
•
•
First triplet (header) defines the number of sub-elements
Sub-elements must be contiguous
Arcs and line segments of sub-elements can cross
Element types 5 can ONLY contain element types 2
Area is implied
Oracle spatial – Creating spatial tables
Element Example: Unknown geometry
Ordinate Offset
1
11
Element Type
0
1003
Interpretation
1324
3
(x7,y7)
(x4,y4)
(x3,y3)
(x5,y5)
(x1,y1)
(x2,y2)
(x6,y6)
• Element type 0 is ignored by Oracle Spatial
• Element type 0 is for modeling unsupported element types
(I.e. curves, splines etc…)
• A geometry with an element type 0 must contain at least one
element of type 1,2,3,4 or 5. The non 0 element is an
approximation of the unsupported geometry. The
approximation is indexed by Oracle Spatial
Oracle spatial – Creating spatial tables
Collection Example: Point cluster
Ordinate Offset
1
Element type
1
(x1,y1)
Interpretation
5
(x3,y3)
(x5,y5)
(x2,y2)
(x4,y4)
•
Interpretation is the number of points in the cluster
Oracle spatial – Creating spatial tables
Collection Example: Multi Line String
Ordinate offset
1
7
Interpretation
1
2
Element type
2
2
(x2,y2)
(x1,y1)
•
(x5,y5)
(x3,y3)
(x4,y4)
Elements can be line strings, arc strings or
compound line strings
Oracle spatial – Creating spatial tables
(x6,y6)
Collection Example: Multi Polygon
Ordinate offset
1
13
Interpretation
1
4
Element type
1003
1003
(x5,y5)
(x6,y6)
(x4,y4)
(x1,y1)
(x8,y8)
(x7,y7)
(x9,y9)
(x2,y2)
•
(x3,y3)
Elements can be polygons, arcs polygons, or
compound polygons
Oracle spatial – Creating spatial tables
Element example: Polygon with void
Ordinate offset
1
13
Interpretation
1
3
Element type
1003
2003
(x5,y5)
(x8,y8)
(x6,y6)
(x4,y4)
(x1,y1)
(x7,y7)
(x2,y2)
(x3,y3)
• A void can be modeled with any combination of type 3 and
type 5 elements
• Voids can contain islands and islands can contain voids
• Area is implied as the difference between the outer and inner
polygons
Oracle spatial – Creating spatial tables
Element example: Compound polygon
with void
Ordinate Offset
1
1
7
17
Element Type
1005
2
2
2003
Interpretation
2
1
2
3
(x5,y5)
(x7,y7)
(x8,y8)
(x4,y4)
(x6,y6)
(x1,y1)
(x10,y10)
(x2,y2)
(x9,y9)
(x3,y3)
• A void can be modeled with any combination of type 3 and
type 5 elements
• Voids can contain islands and islands can contain voids
• Area is implied as the difference between the outer and inner
polygons
Oracle spatial – Creating spatial tables
Encoding polygons with voids
Note:
•
Order required for boundaries
+
•
is different from
+
Orientation of coordinates:
A
B
A
F
C Not the same as B
F
E
D
E
C
Oracle spatial – Creating spatial tables
D
Ring ordering
•
•
External ring must appear before internal ring
Outer and inner rings identified by element type
1003
2003
must be
constructed as
+
1003
2003
1003
must be
constructed as
+
Oracle spatial – Creating spatial tables
+
Points orientation
•
•
Counter-clockwise for exterior rings
Clockwise for interior rings
B
A
A
C
B
F
C
E
F
D
+
D
E
Oracle spatial – Creating spatial tables
Constructing geometries
SQL> INSERT INTO lines VALUES (
2>
attribute_1, …. attribute_n,
3>
mdsys.sdo_geometry (
4>
2002, null, null,
5>
mdsys.sdo_elem_info_array (1,2,1),
6>
mdsys.sdo_ordinate_array (
7>
10,10, 20,25, 30,10, 40,10))
8>
);
(20,25)
(10,10)
(30,10)
Oracle spatial – Creating spatial tables
(40,10)
Constructing geometries
SQL> INSERT INTO PARKS VALUES(
2>
attribute_1, …, attribute_n,
3>
MDSYS.SDO_GEOMETRY(
4>
2003, null, null,
5>
MDSYS.SDO_ELEM_INFO_ARRAY
6>
(1,1005,2, 1,2,1, 7,2,2, 17,2003,3),
7>
MDSYS.SDO_ORDINATE_ARRAY
8>
(10,50,10,30,50,30,50,50,40,60,
9>
30,50,20,60,10,50,25,35,35,40 ));
(x5,y5)
(x7,y7)
(x8,y8)
(x4,y4)
(x6,y6)
(x1,y1)
(x10,y10)
(x2,y2)
(x9,y9)
(x3,y3)
Oracle spatial – Creating spatial tables
Element Types Summarized
Element Types
0 UNKNOWN_ELEMENT
Interpretation
1 POINT
# of points in collection
2 LINESTRING
1 - Straight lines
2 - Circular arcs
3 POLYGON
1 - Straight lines
2 - Circular arcs
3 - Optimized rectangle
4 - Circle
4 COMPLEX LINESTRING
# of type 2 elements that make up the linestring
5 COMPLEX POLYGON # of type 2 elements that make up the polygon
Oracle spatial – Creating spatial tables
Spatial Metadata
•
The spatial routines require you to populate a table
that contains metadata about SDO_GEOMETRY
columns
• The metadata table is created under the Oracle
user MDSYS during installation
•
The metadata table is referenced via the view
USER_SDO_GEOM_METADATA
• For every SDO_GEOMETRY column, insert a row
in the USER_SDO_GEOM_METADATA view
Oracle spatial – Creating spatial tables
USER_SDO_GEOM_METADATA
USER_SDO_GEOM_METADATA
table_name
VARCHAR2(32),
column_name
VARCHAR2(32),
diminfo
MDSYS.SDO_DIM_ARRAY
srid
NUMBER
MDSYS.SDO_DIM_ARRAY
VARRAY(4) OF SDO_DIM_ELEMENT
MDSYS.SDO_DIM_ELEMENT object
sdo_dimname
VARCHAR2(32)
sdo_lb
NUMBER
sdo_ub
NUMBER
sdo_tolerance
NUMBER
Oracle spatial – Creating spatial tables
USER_SDO_GEOM_METADATA (cont.)
•
•
•
•
•
SDO_DIMNAME
– The dimension name
SDO_LB
– Lowest possible value for this dimension
SDO_UB
– Largest possible value for this dimension
SDO_TOLERANCE
– Round-off error value used by Oracle Spatial
indexing, operators and functions.
SDO_SRID
– Spatial Reference System id (optional)
Oracle spatial – Creating spatial tables
Populating
USER_SDO_GEOM_METADATA
SQL> INSERT INTO USER_SDO_GEOM_METADATA VALUES
2
(‘ROADS’,
3
‘GEOMETRY’,
4
MDSYS.SDO_DIM_ARRAY (
5
MDSYS.SDO_DIM_ELEMENT(’Long’, -180, 180, .005),
6
MDSYS.SDO_DIM_ELEMENT(‘Lat’,
-90, 90, .005)
7
),
8
NULL
9
);
Oracle spatial – Creating spatial tables
Notes on dimensions
•
You can place more than 2 dimensions in
the SDO_DIM_ARRAY, but Oracle Spatial
Operators, Functions and Indexing will only
use the first 2 dimensions defined.
•
All layers that you want to match MUST
have the EXACT SAME bounds !
Oracle spatial – Creating spatial tables
Support for topology
•
Topology is not explicitly maintained in the
DB
•
Several topological operators and
functions have been implemented for
checking the relationships between two
geometries
•
Window and spatial join queries are also
supported
Oracle spatial – Creating spatial tables
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
Oracle spatial – Creating spatial tables
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
Oracle spatial – Creating spatial tables
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
Oracle spatial – Creating spatial tables
Topological operators: SDO_RELATE
SDO_RELATE
boolean := SDO_RELATE
( <geometry-1>,
<geometry-2>,
‘MASK=<mask>
QUERYTYPE=<querytype>
[other optional parameters]’
)
MASK: identifies the topological relation
Oracle spatial – Creating spatial tables
Summary
•
Oracle Spatial data model: elements,
geometries and layers
•
•
•
•
SDO_GEOMETRY object type
Constructing geometries
Associated Metadata
Support for Topology
Oracle spatial – Creating spatial tables