地理空间数据库复习笔记：关系数据库标准语言 几何对象模型与查询

我的GIS/CS学习笔记：/yunwei37/ZJU-CS-GIS-ClassNotes

<一个浙江大学本科生的计算机、地理信息科学知识库 >

Lecture 3 关系数据库标准语言

SQL概述

SQL (stands for Structured Query Language)

A standard language for querying and manipulating dataA very high-level (optimized) programming language

Current standard is SQL-

内容:

Data Definition Language (DDL)Data Manipulation Language (DML)

特点：综合统一、高度非过程化、面向集合的操作方式、以同一种语法结构提供两种使用方式、语言简捷，易学易用

数据定义

定义功能包括：

定义基本表

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

实体完整性：PRIMARY KEY (主码，主键)参照完整性：FOREIGN KEY (外码，外键)用户定义完整性：NOT NULL UNIQUE DEFAULT CHECK域约束: SQL语言可以使用CREATE DOMAIN语句定义新的值域

创建Students\Enrolled关系

CREATE TABLE Students (sid CHAR(10) PRIMARY KEY,name VARCHAR(20) NOT NULL,age INT CHECK(age > 0));CREATE TABLE Enrolled (student_id CHAR(10) REFERENCES Students(sid),cid CHAR(20),grade INT,PRIMARY KEY(student_id, cid));

修改和删除表

ALTER TABLE <表名>[ ADD <新列名> <数据类型> [ 完整性约束 ] ][ DROP <完整性约束名> ][ MODIFY <列名> <数据类型> ]DROP TABLE <表名>

举例:

ALTER TABLE Students ADD Scome DATE;ALTER TABLE Students ALTER COLUMN Scome type timestamp;ALTER TABLE Students DROP Scome;ALTER TABLE Enrolled ADD CONSTRAINT grade_checkALTER TABLE Enrolled DROP CONSTRAINT pk_En;DROP TABLE Students;

数据更新

数据插入

INSERTINTO <表名> [(<属性列1>[，<属性列2 >] … )]VALUES (<常量1> [，<常量2>] … )

将新元组插入指定表中:

Insert into Students Values(‘200011’, ‘张三’, 19);Insert into Students(sid, age, name)Values(‘200012’, 20, ‘李四’);Insert into Students(sid, name)Values(‘200013’, ‘王五’);

数据修改

UPDATE <表名>SET <列名>=<表达式>[，<列名>=<表达式>]…[WHERE <条件>]

修改指定表中满足WHERE子句条件的元组:

Update Students Set age = 20 Where sid = ‘200011’Update Students Set age = 21 Where name = ‘王五’Update Students Set age = age + 1;Update Students Set sid = ‘200012’ Where sid = ‘200011’;

数据删除

DELETEFROM <表名>[WHERE <条件>]；

删除指定表中满足WHERE子句条件的元组

Delete From Students Where sid = ‘200011’;Delete From Students Where sid = ‘200000’;Delete From Students

数据查询

SELECT

SELECT A1, A2, …, An #3: what to returnFROM R1, R2, …, Rn #1: relations to queryWHERE condition #2: combine, filter relationsGROUP BY Ai, Aj, …, AkHAVING conditionORDER BY <列名1> [ASC|DESC]

Max/Min value problem:

▬ Solution 0SELECT sID FROM Student ORDER BY GPA DESC LIMIT 1;▬ Solution 1 (all/any?)SELECT sID FROM StudentWHERE GPA >= all (SELECT GPA FROM Student);▬ Solution 2SELECT sID FROM StudentWHERE GPA = (SELECT max(GPA) FROM Student)

(Left | Right | Full) Outer Join:

If join condition don’t match for certain tuples, include those tuples in the result, but pad with NULL values

Write a SQL query that returns the number ofcolleges applied to by each student, including 0 for those who applied nowhere

SELECT Student.sID, count(distinct cName)FROM Student, ApplyWHERE Student.sID = Apply.sIDGROUP BY Student.sIDunionSELECT sID, 0FROM StudentWHERE sID NOT IN (SELECT sID FROM Apply);

查询每个城市最受欢迎的站点:

select station.city,station_name,max as visit_count from (select city,max(count)from(select s1.id as id, s1.count+s2.count as count from (select start_station_id as id, count(*)from trip group by start_station_id) as s1,(select end_station_id as id, count(*)from trip group by end_station_id) as s2 where s1.id = s2.id) as counts, stationwhere counts.id =station_id group by city) as maxCity,station,(select s1.id as id, s1.count+s2.count as count from (select start_station_id as id, count(*)from trip group by start_station_id) as s1,(select end_station_id as id, count(*)from trip group by end_station_id) as s2 where s1.id = s2.id) as countswhere counts.id = station_id and counts.count = max and maxCity.city = station.cityorder by station.city;

