Bazy_dannykh_Uchebnoe_posobie
.pdf9.
,
- 80%
|
|
|
|
. |
|
|
|
|
, |
- |
|
|
, |
|
. |
|
|
: |
|
. |
, |
, |
, |
. |
. |
|
, |
|
. 20. |
|
|
|
|
, .
. , , ,
.
,
,
. :
, ();
k ;
.
, , . (x,y), .
:
. .
, (),
,
.
, .
,
, . , ().
.
101
MBR
,
.
. 21. MBR
.
. 22.
,
22. , R-
.
.
Open Geospatial Consortium - OGC
Open Geospatial Consortium |
|
|
250 |
, |
, |
,
, . OGC Web- http://www.opengis.org/.
OpenGIS
OpenGIS |
|
. |
. |
: |
non- |
instantiable instantiable.
. |
|
|
|
- Geometry ( |
) |
, |
|
- Point ( |
) - |
- |
. |
- Curve ( |
) |
|
, |
o |
- LineString ( |
|
) |
|
- Line |
|
|
102
|
|
- LinearRing |
|
|
|
- Surface ( |
) |
|
2 |
o |
|
Polygon ( |
) |
|
|
|
- GeometryCollection ( |
) |
|
o |
- MultiPoint ( |
) |
|
|
o |
- MultiCurve ( |
) |
|
|
|
|
- MultiLineString ( |
) |
|
o |
|
- MultiSurface ( |
) |
|
|
|
- MultiPolygon ( |
) |
|
. 23. |
|
. |
23 |
: |
|
|
1. |
|
LineString; |
|
2. |
|
LineString |
; |
3. |
|
LineString; |
|
4. |
|
LineString |
. |
:
(type),
.
(SRID - Spatial Reference Identifier).
R- (. . 17), ,
.
(X,Y) ,
().
(X,Y).
. (SRID). ,
103
,
, () .
: , .
. ,
, , . , , , . .
(MBR).
:
((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY)), .
: , .
. , (. .
18).
: , .
.
: , . .
: , (
NULL). , 0 ().
. 0, 1 2: o 0 .
o1 , .
o 2 .
OGIS
.
, GROUP BY HAVING,
. , OGIS
.
, (,
« |
», « |
», « |
», « |
»). |
|
|
, |
|
. |
, 3D .
104
60:
|
|
. |
« |
» |
|
CREATE |
TABLE |
( |
|
|
varchar(30), |
|
|
varchar(30), |
|
|
varchrar(30), |
|
|
Integer, |
|
|
Number, |
|
|
Polygon |
); |
|
|
« |
» |
|
CREATE |
TABLE |
( |
|
|
varchar(30), |
|
|
varchar(30), |
|
|
Number, |
|
|
LineString |
); |
|
|
« |
» |
|
CREATE |
TABLE |
( |
|
|
varchar(30), |
|
|
varchar(30), |
|
|
integer, |
|
|
Point |
); |
|
|
.
SQL . . OGIS 9
.
1. Touch , ,
.
,
2. |
Cross |
. |
, |
, .
3. |
Distance |
. |
. |
. WHERE SELECT . .
105
, |
, |
. , , Distance
.
Area , Length . 5. Buffer
.
,
..
- |
. |
4. |
|
|
. 24.
.
,
Buffer.
6. |
Area - |
. |
. |
. ,
, . ,
, Area
|
|
|
. |
7. |
Intersection |
. |
, . |
. Intersection
.
, () (), .
8. |
Overlap |
. |
. |
.
.
SQL
.
106
61: |
|
|
|
|
|
|
|
|
|
|
, |
|
. |
|
|
|
n Touch. |
SELECT |
C1. |
AS " |
|
" |
|
FROM |
|
C1, |
C2 |
|
|
WHERE |
(Touch(C1. |
, C2. |
|
) = 1) |
|
|
AND (C2. |
|
= |
); |
|
62: |
|
|
|
|
|
|
, |
|
|
, |
, |
|
. |
|
|
|
Cross. |
SELECT |
. |
C |
. |
|
|
FROM |
, C |
|
|
|
|
WHERE |
Cross( |
. |
, C |
. |
) = 1; |
63: |
|
|
|
|
|
|
, |
|
|
|
, |
|
, |
|
|
. |
|
Distance.
SELECT |
C1. |
, R1. |
|
|
|
FROM |
|
C1, |
R1 |
|
|
WHERE |
Distance |
(C1. |
, R1. |
) |
< |
ALL ( |
|
|
|
|
|
|
SELECT Distance(C2. |
, R1. |
) |
||
|
FROM |
|
C2 |
|
|
|
WHERE C1. |
<> C2. |
|
|
|
|
); |
|
|
|
|
WHERE Distance,
, C1 ,
R1.
,
, C2, 1., .
64:
. ,
300 |
. |
, |
. |
|
. |
|
. |
|
|
|
107 |
|
|
Overlap. |
|
SELECT |
. |
|
|
FROM |
, |
|
|
WHERE (Overlap( |
. |
, Buffer( . |
,300)) = 1) |
AND (R.Name = . |
); |
|
65:
, ,
|
|
.. |
|
|
SELECT C. |
, C. |
, Area(C. |
) AS " |
" |
FROM |
C; |
|
|
|
|
- |
, |
|
|
. |
, |
|
. |
, |
, . -
, |
, |
, |
. -,
Area - .
66:
, .
Cross,
. ()
, .
SELECT |
R. |
, C. |
, |
|
|
Length(Intersection(R. |
, C. |
)) AS " |
" |
||
FROM |
R, |
|
C |
|
|
WHERE |
Cross(R. |
|
, C. |
) = 1; |
|
67:
. ,
, .
Point(0,C..y) , , , C.. , ..y y.
108
|
|
|
, |
|
, y |
|
. |
|
|
|
. |
|
|
SELECT C |
. |
, |
|
|
|
|
Distance(Point(0, |
|
. |
.y), |
. |
) AS " |
" |
FROM |
, |
|
|
|
|
|
WHERE |
. |
= |
. |
; |
|
|
68: |
|
|
|
|
|
|
|
|
, |
|
|
- |
. |
. |
|
|
|
Touch |
|
|
|
|
|
. |
|
|
. |
SELECT |
Co. |
, Count(Co1. |
) |
|
FROM |
Co, |
Co1 |
|
|
WHERE |
Touch(Co. |
|
, Co1. |
) |
GROUP BY Co. |
|
|
|
|
ORDER BY Count(Co1. |
|
) |
|
69:
, .
,
. |
, |
, . .
SELECT |
Co.Name |
|
|
|
|
|
FROM |
|
Co, |
Co1 |
|
||
WHERE |
Touch(Co. |
|
, Co1. |
)) |
|
|
GROUP BY Co. |
|
|
|
|
|
|
HAVING |
Count(Co1. |
|
) = 1 |
|
|
|
SELECT |
Co. |
|
|
|
|
|
FROM |
|
Co |
|
|
|
|
WHERE |
Co. |
|
IN |
|
|
|
|
( |
|
|
|
|
|
|
SELECT |
Co. |
|
|
|
|
|
FROM |
|
Co, |
Co1 |
|
|
|
WHERE |
Touch(Co. |
, Co1. |
) |
||
|
GROUP BY Co. |
|
|
|
||
|
HAVING |
Count(*) = 1 |
|
|
||
|
); |
|
|
|
|
|
|
|
|
|
. |
|
|
FROM |
|
|
, |
|
, |
. |
|
GROUP BY |
|
|
|
||
|
|
|
. |
, |
HAVING |
|
|
|
|
|
|
|
109 |
,
. HAVING , WHERE, : HAVING ,
count.
69:
? .
CREATE VIEW |
|
AS |
|
|
|
( |
|
|
|
|
|
SELECT |
Co. |
|
, |
|
|
Count(Co1. |
|
) AS |
_ |
|
|
FROM |
|
|
Co, |
Co1 |
|
WHERE |
Touch(Co. |
|
, Co1. |
) |
|
GROUP BY Co. |
|
|
|
|
|
) |
|
|
|
|
|
SELECT Co. |
, |
|
_ |
|
|
FROM |
|
|
|
|
|
WHERE |
_ |
= |
|
|
|
( |
|
|
|
|
|
SELECT Max( |
_ |
) |
|
||
FROM Neighbor |
|
|
|
)
;
VIEW () .
, .
. ,
.
110