【SQL语言】空间数据库原理

第六章 空间数据库的查询和优化

6.1结构化查询语言SQL

1.建立表命令格式

CREATE TABLE <表名>
(<列名><数据类型>[DEFAULT] [列级完整性约束条件]
[, <列名><数据类型> [DEFAULT][列级完整性约束条件]
[,…]]
[, <表级完整性约束条件>])

创建表示例
CREATE TABLE publishers
(
pub_id char(4) NOT NULL PRIMARY KEY CLUSTERED   -- 主键约束
CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756')
OR pub_id LIKE '99[0-9][0-9]'),  --CHECK约束
pub_name varchar(40) NULL,
city varchar(20) NULL,
state char(2) NULL,
country varchar(30) NULL DEFAULT('USA')  --默认值
)

2.删除表命令格式

DROP TABLE <表名>

3.建立索引

CREATE [UNIQUE][CLUSTER]INDEX<索引名>
ON<表名> (<列名>[<次序>][, <列名>[<次序>]]…);

创建索引示例
1.下面的示例为authors表的au_id列创建索引。
SET NOCOUNT OFF
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'au_id_ind')
DROP INDEX authors.au_id_ind
GO
USE pubs CREATE INDEX au_id_ind ON authors (au_id)
GO
2.在前述Friends表上建立一个索引
CREATE INDEX NewIndex ON Friends ([LastName], [FirstName]);

4.删除索引

DROP INDEX <索引名>

6.2 空间结构化查询语言介绍

1.类型级的继承性

假设有关于人的类型定义:
CREATE TYPE Person (
name varchar(10),
social_number char(18)
);

用继承性定义学生类型和教师类型:
CREATE TYPE Student UNDER Person (
degree varchar(10),
department varchar(20)
);
CREATE TYPE Teacher UNDER Person (
salary integer,
department varchar(20)
);

2.表级的继承性

先定义表people:
CREATE TABLE people OF Person;  --people称为超表

然后可以创建表students和teachers作为people的子表:
CREATE TABLE students OF Student UNDER people;
CREATE TABLE teachers OF Teacher UNDER people;

3.用户自定义类型

创建Composite Types
CREATE TYPE MyPoint AS (
x double precision,
y double precision
);
CREATE TYPE MyLineString AS (
Num_of_Points INT,
Geometry MyPoint[600]
);
CREATE TYPE MyPolygon AS(
Num_of_LineString INT,
Geometry MyLineString[50]
);

使用Composite Types
--创建city数据表
CREATE TABLE City(
Name varchar(30),
Country varchar(36),
Pop int,
Capital char(1),
Shape MyPoint
)
--在city表中插入一个点数据
INSERT INTO City VALUES(
'Brasilia', 'Brazil',1.5, 'Y', row(-54.5,-23.2)
);
--创建river数据表
CREATE TABLE River (
Name varchar(30),
Origin varchar(30),
Length double precision,
Shape MyLineString
);
--创建country数据表
CREATE TABLE Country (
Name varchar(30),
Cont varchar(30),
Pop int,
GDP numeric,
Life_Exp numeric,
Shape MyPolygon
);

4.PostGIS对几何信息的检查

ALTER TABLE cities
ADD CONSTRAINT geometry_valid
CHECK (IsValid(shape))

INSERT INTO test.cities ( shape, name )
VALUES ( ST_GeomFromText('LINESTRING(0 0,0 0)', 4326), '北京');  --cities是点数据
ERROR: new row for relation "cities" violates check constraint
"geometry_valid"
SQL 状态: 23514
)

6.3 POSTGRESSQL空间查询语言

实验1 在POSTGIS中测试拓扑关系

--2.创建一个gis数据库。
--方式一：在pgadmin中创建数据库并使用postgis模板
--方式二：在pgadmin中创建一个数据库，然后在该数据库的脚本窗口中输入
create extension postgis
--并执行
--方式三：进入psql console写命令执行（或在pgadmin的查询窗口执行）
--首先使用cmd命令创建一个postgressql数据库
CREATE DATABASE [yourdatabase]
--转到你新建的xx数据库中：
\c xx;
--然后输入官网给的这几条添加扩展语句（官网可找到）：
-- Enable PostGIS (includes raster)
CREATE EXTENSION postgis;
-- Enable Topology
CREATE EXTENSION postgis_topology;
-- （其他不一一列出）
--5.测试拓扑关系。
--利用touches()查询与Glades相接的几何对象
select b.* from frcounties a, frcounties b
where a.name = 'Glades' and ST_Touches(a.geom, b.geom)
--利用表示touches的9交表达式查询与Glades相接的几何对象
select b.* from frcounties a, frcounties b
where a.name = 'Glades' and ST_Relate(a.geom, b.geom, 'F***T****')
--查询touches关系并且与Glades相接于公共边的空间目标
select b.* from frcounties a, frcounties b
where a.name = 'Glades' and ST_Relate(a.geom, b.geom, 'F***1****')
--查询touches关系并且与Glades相接于公共点的空间目标
select b.* from frcounties a, frcounties b
where a.name = 'Glades' and ST_Relate(a.geom, b.geom, 'F***0****')