在PostgreSQL中，可使用extract函数从timestamp类型变量中提取年月日信息，如：extract(year from date)

绘制日均租车量-时间变化直方图（在sql中仅需输出month与number）:

select extract(month from date) as month ,avg(count) as number from (select extract(doy from start_time),count(*) from trip where start_station_id in (select station_id from station where zip_code = '94107') and extract(year from start_time) = group by extract(doy from start_time)) as days, weather where extract(doy from date) = date_part group by extract(month from date) order by month;

NULL values

For numerical operations, NULL -> NULL:For boolean operations, in SQL there are three values: FALSE = 0UNKNOWN = 0.5TRUE = 1 WHERE子句只有条件为True才保留这个记录HAVING子句只有条件为True才保留这个GROUPJOIN NULL != NULLGROUP BY NULL算一个GROUPNULL在ORDER BY时默认排序最前面如果COUNT(*)，NULL的记录参与计算，COUNT属性，NULL的记录忽略

Text comparison

s LIKE p: pattern matching on strings% = any sequence of characters_ = any single character

其他

天气关系数据weather导入，未给出的数据默认为NULL

copy weather from ‘E://weather.txt’ delimiter ‘,’ NULL ‘’;

Lecture 4 几何对象模型与查询

空间数据模型

空间数据模型：

空间信息的一种数据组织方式对象模型、场模型、网络模型

GIS中常见的两大数据模型：栅格模型、矢量模型

矢量模型的优点：

数据结构紧凑，冗余度低，表达精度高，图形显示质量好，有利于网络和检索分析等在GIS中应用广泛，特别在小区域（大比例尺）制图中充分利用了它的精度高的优点

栅格模型的优缺点：

数据结构简单、空间分析和地理现象的模拟较为容易等数据量大、投影转换比较困难

矢量模型：

点数据网络数据

几何对象模型

概念模型

地理要素(feature):

对现实世界空间现象的抽象由几何(geometry)、属性(attribute)、行为(behavior)等三类信息构成地理要素的属性和行为等信息的建模是由应用系统的设计者，根据实际应用需求进行建模几何的建模是数据库管理系统关心的基础问题 关键是选择一组基本空间数据类型来满足地图常用几何信息的建模要求

几何对象模型利用对象关系型数据库中的扩展数据类型实现.

几何对象模型的核心:

一个依赖于空间参考系(Spatial Reference System)和测量参考系(Measure Reference System)的几何(Geometry)类派生出点(Point)、线(Curve)、面(Surface)、多点(MultiPoint)、多线(MultiCurve)、多面(MultiPolygon)等类型

模型层次关系 (数据):

点(Point): 零维几何对象类，代表空间中的一个点，如城市曲线(Curve): 由点序列描述一维的几何对象类，如街道、管线相邻两点间的插值方法：线性插值和非线性插值 折线(LineString): 曲线的子类，采用线性插值线段(Line): 折线的特例，只有两个点的线串环线(LineRing): 由折线派生而来，闭合的、不自相交或相切的折线面(Surface): 二维几何对象类，代表一个外边界、零到多个内边界组成的几何对象 在三维空间中，可能是一个同构的曲面 多边形(Polygon) 二维坐标空间中由一个外边界、零到多个内边界定义的平坦表面，由一个或一个以上的线环聚合而成，如省份仅支持由折线串围成的多边形，暂不支持曲线 体表面(PolyhedraSurface) 由简单面沿着它们的边界“缝合”而成三维空间中的多面体曲面总体上可以不平坦相互接触的一对多边形的公共边可以表达为有限折线的集合 三角形(Triangle): 多边形类的一个特例不规则三角网(Triangulated irregular network, TIN): 体表面的一个特例，由多个共享公共边的连续三角形聚合而成几何集合(GeometryCollection): 由一个或多个几何对象组成的集合，其中的元素必须具有相同的空间参考系和测量参考系 多点(MultiPoint): 零维的几何类集合，由多个点聚合而成，代表空间中的多个点，例如多个岛屿多面(MultiSurface): 二维的几何集合类，由多个面聚合而成多曲线(MultiCurve): 一维的几何类，由多条曲线聚合而成多折线(MultiLineString): 多曲线类的子类，由多条折线聚合而成，如由多条河流组成的水系多曲线允许出现弧线，多折线由折线组成 多多边形(MultiPolygon): 多面的子类，由多个多边形对象聚合而成，例如多个岛屿组成的群岛（大比例尺）

