空间数据库实习3代码_空间数据库实习

空间数据库实习3代码由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“空间数据库实习”。

/*--

一、Country表创建及记录录入

--1.创建一个新的Country表，包含Name、Cont、Pop--GDP、Life_Exp、Shape字段 CREATE TABLE Country(Name NVARCHAR(80)NOT NULL PRIMARY KEY, Cont NVARCHAR(10)NULL, Pop NUMERIC(38,15)NULL, GDP NUMERIC(38,15)NULL, Life_Exp NUMERIC(5,2)NULL, Shape GEOMETRY)go

--2.向Country表中添加Name,Pop,Shape记录数据 INSERT INTO Country(Name,Pop,Shape)SELECT CNTRY_NAME,POP2005,Shape from COUNTRY06

WHERE CNTRY_NAME IN('Canada','Mexico', 'Brazil','Cuba','United States','Argentina')GO

--3.更新Country表数据，更新表中的Cont、GDP、Life_Exp数据

USE spatialdata UPDATE Country SET Cont='NUM',GDP=658.0,Life_Exp=77.08 WHERE Name='Canada' GO

UPDATE Country SET Cont='NUM',GDP=694.3,Life_Exp=69.36 WHERE Name='Mexico' GO

UPDATE Country SET Cont='SAM',GDP=1004.0,Life_Exp=65.60 WHERE Name='Brazil' GO

UPDATE Country SET Cont='NUM',GDP=16.9,Life_Exp=75.95 WHERE Name='Cuba' GO

UPDATE Country SET Cont='NUM',GDP=8003.0,Life_Exp=75.75 WHERE Name='United States' GO

UPDATE Country SET Cont='SAM',GDP=348.2,Life_Exp=70.75 WHERE Name='Argentina' GO */ /*--

二、City表创建及记录录入

--1.创建一个新的City表，包含Name、Country、Pop--Captial、Shape字段 CREATE TABLE City(Name_C NVARCHAR(80)NOT NULL PRIMARY KEY, Country NVARCHAR(80)NULL FOREIGN KEY

REFERENCES Country(Name), Pop NUMERIC(38,15)NULL, Captial NVARCHAR(1)NULL, Shape GEOMETRY)GO

--2.向City表中添加Name,Country,Shape记录数据 USE spatialdata INSERT INTO City(Name_C,Country,Shape)SELECT CITY_NAME,CNTRY_NAME,Shape from CITIES WHERE CITY_NAME IN('Havana','Washington D.C.', 'Monterrey','Toronto','Brasilia','Rosario','Ottawa','Mexico City','Buenos Aires')GO

--3.更新City表中的数据 USE spatialdata UPDATE City SET Pop=2100000,Captial='Y' WHERE Name_C='Havana' GO

UPDATE City SET Pop=3200000,Captial='Y' WHERE Name_C='Washington D.C.' GO

UPDATE City SET Pop=2000000,Captial='N' WHERE Name_C='Monterrey' GO

UPDATE City SET Pop=3400000,Captial='N' WHERE Name_C='Toronto' GO

UPDATE City SET Pop=1500000,Captial='Y' WHERE Name_C='Brasilia' GO

UPDATE City SET Pop=1100000,Captial='N' WHERE Name_C='Rosario' GO

UPDATE City SET Pop=800000,Captial='Y' WHERE Name_C='Ottawa' GO

UPDATE City SET Pop=1400000,Captial='Y' WHERE Name_C='Mexico City' GO

UPDATE City SET Pop=1075000,Captial='Y' WHERE Name_C='Buenos Aires' GO */ /*--

三、River表创建及记录录入

--1.创建一个新的River表，包含Name、Origin、Length、Shape字段 USE spatialdata CREATE TABLE River(Name_R NVARCHAR(80)NOT NULL PRIMARY KEY, Origin NVARCHAR(80)NULL FOREIGN KEY

REFERENCES Country(Name), Length_ NUMERIC(8,2)NULL, Shape GEOMETRY)GO

--2.向表中插入记录 USE spatialdata INSERT INTO River(Name_R, Shape)SELECT NAME, Shape from RIVERS WHERE Name in('Rio Paranaiba','St.Lawrence','Rio Grande, North America','Miiippi')GO

--3.更新River表中的数据 USE spatialdata UPDATE River SET Origin='Brazil',Length_ =2600 WHERE Name_R='Rio Paranaiba' GO

UPDATE River SET Origin='United States',Length_ =1200 WHERE Name_R='St.Lawrence' GO

UPDATE River SET Origin='United States',Length_ =3000 WHERE Name_R='Rio Grande, North America' GO

UPDATE River SET Origin='United States',Length_ =600 WHERE Name_R='Miiippi' GO */

