达梦空间数据学习（二）

几何数据类型定义及对应构造函数的使用

--点（ST_Point）

drop table if exists dmpoint;

create table dmpoint(name varchar(100),geom ST_Point);

insert into dmpoint VALUES ('p1',dmgeo2.ST_PointFromText('point (1 1)',4326));

insert into dmpoint VALUES ('p2',dmgeo2.ST_PointFromText('point (2 2)',4326));

--线（ST_LineString）

drop table if exists dmLine;

create table dmLine(name varchar(100),geom ST_LineString);

insert into dmLine VALUES ('L1',dmgeo2.ST_LineFromText('linestring(0 0,1 1,2 1,2 2)' ,4326));

insert into dmLine VALUES ('L1',dmgeo2.ST_LineFromText('linestring(0 0,1 1,2 2,3 3)' ,4326));

--面（ST_POLYGON)

drop table if exists dmPOLYGON;

create table dmPOLYGON(name varchar(100),geom ST_POLYGON);

insert into dmPOLYGON VALUES ('Polygon1',dmgeo2.ST_PolygonFromText('polygon ((0 0, 1 0, 1 1, 0 1, 0 0))', 4326));

insert into dmPOLYGON VALUES ('Polygon1',dmgeo2.ST_PolygonFromText('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))', 4326));

--多点（ST_Multipoint）

drop table if exists dmMpoint;

create table dmMpoint(name varchar(100),geom ST_Multipoint);

insert into dmMpoint VALUES ('mp1',dmgeo2.ST_MPointFromText('multipoint (1 1, 2 2)',4326));

insert into dmMpoint VALUES ('mp2',dmgeo2.ST_MPointFromText('MULTIPOINT(0 0,1 2)',4326));

--多线（ST_Multilinestring）

drop table if exists dmMline;

create table dmMline(name varchar(100),geom ST_Multilinestring);

insert into dmMline VALUES ('mltline1',dmgeo2.ST_MLineFromText('multilinestring ((1 1,2 1),(4 4, 3 3))',4326));

insert into dmMline VALUES ('mltline2',dmgeo2.ST_MLineFromText('MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))', 4326));

--多面（ST_Multipolygon）

drop table if exists dmMpolygon;

create table dmMpolygon(name varchar(100),geom ST_Multipolygon);

insert into dmMpolygon VALUES ('mpolygon1',dmgeo2.ST_MPolyFromText   ('multipolygon (((1 1, 1 2, 2 2, 2 1, 1 1)), ((3 3, 3 4, 4 4, 4 3, 3 3)))', 4326));

insert into dmMpolygon VALUES ('mpolygon2',dmgeo2.ST_MPolyFromText   ('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)))', 4326));

--几何集合

drop table if exists dmCOLLECTION;

CREATE TABLE dmCOLLECTION (name varchar(100), geom ST_Geometry);

insert into dmCOLLECTION VALUES ('Col1',dmgeo2.ST_GeomFromText('GEOMETRYCOLLECTION(point(2 0),POLYGON((0 0,1 0,1 1,0 0)))',4326));

insert into dmCOLLECTION VALUES ('Col2',dmgeo2.ST_GeomFromText('GEOMETRYCOLLECTION(POLYGON((4 0,8 0,8 4,4 0)),LINESTRING(2 3,3 4))',4326));

insert into dmCOLLECTION VALUES ('Col3',dmgeo2.ST_GeomFromText('GEOMETRYCOLLECTION(point (1 1),linestring(0 0,1 1,2 2,3 3))',4326));

--如果一个表中含有多种空间数据类型，可用ST_Geometry进行定义数据类型 和ST_GeomFromText 进行构造

drop table if exists dmgeom;
CREATE TABLE dmgeom (name varchar(100), geom ST_Geometry);
--点（POINT）
insert into dmgeom VALUES ('point',dmgeo2.ST_GeomFromText('point (1 1)',4326));
--线（LINESTRING）
insert into dmgeom VALUES ('Linestring',dmgeo2.ST_GeomFromText('linestring (0 0,1 1,2 1,2 2)' , 4326));
--面（POLYGON)
insert into dmgeom VALUES ('Polygon',dmgeo2.ST_GeomFromText('polygon ((0 0, 1 0, 1 1, 0 1, 0 0))', 4326));
--多面（GEOMETRYCOLLECTION）
insert into dmgeom VALUES ('mltPolygon',dmgeo2.ST_GeomFromText('multipolygon (((1 1, 1 2, 2 2, 2 1, 1 1)), ((3 3, 3 4, 4 4, 4 3, 3 3)))' , 4326));
--多线
insert into dmgeom VALUES ('mltline',dmgeo2.ST_GeomFromText('multilinestring ((1 1,2 1),(4 4, 3 3))', 4326));
--多点
insert into dmgeom VALUES ('mltpoint',dmgeo2.ST_GeomFromText('multipoint (1 1, 2 2)', 4326));
--GeometryCollection —— 几何集合
insert into dmgeom VALUES ('Collection',dmgeo2.ST_GeomFromText('GEOMETRYCOLLECTION(point(2 0),POLYGON((0 0,1 0,1 1,0 0)))',4326));