坐标维数和几何维数的区别:

坐标维数是指在一个坐标系统描述一个位置所需的测量或坐标轴的个数（空间维数）几何维度是在一定前提下描述一个几何对象所需的参数个数

任何几何模型都有其边界(boundary)、内部(interior)和外部(exterior)：

边界：一个几何实体界限的集合，几何维数是其本身几何形状的维数减一 点：空线：端点曲线及其子类：起始点和终止点多曲线及其子类：各曲线的起始点和终止点面：构成它的线串 内部：几何对象除边界外的所有直接位置(direct position)的集合 直接位置是用坐标参考系中的一组坐标描述的位置几何维数与其本身的维数一致所有几何对象都有内部，即几何对象形状减去其边界后的部分 外部：空间全域与几何闭包之差 任意几何对象外部的维数总是2所有的几何形状都有外部，即其几何形状的补集

几何对象模型的坐标维数为3，但目前仅能描述二维几何对象。 z值仅用于记录点在坐标空间中第3个坐标轴的测量值。

M值：点类除了x, y, z坐标外，还有一个M坐标

M值是线性参考系统的一个重要的度量值例如，高速公路上的里程碑点可用其M值表示从高速公路起点到当前位置的距离

由于体表面违反了“多边形元素只能相交在有限数量的点上”的规则，所以体表面不是多多边形

几何对象的方法 (函数)：

常规方法： Dimension() : IntegerCoordinateDimension() : IntegerGeometryType() : StringSRID() : IntegerEnvelope() : GeometryAsText() : StringAsBinary() : StringIsEmpty() : BooleanIsSimple() : BooleanIs3D() : BooleanIsMeasured() : BooleanBoundary() : Geometry 常规GIS分析方法 (空间分析)： Distance(another: Geometry) : DistanceBuffer(distance: Distance) : GeometryConvexHull() : GeometryIntersection(another : Geometry) : GeometryUnion(another : Geometry) : GeometryDifference(another : Geometry) : GeometrySymDifference(another : Geometry) : Geometry 空间查询方法 (拓扑分析）： Equals(another : Geometry) : BooleanDisjoint(another : Geometry) : BooleanIntersects(another : Geometry) : BooleanTouches(another : Geometry) : BooleanCrosses(another : Geometry) : Boolean；Within(another : Geometry) : BooleanContains(another : Geometry) : BooleanOverlaps(another : Geometry) : BooleanRelates(another : Geometry, matrix : String) : BooleanLocateAlong(mValue : Double) : GeometryLocateBetween(mStart : Double, mEnd : Double) : Geometry

九交模型：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nlJYaLUq-1598684054065)(pictures\Nine-Intersection.png)]

空间拓扑关系: 前面加上 st_ 就是对应函数

相离(disjoint)FF*FF****若 A ∩ B = ∅，则a和b相离 相交(intersects) 若 A ∩ B ≠ ∅，则a和b相交a.Intersects(b) ↔ !a.Disjoint(b) 相等(equals)TFFFTFFFT交叠(overlaps)T*T***T**点/点、线/线、面/面交叠关系，没有线/面等交叠关系 包含于(within)T*F**F***包含(contains)相接(touches)FT*******F**T*****F***T****面/面、线/线、线/面、点/面、点/线相接关系，但没有点/点相接关系 穿越(crosses)T*T******点/线、点/面、线/线、线/面穿越关系

线性参考系查询方法

LocateAlong和LocateBetween用于动态构造线性要素的各个部分的方法 LocateAlong用于选取几何中M值为mValue的点，并构造成一个新的几何对象LocateBetween用于选取几何中M值在mStart和mEnd之间的点，并构造一个新的几何对象

几何对象逻辑模型与物理模型

基于概念模型，OGC提出了基于预定义数据类型和基于扩展几何类型的两种逻辑模型实现方法：

