Spatial DB system 0921

 

An Example World Database - 기본 SQL

 

 

many to one, one to one의 관계는 별도의 table로 저장하지 않음

(foreign key만 가지고 있으면 되므로)

>> City(Name, Country, Pop,Capital, Shape)

>> River(Name, Origin, Length, Shape) 

     Origin = country name

** 밑줄은 foreign key

Three Components of SQL

1. Data Definition Language (DDL) : 정의표

• Creation and modification of relational schema
• Schema objects include relations, indexes, etc. 

2. Data Manipulation Language (DML) 

• Insert, delete, update rows in tables 
• Query data in tables 

3. Data Control Language (DCL) 

• Concurrency control, transactions

• Administrative tasks, e.g., set up database users, security permissions

Select 구문

Clauses 

• SELECT specifies desired columns 

• FROM specifies relevant tables 

• WHERE specifies qualifying conditions for rows 

• ORDER BY specifies sorting columns for results 

• GROUP BY, HAVING specifies aggregation and statistics

Having 조건문에 대한 내용

origin을 그룹으로 했을 때, USA에 해당하는 count(*)의 값은= 3

Extending SQL for Spatial Data

(위의 나온 기본 SQL에 대해 spatial Data에 적용)

 

 

 

OGIS Spatial Data Model의 4가지 타입

Point, Curve, Surface and GeometryCollection

 

Operations fall into three categories: 

1. Apply to all geometry types 

   • SpatialReference, Envelope, Export,IsSimple, Boundary 

2. Predicates for Topological relationships (위상에 대한 정보)

  • Equal, Disjoint, Intersect, Touch, Cross, Within, Contains 

3. Spatial Data Analysis 

  • Distance,Buffer,Union, Intersection, ConvexHull, SymDiff

 

 

 

SpatialReference()

 

기준 위치에 대해 상대적인 위치를 참조하여 표현함

그러므로 원점좌표(기준위치)에 대한 정보를 주는 것이 spatial reference임

 

 

 

Envolope함수의 장점(필요성)

 

아래와 같이 line, polygon등 이 주어졌을 때, 표현하기위한 좌표에 대한 갯수가 유동적임 

 

이것을 포함하는 가장 작은 하나의 “직사각형화” 하여 

 

가장 최소의 좌표와 최대의 좌표를 구하여 표현하면 간소화 시킬 수 있음

또한, x라는 점이 해당 범위에 있는지, 없는지

세모점이 해당 범위에 있는지 없는지 파악이 가능하게 해주기 위해 

Evolope함수가 필요함

coutry 면적을 구하는 예시

Area함수로 면적을 구할 수 있음

SELECT C.Name, C.Pop, Area(C.Shape) AS "Area" 

FROM Country C

적도에서부터 각 국가의 수도까지의 거리를 구하는 sql

SELECT Co.GDP, Distance(Point(Ci.Shape.x, 0), Ci.Shape) AS "Distance" 

FROM Country Co, City Ci 

WHERE Co.Name = Ci.Country AND Ci.Capital =‘Y ’

**(Ci.Shape.x, 0) 적도부터의 거리이므로 y=0 임 

미국과 인접한 국가를 찾는 것

SELECT C1.Name AS "Neighbors of USA" 

FROM Country C1, Country C2 

WHERE Touch(C1.Shape,C2.Shape) = 1 AND C2.Name =‘USA ’

** touch=1이면 boundary가 접해있다는 뜻임

River가 통과하는 나라를 찾기

SELECT R.Name, C.Name 

FROM River R, Country C 

WHERE Cross(R.Shape,C.Shape) = 1

River가 통과하는 나라에서의 강의 길이 찾기

SELECT R.names,  C.Name, Length(Intersection(R.shape, C.shape)) 

FROM River R, Country C 

WHERE Cross(R.Shape,C.Shape) = 1

 

노란색의 길이 찾기

300km내에서 물을 공급받는 두 도시(A, B)의 이름을 찾기

SELECT Ci.Name 

FROM City Ci, River R 

WHERE Overlap(Ci.Shape, Buffer(R.Shape,300)) = 1 AND R.Name =‘St.Lawrence ’

인접하는 모든 국가에 대해서 순서대로 나열하시오 (self join)

Co	Co1
A	B
A	C
A	D

 

SELECT Co.Name, Count(Co1.Name) 

FROM Country Co, Country Co1 

WHERE Touch(Co.Shape,Co1.Shape) 

GROUP BY Co.Name 

ORDER BY Count(Co1.Name)

 

위의 테이블에서는 A : 3으로 나오고 값에대해 ordering 된 결과로 나옴

** SQL에서 order의 default는 decrease임

 

 

각각의 강에 대해서 가장 가까운 도시에 대한 정보를 가져오기

SELECT C1.Name, R1.Name 

FROM City C1, River R1 

WHERE Distance (C1.Shape,R1.Shape)<=ALL( SELECT Distance(C2.Shape, R1.Shape) 

        FROM City C2 

        WHERE C1.Name <> C2.Name )

 

초기값이 (A, C1)이라고 할때, (A, C2) , (A, C3)의 거리를 다 비교하여 (A, C1) 보다 작은 값을 가져오는 쿼리임

각각의 강에서 가장 가까운 도시 사이의 거리를 구하는 쿼리

SELECT R1.Name, MIN(Distance(R1.Shape, C1.Shape)) 

FROM River R1, City C1 

GROUP BY R1.Name

이웃한 국가의 NAME을 다 추출하는 쿼리의 2가지 방식

 

 

View라는 임시 테이블을 만들어 참조하는 쿼리

** View는 실제 테이블이 아니고 쿼리를 저장하고 있는 것임