--

四、空间查询 /*--1.列出Country 表中所有与美国（United States）--相邻的国家名字 USE spatialdata SELECT C1.Name 'Neighbors of United States' FROM Country C1,Country C2 WHERE C1.Shape.STTouches(C2.Shape)=1 AND C2.name='United States'

--2.找出River表中所列出的河流经过的国家

SELECT R.Name_R, C.name FROM River R,Country C WHERE R.Shape.STCroes(C.Shape)=1

--3.对于River 表中列出的河流，在City表中--找到距离其最近的城市

SELECT C1.Name_C,R1.Name_R, C1.Shape.STDistance(R1.Shape)Distance,R1.Shape FROM City C1,River R1 WHERE C1.Shape.STDistance(R1.Shape)

FROM City C2 WHERE C1.Name_CC2.Name_C)

--4.查询St.Lawrence河能为公里以内的城市--供水，列出能从该河获得供水的城市。SELECT Ci.Name_C FROM City Ci,River R WHERE Ci.Shape.STOverlaps(R.Shape.STBuffer(300))=1 AND R.Name_R='St.Lawrence'

--5.列出Country表中每个国家的名字、人口和国土面积 SELECT C.Name,C.Pop,C.Shape.STArea()AS 'Area' FROM Country C

--6.求出河流在流经的各国境内的长度 SELECT R.Name_R,C.Name, R.Shape.STIntersection(C.Shape).STLength()AS 'Length' FROM River R,Country C WHERE R.Shape.STCroes(C.Shape)=1

--7.列出每个国家的GDP及其首都到赤道的距离 SELECT Co.GDP，--geometry::Parse('POINT(0,Ci.Shape.STY)')geometry::Point(0,Ci.Shape.STY,4326).STDistance(Ci.Shape)AS 'Distance' FROM Country Co,City Ci WHERE Co.Name=Ci.Country AND Ci.Captial='Y'

--8.按其邻国数目的多少列出所有国家

SELECT Co.Name,count(Co1.Name)' Count Country Number' FROM Country Co,Country Co1 WHERE Co.Shape.STTouches(Co1.Shape)=1 GROUP BY Co.Name ORDER BY count(Co1.Name)

--9.列出只有一个邻国的国家。如果一个国家与另一个国家

--在陆地上有一条共同的国界，那么这个国家就是另一个国家--的邻国

SELECT Co.Name,count(Co1.Name)' Count Country Number' FROM Country Co,Country Co1 WHERE Co.Shape.STTouches(Co1.Shape)=1 GROUP BY Co.Name HAVING Count(Co1.Name)=1--10.查询哪一个国家的邻国多，并创建其视图 GO CREATE VIEW Neighbor AS SELECT Co.Name,count(Co1.Name)AS num_Neighbors FROM Country Co,Country Co1 WHERE Co.Shape.STTouches(Co1.Shape)=1 GROUP BY Co.Name

GO SELECT Name,num_Neighbors FROM Neighbor WHERE num_Neighbors=(SELECT max(num_Neighbors)FROM Neighbor)*/