基于预定义数据类型的实现 利用关系数据库中已有的数字(numeric)类型、二进制大对象(BLOB)类型实现空间数据的存储和管理，这些数据类型的解释和维护由DBMS负责相关空间数据访问方法作为扩展函数嵌入到DBMS中与空间数据引擎(如ArcSDE)的区别基于numeric和BLOB的实现，要素表(Feature)、几何列表(GEOMETRY_COLUMNS)和空间参考系(SPATIAL_REF_SYS)表的结构都一样，不同之处在于几何(Geometry)表的结构Feature表： 记录一组具有相同属性和行为的地理要素的集合，要素表的列代表要素的属性，而不同的行代表不同的要素Geometry_Column列是几何对象的逻辑几何数据类型，其存储的是几何对象的唯一标识(geometry ID, GID)，而几何数据实际存储在Geometry表中，因此，可以将GID作为指针到Geometry表找到其空间数据 Geometry表：Numeric类型 将几何类型的空间坐标作为数值对存储在表中，每行最多可存储MAX_PPR个空间坐标 Geometry表：BLOB类型 将空间数据以WKB(Well-Known Binary Representation)形式存储在名为WKB_Geometry的BLOB类型的字段中 GEOMETRY_COLUMNS表：记录数据库中所有要素表及其几何列的属性SPATIAL_REF_SYS表基于扩展几何类型的实现 利用对象关系数据库中对抽象数据类型的支持，定义Geometry类型及其相关的方法与函数，并用该扩展几何类型实现空间数据的存储和管理扩展几何类型的解释和维护由定义者负责Oracle Spatial中的SDO_GEOMETRY、PostGIS中的Geometry 是扩展的空间数据类型GEOMETRY_COLUMNS和SPATIAL_REF_SYS是系统表，用于存储元数据信息Feature表是用户表，用于存储空间数据

几何对象物理模型：

WKB表达：FA SQL给出了一种较为紧凑的几何数据的二进制方式的存储格式WKT表达：一种基于文本格式几何数据交汇标准表达方式

举例：

