postgresql几何类型和函数

postgresql支持的几何类型如下表：

名字	存储空间	描述	表现形式
point	16字节	平面上的点	(x,y)
line	32字节	直线	{A,B,C}
lseg	32字节	线段	((x1,y1),(x2,y2))
box	32字节	矩形	((x1,y1),(x2,y2))
path	16+16n字节	闭合路径	((x1,y1),...)
path	16+16n字节	开放路径	[(x1,y1),...]
polygon	40+16n字节	多边形	((x1,y1),...)
circle	24字节	圆	<(x,y),r>

示例：

test=# select point'(1,1)';
 point 
-------
 (1,1)
(1 row)

test=# select line'{1,1,1}';
  line   
---------
 {1,1,1}
(1 row)

test=# select lseg'(1,1),(2,2)';
     lseg      
---------------
 [(1,1),(2,2)]
(1 row)

test=# select box'(1,1),(2,2)';
     box     
-------------
 (2,2),(1,1)
(1 row)

test=# select path'(1,1),(2,2),(2,1)';
        path         
---------------------
 ((1,1),(2,2),(2,1))
(1 row)

test=# select path'[(1,1),(2,2),(2,1)]';
        path         
---------------------
 [(1,1),(2,2),(2,1)]
(1 row)

test=# select polygon'((1,1),(2,2),(2,1))';
       polygon       
---------------------
 ((1,1),(2,2),(2,1))
(1 row)

test=# select circle'<(0,0),1>';
  circle   
-----------
 <(0,0),1>
(1 row)

操作符

操作符	描述	示例	结果
+	平移	select box '((0,0),(1,1))' + point '(2.0,0)';	(3,1),(2,0)
-	平移	select box '((0,0),(1,1))' - point '(2.0,0)';	(-1,1),(-2,0)
*	伸缩/旋转	select box '((0,0),(1,1))' * point '(2.0,0)';	(2,2),(0,0)
/	伸缩/旋转	select box '((0,0),(2,2))' / point '(2.0,0)';	(1,1),(0,0)
#	交点或者交面	select box'((1,-1),(-1,1))' # box'((1,1),(-1,-1))';	(1,1),(-1,-1)
#	path或polygon的顶点数	select #path'((1,1),(2,2),(2,1))';	3
@-@	长度或周长	select @-@ path'((1,1),(2,2),(2,1))';	3.41421356237309
@@	中心	select @@ circle'<(0,0),1>';	(0,0)
##	第一个操作数和第二个操作数的最近点	select point '(0,0)' ## lseg '((2,0),(0,2))';	(1,1)
<->	间距	select circle '<(0,0),1>' <-> circle '<(5,0),1>';	3
&&	是否有重叠	select box '((0,0),(1,1))' && box '((0,0),(2,2))';	t
<<	是否严格在左	select circle '((0,0),1)' << circle '((5,0),1)';	t
>>	是否严格在右	select circle '((0,0),1)' >> circle '((5,0),1)';	f
&<	是否没有延伸到右边	select box '((0,0),(1,1))' &< box '((0,0),(2,2))';	t
&>	是否没有延伸到左边	select box '((0,0),(3,3))' &> box '((0,0),(2,2))';	t
<<|	是否严格在下	select box '((0,0),(3,3))' <<| box '((3,4),(5,5))';	t
|>>	是否严格在上	select box '((3,4),(5,5))' |>> box '((0,0),(3,3))';	t
&<|	是否没有延伸到上面	select box '((0,0),(1,1))' &<| box '((0,0),(2,2))';	t
|&>	是否没有延伸到下面	select box '((0,0),(3,3))' |&> box '((0,0),(2,2))';	t
<^	是否低于（允许接触）	select box '((0,0),(3,3))' <^ box '((3,3),(4,4))';	t
>^	是否高于（允许接触）	select box '((0,0),(3,3))' >^ box '((3,3),(4,4))';	f
?#	是否相交	select lseg '((-1,0),(1,0))' ?# box '((-2,-2),(2,2))';	t
?-	是否水平对齐	select ?- lseg '((-1,1),(1,1))';	t
?-	两边图形是否水平对齐	select point '(1,0)' ?- point '(0,0)';	t
?|	是否竖直对齐	select ?| lseg '((-1,0),(1,0))';	f
?|	两边图形是否竖直对齐	select point '(0,1)' ?| point '(0,0)';	t
?-|	是否垂直	select lseg '((0,0),(0,1))' ?-| lseg '((0,0),(1,0))';	t
?||	是否平行	select lseg '((-1,0),(1,0))' ?|| lseg '((-1,2),(1,2))';	t
@>	是否包含	select circle '((0,0),2)' @> point '(1,1)';	t
<@	是否包含于或在图形上	select point '(1,1)' <@ circle '((0,0),2)';	t
~=	是否相同	select polygon '((0,0),(1,1))' ~= polygon '((1,1),(0,0))';	t

函数

函数	返回值类型	描述	示例	结果
area(object)	double precision	面积	select area(circle'((0,0),1)');	3.14159265358979
center(object)	point	中心	select center(box'(0,0),(1,1)');	(0.5,0.5)
diameter(circle)	double precision	圆周长	select diameter(circle '((0,0),2.0)');	4
height(box)	double precision	矩形竖直高度	select height(box '((0,0),(1,1))');	1
isclosed(path)	boolean	是否为闭合路径	select isclosed(path '((0,0),(1,1),(2,0))');	t
isopen(path)	boolean	是否为开放路径	select isopen(path '[(0,0),(1,1),(2,0)]');	t
length(object)	double precision	长度	select length(path '((-1,0),(1,0))');	4
npoints(path)	int	path中的顶点数	select npoints(path '[(0,0),(1,1),(2,0)]');	3
npoints(polygon)	int	多边形的顶点数	select npoints(polygon '((1,1),(0,0))');	2
pclose(path)	path	将开放path转换为闭合path	select pclose(path '[(0,0),(1,1),(2,0)]');	((0,0),(1,1),(2,0))
popen(path)	path	将闭合path转换为开放path	select popen(path '((0,0),(1,1),(2,0))');	[(0,0),(1,1),(2,0)]
radius(circle)	double precision	圆半径	select radius(circle '((0,0),2.0)');	2
width(box)	double precision	矩形的水平长度	select width(box '((0,0),(1,1))');	1

