/* --
一、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) */