POINT(0 0)LINESTRING(0 0, 1 1, 1 2)POLYGON((0 0, 4 0, 4 4, 0 4, 0 0))POLYGON((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1))MULTIPOINT((0 0), (1 2))MULTILINESTRING((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))MULTIPOLYGON(((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))GEOMETRYCOLLECTION(POINT(2 3), LINESTRING(2 3,3 4))

PostGIS：

PostGIS中常用数据类型：

boolean 布尔类型，其值为T或Fbox2d 矩形框类型，由矩形左下角和右上角组成box3d 长方体类型，由左前下角和右后上角组成bytea 相当于BLOB类型，可变长的二进制值integer 字节为4的整数(int4)double precision 字节为8的浮点数(float8)geometry 几何类型geometry[] 几何数据类型数组geometry set 几何数据类型的集合raster 栅格类型setof geomval 几何对象与值对的集合，用于返回几个对象及与该集合对象对应的栅格值text 文本类型character varying 相当于BLOB类型，可变长的文本描述

表的定义：

create table landuse (landuse_id integer NOT NULL,name varchar(20),the_geom geometry,area double precision,perimeter double precision,constraint landuse_key primary key (landuse_id));

数据插入:

insert into landusevalues(12, ‘Timber-forest’,‘01010000001DDB93F460BB4241A84E5AC86F455441’,47806700, 34246.2);insert into landusevalues(12, ‘Timber-forest’,ST_GeomFromText(‘Polygon((10 10, 10 20, 20 20, 20 10, 10 10))’, 4326),47806700, 34246.2);

AddGeometryColumn 增加几何属性：

select AddGeometryColumn('testgeom', 'geom', 4326, 'MULTILINESTRING', 2);

Dimension()

ST_Dimension('GEOMETRYCOLLECTION(LINESTRING(1 1,0 0), POINT(0 0))');

SRID() : Integer 用于获取几何类型的空间参考系

SELECT ST_SRID(ST_GeomFromText('POINT(-71.1043 42.315)',4326));

空间参考系更改为4326

select UpdateGeometrySRID('ushighways', 'geom', 4326);

Envelope() : Geometry 用于获取Geometry的最小边界矩形

SELECT ST_AsText(ST_Envelope('POINT(1 3)'::geometry));

Boundary() : Geometry 获取几何类型的边界

SELECT ST_AsText(ST_Boundary(ST_GeomFromText('LINESTRING(1 1,0 0, -1 1)')));

Distance(another: Geometry) : Distance 求本Geometry与另一个Geometry间的距离

geometry在空间参考系4326下空间计算单位为度，如果单位需要转成米，可以通过以下三种方式： ST_Distance(ST_Transform(geom1, 26986), ST_Transform(geom2, 26986))ST_Distance(geom1::geography, geom1::geograpy)ST_Distance(geom1, geom2, false) (推荐方法) ST_DistanceSphere 以几何所定义的SRID椭球体进行计算，返回单位为米；比ST_DistanceSpheroid快；ST_DistanceSpheroid 需要另外提供椭球体信息进行计算，返回单位为米；ST_Distance 返回二维平面上的笛卡尔距离，单位是投影单位。

SELECT ST_Distance(ST_GeomFromText('POINT(-72.1235 42.3521)',4326),ST_GeomFromText('LINESTRING(-72.1260 42.45, -72.123 42.1546)', 4326));

比较~=(操作符)、=(操作符)、ST_Equals和ST_OrderingEquals四个函数的异同

boolean =( geometry A , geometry B )：仅将在所有方面完全相同，坐标相同，顺序相同的几何视为相等。

boolean ~=( geometry A , geometry B );将边界框相同的几何要素视为相等。（PostGIS 1.5前测试实际相等性）

boolean ST_Equals(geometry A, geometry B);几何在空间上相等则返回true，不考虑点的顺序。即 ST_Within（A，B）= true 且 ST_Within（B，A）= true 。

boolean ST_OrderingEquals(geometry A, geometry B);如果几何相等且坐标顺序相同，则返回TRUE。

可以将MultiXXX转换XXX，如MultiPolygon转换获得多个Polygon

select ST_Dump(ST_GeomFromText('MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))'))

查询点的数目:

select gid, 'Lake Superior''s number of point' as name, ST_NPoints(geom) as numberfrom uslakeswhere name like '%Superior%';

ConvexHull() : Geometry 求本Geometry的凸包

ST_AsText(ST_ConvexHull(ST_Collect(ST_GeomFromText('MULTILINESTRING((100 190,10 8),(150 10, 20 30))'),ST_GeomFromText('MULTIPOINT(50 5, 150 30, 50 10, 10 10)'))) );

查询距离ST_CASE = 10012交通事故最近的城市

select uc.name || ' in ' || uc.state as name from uscities uc, usaccidents ua where st_case = 10012and ST_Distance(uc.geom,ua.geom) = ( select min(ST_Distance(uc.geom,ua.geom)) from uscities uc, usaccidents ua where st_case = 10012);

查询哪条高速公路上的交通事故最多：该高速公路上的交通事故

select ua.gid, hw1.name,ua.geom from(select hw.gid, hw.full_name as name, hw.geom, count(*) c from (select * from usaccidents where month = 8 or month = 9) ua, ushighways hw where ST_DWithin(hw.geom::geography, ua.geom::geography,500) group by hw.gid order by c desc limit 1) hw1, (select * from usaccidents where month = 8 or month = 9) ua where ST_DWithin(hw1.geom::geography, ua.geom::geography,500);

空间网格关联查询：

select x || '0' || y as gid, x || ' ' || y as name, grid1.geom ,count(*) as valuefrom usaccidents ua, (WITH usext AS (SELECT ST_SetSRID(CAST(ST_Extent(geom) AS geometry),4326) AS geom_ext, 50 AS x_gridcnt, 46 AS y_gridcntFROM cal),grid_dim AS (SELECT (ST_XMax(geom_ext)-ST_XMin(geom_ext)) / x_gridcnt AS g_width, ST_XMin(geom_ext) AS xmin, ST_xmax(geom_ext) AS xmax,(ST_YMax(geom_ext)-ST_YMin(geom_ext)) / y_gridcnt AS g_height,ST_YMin(geom_ext) AS ymin, ST_YMax(geom_ext) AS ymaxFROM usext), grid AS (SELECT x, y, ST_MakeEnvelope( xmin + (x - 1) * g_width, ymin + (y - 1) * g_height, xmin + x * g_width, ymin + y * g_height,4326) AS grid_geom FROM (SELECT generate_series(1,x_gridcnt) FROM usext) AS x(x) CROSS JOIN (SELECT generate_series(1,y_gridcnt) FROM usext) AS y(y) CROSS JOIN grid_dim ) SELECT g.x x, g.y y, ST_Intersection(s.geom, grid_geom) AS geom FROM cal AS s INNER JOIN grid AS g ON ST_Intersects(s.geom,g.grid_geom)) grid1where ST_Within(ua.geom,grid1.geom)group by grid1.x,grid1.y,grid1.geom;

查询在加州范围内的交通事故，通过heatMap进行可视化

select gid, tway_id as name, geom from usaccidents where ST_Within(geom, (select geom from cal ));