一、Geography类型概述
1.1、Geography数据类型是什么?Geography数据类型和Geometry数据类型区别又是什么?
PostGIS的GEOGRAPHY数据类型基于大地坐标系对经纬度坐标(也可称为大地坐标或者地理坐标)数据提供支持,大地坐标是用角度单位表示的球面坐标。而Geometry是基于二维笛卡尔坐标系-Cartesian Coordinate System(平面坐标系,投影坐标系)的,投影坐标通常是以米(meter)、千米(kilometer)等作为单位。
Geometry和Geography这两种数据类型的最大不同之处是它们对空间数据的计算方式不同,这也是最能体现Geography数据类型价值的地方。例如,计算两个坐标点的距离,Geometry数据类型的数据是基于平面坐标系来计算的,也就是将两个坐标点看作是处于同一个平面,使用类似勾股定理之类的公式,计算它们之间的平面距离。而Geography数据类型的数据是基于球面坐标来计算的,也就是将两个坐标点看作地球参考椭球体上的两个坐标点,计算它们之间的大圆航线的距离。(下图是球体上的大圆航线和平面距离的对比)
1.2、Geography数据类型和空间函数
我们都知道,在球面上的计算比在平面上的计算更复杂,代价更高,所以Geography数据类型支持的空间函数比Geometry数据类型支持的空间函数少(PostGIS依赖的GEOS库实现的函数,没有一个支持Geography数据类型)。不过,Geography数据类型与Geometry数据类型可以相互转换。
1.3、Geography数据类型相关的版本问题
在PostGIS的2.2版本之前,PostGIS只支持WGS84地理坐标系(SRID:4326)。在2.2版本之后,Geography数据类型支持spatial_ref_sys元数据表中的所有地理坐标系。你甚至可以添加你自己自定义球面空间参考系统,相关内容可以参考下面这篇文章:http://www.bostongis.com/blog/index.php?/archives/266-geography-type-is-not-limited-to-earth.html
二、Geography数据类型的子类型
除了曲线、TIN、POLYHEDRALSURFACE相关的数据类型,Geography数据类型支持所有OGC标准的子类型,同样也能基于EWKT和EWKB的形式输入/输出数据。
和Geometry数据类型一样,Geography数据类型也使用PostgreSQL的类型修饰符(typmod)定义子类型。
2.1、点数据类型
点数据类型有:
POINTPOINTZPOINTMPOINTZM
CREATE TABLE point2(
id SERIAL PRIMARY KEY,
point GEOGRAPHY(POINT, 4326),
pointz GEOGRAPHY(POINTZ, 4326),
pointm GEOGRAPHY(POINTM, 4326),
pointzm GEOGRAPHY(POINTZM, 4326)
);
INSERT INTO point2(
point,
pointz,
pointm,
pointzm)
VALUES (
ST_GeographyFromText(SRID=4326;POINT (10 10)),
ST_GeographyFromText(POINT Z (10 10 10)), -- 默认是4326地理坐标系
ST_GeographyFromText(POINT M (10 10 10)),
ST_GeographyFromText(POINT ZM (10 10 10 10))
);
SELECT GeometryType(point) AS point, GeometryType(pointz) AS pointz,
GeometryType(pointm) AS pointm, GeometryType(pointzm) AS pointzm
FROM point2;
结果:
2.2、LineString(线串)
线串数据类型有:
LINESTRINGLINESTRINGZLINESTRINGMLINESTRINGZM
示例:
CREATE TABLE linestring2(
id SERIAL PRIMARY KEY,
linestring GEOGRAPHY(LINESTRING, 4326),
linestringz GEOGRAPHY(LINESTRINGZ, 4326),
linestringm GEOGRAPHY(LINESTRINGM, 4326),
linestringzm GEOGRAPHY(LINESTRINGZM, 4326)
);
INSERT INTO linestring2(
linestring,
linestringz,
linestringm,
linestringzm)
VALUES (
ST_GeographyFromText(SRID=4326;LINESTRING (10 10, 20 20, 30 30)),
ST_GeographyFromText(SRID=4326;LINESTRING Z (10 10 10, 20 20 20, 30 30 30)),
ST_GeographyFromText(LINESTRING M (10 10 10, 20 20 20, 30 30 30)),
ST_GeographyFromText(LINESTRING ZM (10 10 10 10, 20 20 20 20, 30 30 30 30))
);
SELECT GeometryType(linestring) AS linestring, GeometryType(linestringz) AS linestringz,
GeometryType(linestringm) AS linestringm, GeometryType(linestringzm) AS linestringzm
FROM linestring2;
结果:
2.3、POLYGON(多边形)
多边形数据类型有:
POLYGONPOLYGONZPOLYGONMPOLYGONZM
示例:
CREATE TABLE polygon2(
id SERIAL PRIMARY KEY,
polygon GEOGRAPHY(POLYGON, 4326),
polygonWithHole GEOGRAPHY(POLYGON, 4326),
polygonz GEOGRAPHY(POLYGONZ, 4326),
polygonm GEOGRAPHY(POLYGONM, 4326),
polygonzm GEOGRAPHY(POLYGONZM, 4326)
);
INSERT INTO polygon2(
polygon,
polygonWithHole,
polygonz,
polygonm,
polygonzm)
VALUES (
ST_GeogFromText(POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))),
ST_GeogFromText(POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))),
ST_GeogFromText(POLYGON Z ((0 0 0, 10 0 0, 10 10 10, 0 10 10, 0 0 0))),
ST_GeogFromText(POLYGON M ((0 0 0, 10 0 0, 10 10 10, 0 10 10, 0 0 0))),
ST_GeogFromText(POLYGON ZM ((0 0 0 0, 10 0 0 0, 10 10 10 10, 0 10 10 10, 0 0 0 0)))
);
SELECT GeometryType(polygon) AS polygon, GeometryType(polygonWithHole) AS polygonWithHole,
GeometryType(polygonz) AS polygonz, GeometryType(polygonm) AS polygonm, GeometryType(polygonzm) AS polygonzm
FROM polygon2;
结果:
2.4、GeometryCollection(图形集合)
GeometryCollection数据类型有:
MULTIPOINT、MULTIPOINTZ、MULTIPOINTM、MULTIPOINTZMMULTILINESTRING、MULTILINESTRINGZ、MULTILINESTRINGM、MULTILINESTRINGZMMULTIPOLYGON、MULTIPOLYGONZ、MULTIPOLYGONM、MULTIPOLYGONZMGEOMETRYCOLLECTION、GEOMETRYCOLLECTIONZ、GEOMETRYCOLLECTIONM、GEOMETRYCOLLECTIONZM
示例1:
CREATE TABLE multipoint2 (
multipoint GEOGRAPHY(MULTIPOINT, 4326),
multipointz GEOGRAPHY(MULTIPOINTZ, 4326),
multipointm GEOGRAPHY(MULTIPOINTM, 4326),
multipointzm GEOGRAPHY(MULTIPOINTZM, 4326)
);
INSERT INTO multipoint2(
multipoint,
multipointz,
multipointm,
multipointzm
)
VALUES (
ST_GeogFromText(MULTIPOINT (-1 1, 0 0, 2 3)),
ST_GeogFromText(MULTIPOINT Z (-1 1 3, 0 0 1, 2 3 1)),
ST_GeogFromText(MULTIPOINT M (-1 1 4, 0 0 2, 2 3 2)),
ST_GeogFromText(MULTIPOINT ZM (-1 1 3 4, 0 0 1 2, 2 3 1 2))
);
SELECT GeometryType(multipoint) AS multipoint, GeometryType(multipointz) AS multipointz,
GeometryType(multipointm) AS multipointm, GeometryType(multipointzm) AS multipointzm
FROM multipoint2;
结果:
示例2:
CREATE TABLE multilinestring2 (
multilinestring GEOGRAPHY(MULTILINESTRING, 4326),
multilinestringz GEOGRAPHY(MULTILINESTRINGZ, 4326),
multilinestringm GEOGRAPHY(MULTILINESTRINGM, 4326),
multilinestringzm GEOGRAPHY(MULTILINESTRINGZM, 4326)
);
INSERT INTO multilinestring2(
multilinestring,
multilinestringz,
multilinestringm,
multilinestringzm
)
VALUES (
ST_GeogFromText(MULTILINESTRING ((0 0,0 1,1 1), (-1 1,-1 -1))),
ST_GeogFromText(MULTILINESTRING Z((0 0 1,0 1 2,1 1 3), (-1 1 1,-1 -1 2))),
ST_GeogFromText(MULTILINESTRING M ((0 0 1,0 1 2,1 1 3), (-1 1 1,-1 -1 2))),
ST_GeogFromText(MULTILINESTRING ZM ((0 0 1 1,0 1 1 2,1 1 1 3), (-1 1 1 1,-1 -1 1 2)))
);
SELECT GeometryType(multilinestring) AS multilinestring, GeometryType(multilinestringz) AS multilinestringz,
GeometryType(multilinestringm) AS multilinestringm, GeometryType(multilinestringzm) AS multilinestringzm
FROM multilinestring2;
结果:
示例3:
CREATE TABLE multipolygon2 (
multipolygon GEOGRAPHY(MULTIPOLYGON, 4326),
multipolygonz GEOGRAPHY(MULTIPOLYGONZ, 4326),
multipolygonm GEOGRAPHY(MULTIPOLYGONM, 4326),
multipolygonzm GEOGRAPHY(MULTIPOLYGONZM, 4326)
);
INSERT INTO multipolygon2(
multipolygon,
multipolygonz,
multipolygonm,
multipolygonzm
)
VALUES (
ST_GeogFromText(MULTIPOLYGON (((2.25 0, 1.25 1, 1.25 -1, 2.25 0)),
((1 -1, 1 1, 0 0, 1 -1)))),
ST_GeogFromText(MULTIPOLYGON Z (((2.25 0 1, 1.25 1 1, 1.25 -1 1, 2.25 0 1)),
((1 -1 2, 1 1 2, 0 0 2, 1 -1 2)))),
ST_GeogFromText(MULTIPOLYGON M (((2.25 0 1, 1.25 1 2, 1.25 -1 1, 2.25 0 1)),
((1 -1 1, 1 1 2, 0 0 3, 1 -1 4)))),
ST_GeogFromText(MULTIPOLYGON ZM (((2.25 0 1 1, 1.25 1 1 2, 1.25 -1 1 1, 2.25 0 1 1)),
((1 -1 2 1, 1 1 2 2, 0 0 2 3, 1 -1 1 4))))
);
SELECT GeometryType(multipolygon) AS multipolygon, GeometryType(multipolygonz) AS multipolygonz,
GeometryType(multipolygonm) AS multipolygonm, GeometryType(multipolygonzm) AS multipolygonzm
FROM multipolygon2;
结果:
示例4:
CREATE TABLE geometrycollection2 (
geometrycollection GEOGRAPHY(GEOMETRYCOLLECTION, 4326),
geometrycollectionz GEOGRAPHY(GEOMETRYCOLLECTIONZ, 4326),
geometrycollectionm GEOGRAPHY(GEOMETRYCOLLECTIONM, 4326),
geometrycollectionzm GEOGRAPHY(GEOMETRYCOLLECTIONZM, 4326)
);
INSERT INTO geometrycollection2(
geometrycollection,
geometrycollectionz,
geometrycollectionm,
geometrycollectionzm
)
VALUES (
ST_GeogFromText(GEOMETRYCOLLECTION (
MULTIPOINT(-1 1, 0 0, 2 3),
MULTILINESTRING((0 0, 0 1, 1 1), (-1 1, -1 -1)),
POLYGON(
(-0.25 -1.25, -0.25 1.25, 2.5 1.25, 2.5 -1.25, -0.25 -1.25),
(2.25 0,1.25 1,1.25 -1,2.25 0),
(1 -1, 1 1, 0 0, 1 -1)
)
)),
ST_GeogFromText(GEOMETRYCOLLECTION Z (
MULTIPOINT Z (-1 1 1, 0 0 0, 2 3 3),
MULTILINESTRING Z ((0 0 0, 0 1 1, 1 1 1), (-1 1 1, -1 -1 -1)),
POLYGON Z (
(-0.25 -1.25 1, -0.25 1.25 1, 2.5 1.25 2, 2.5 -1.25 3, -0.25 -1.25 4),
(2.25 0 5,1.25 1 2,1.25 -1 3,2.25 0 4),
(1 -1 2, 1 1 3, 0 0 4, 1 -1 2)
)
)),
ST_GeogFromText(GEOMETRYCOLLECTION M (
MULTIPOINT M (-1 1 1, 0 0 0, 2 3 3),
MULTILINESTRING M ((0 0 0, 0 1 1, 1 1 1), (-1 1 1, -1 -1 -1)),
POLYGON M (
(-0.25 -1.25 1, -0.25 1.25 1, 2.5 1.25 2, 2.5 -1.25 3, -0.25 -1.25 4),
(2.25 0 5,1.25 1 2,1.25 -1 3,2.25 0 4),
(1 -1 2, 1 1 3, 0 0 4, 1 -1 2)
)
)),
ST_GeogFromText(GEOMETRYCOLLECTION ZM (
MULTIPOINT ZM (-1 1 1 1, 0 0 0 1, 2 3 3 3),
MULTILINESTRING ZM ((0 0 0 2, 0 1 1 1, 1 1 1 2), (-1 1 1 3, -1 -1 -1 4)),
POLYGON ZM (
(-0.25 -1.25 1 2, -0.25 1.25 1 3, 2.5 1.25 2 2, 2.5 -1.25 3 4, -0.25 -1.25 4 5),
(2.25 0 5 2,1.25 1 2 3,1.25 -1 3 4,2.25 0 4 5),
(1 -1 2 2, 1 1 3 3, 0 0 4 4, 1 -1 2 6)
)
))
);
SELECT GeometryType(geometrycollection) AS geometrycollection, GeometryType(geometrycollectionz) AS geometrycollectionz,
GeometryType(geometrycollectionm) AS geometrycollectionm, GeometryType(geometrycollectionzm) AS geometrycollectionzm
FROM geometrycollection2;
结果:
三、Geography和Geometry的互相转换
关于Geography和Geometry的互相转换可以查看下面这篇文章:https://zhuanlan.zhihu.com/p/63173609。
四、Geography支持的空间函数
可以查看通过以下链接查看Geography支持的空间函数:
http://postgis.net/docs/manual-3.0/PostGIS_Special_Functions_Index.html#PostGIS_GeographyFunctionshttp://postgis.net/docs/manual-3.0/PostGIS_Special_Functions_Index.html#PostGIS_TypeFunctionMatrix
》的全部内容,本文网址:https://www.7ca.cn/baike/43904.shtml,如对您有帮助可以分享给好友,谢谢。