类型转换函数

函数	返回类型	描述	示例	结果
box(circle)	box	圆形转矩形	select box(circle '((0,0),2.0)');	(1.41421356237309,1.41421356237309),(-1.41421356237309,-1.41421356237309)
box(point)	box	点转空矩形	select box(point '(0,0)');	(0,0),(0,0)
box(point, point)	box	点转矩形	select box(point '(0,0)', point '(1,1)');	(1,1),(0,0)
box(polygon)	box	多边形转矩形	select box(polygon '((0,0),(1,1),(2,0))');	(2,1),(0,0)
bound_box(box, box)	box	将两个矩形转换成一个边界矩形	select bound_box(box '((0,0),(1,1))', box '((3,3),(4,4))');	(4,4),(0,0)
circle(box)	circle	矩形转圆形	select circle(box '((0,0),(1,1))');	<(0.5,0.5),0.707106781186548>
circle(point, double precision)	circle	圆心与半径转圆形	select circle(point '(0,0)', 2.0);	<(0,0),2>
circle(polygon)	circle	多边形转圆形	select circle(polygon '((0,0),(1,1),(2,0))');	<(1,0.333333333333333),0.924950591148529>
line(point, point)	line	点转直线	select line(point '(-1,0)', point '(1,0)');	{0,-1,0}
lseg(box)	lseg	矩形转线段	select lseg(box '((-1,0),(1,0))');	[(1,0),(-1,0)]
lseg(point, point)	lseg	点转线段	select lseg(point '(-1,0)', point '(1,0)');	[(-1,0),(1,0)]
path(polygon)	path	多边形转path	select path(polygon '((0,0),(1,1),(2,0))');	((0,0),(1,1),(2,0))
point(double precision, double precision)	point	点	select point(23.4, -44.5);	(23.4,-44.5)
point(box)	point	矩形转点	select point(box '((-1,0),(1,0))');	(0,0)
point(circle)	point	圆心	select point(circle '((0,0),2.0)');	(0,0)
point(lseg)	point	线段中心	select point(lseg '((-1,0),(1,0))');	(0,0)
point(polygon)	point	多边形的中心	select point(polygon '((0,0),(1,1),(2,0))');	(1,0.333333333333333)
polygon(box)	polygon	矩形转4点多边形	select polygon(box '((0,0),(1,1))');	((0,0),(0,1),(1,1),(1,0))
polygon(circle)	polygon	圆形转12点多边形	select polygon(circle '((0,0),2.0)');	((-2,0),(-1.73205080756888,1),(-1,1.73205080756888),(-1.22460635382238e-16,2),(1,1.73205080756888),(1.73205080756888,1),(2,2.4492127 0764475e-16),(1.73205080756888,-0.999999999999999),(1,-1.73205080756888),(3.67381906146713e-16,-2),(-0.999999999999999,-1.73205080756 888),(-1.73205080756888,-1))
polygon(npts, circle)	polygon	圆形转npts点多边形	select polygon(12, circle '((0,0),2.0)');	((-2,0),(-1.73205080756888,1),(-1,1.73205080756888),(-1.22460635382238e-16,2),(1,1.73205080756888),(1.73205080756888,1),(2,2.4492127 0764475e-16),(1.73205080756888,-0.999999999999999),(1,-1.73205080756888),(3.67381906146713e-16,-2),(-0.999999999999999,-1.73205080756 888),(-1.73205080756888,-1))
polygon(path)	polygon	将path转多边形	select polygon(path '((0,0),(1,1),(2,0))');	((0,0),(1,1),(2,0))

PostGIS拆分LineString为segment,point

 CREATE TABLE lines ( 
      gid integer primary key, 
      geom geometry(Linestring, 4326)
 );
 
 INSERT INTO lines VALUES (1, 'SRID=4326;LINESTRING(111.6676775 40.8276439, 111.6676775 40.8277439, 111.6676775 40.8278439, 111.6676775 40.8279439)');
 INSERT INTO lines VALUES (2, 'SRID=4326;LINESTRING(111.6676775 40.8277439, 111.6676775 40.8278439, 111.6676775 40.8279439)');
 
 -- 拆分成点
 SELECT gid,(pt).path as ptindex,(pt).geom
FROM (SELECT gid, ST_DumpPoints(geom) AS pt FROM lines) as foo;
-- 拆分成线
 WITH segments AS (
    SELECT gid, ST_MakeLine(lag((pt).geom, 1, NULL) OVER (PARTITION BY gid ORDER BY gid, (pt).path), (pt).geom) AS geom
      FROM (SELECT gid, ST_DumpPoints(geom) AS pt FROM lines) as dumps
    )
SELECT * FROM segments WHERE geom IS NOT NULL;

合并：

方法名	入参	出参	使用场景
ST_Union	任何图形	Multi类型，collection类型	聚合后的图形联合
ST_LineMerge	MultiLineString	LineString,MultiLineString	将MultiLineString中相连的线路合并成完整的LineString
ST_MakeLine	Point,MultiPoint,LineString	LineString	构造新的线路

原文链接：

PostgreSQL: Documentation: 9.6: Geometric Functions and Operators