postgresql - PostGIS 在 LINESTRING Z 上创建缓冲区以获得 POLYGON Z

Question

我在 PostgreSQL 中有几个 LINESTRING Z 几何图形，它们看起来像LINESTRING Z (1 2 1,1 1 4)

我想围绕这个线串做一个缓冲区，这样我就可以有一个 POLYGON Z geometry 用于进一步导出到 dxf。

我试过了

select st_astext(st_buffer('LINESTRING Z (1 2 1,1 1 4)'::geometry, 2)) as geom;

它给了我

多边形((3 1,2.96157056080646 0.609819355967741,2.84775906502257 0.23463313526
9818,2.66293922460509 -0.111140466039206,2.41421356237309 -0.414213562373096,2.
1111404660392 -0.662939224605091,1.76536686473018 -0.847759065022574,1.39018064
403226 -0.961570560806461,1 -1,0.609819355967745 -0.961570560806461,0.234633135
269822 -0.847759065022574,-0.111140466039202 -0.662939224605092,-0.414213562373
094 -0.414213562373096,-0.662939224605089 -0.111140466039207,-0.847759065022572
0.234633135269818,-0.96157056080646 0.609819355967739,-1 1,-1 2,-0.96157056080
646 2.39018064403226,-0.847759065022572 2.76536686473018,-0.662939224605089 3.1
1114046603921,-0.414213562373094 3.4142135623731,-0.111140466039203 3.662939224
60509,0.234633135269821 3.84775906502257,0.609819355967744 3.96157056080646,1 4
,1.39018064403226 3.96157056080646,1.76536686473018 3.84775906502257,2.11114046
60392 3.66293922460509,2.41421356237309 3.4142135623731,2.66293922460509 3.1111
4046603921,2.84775906502257 2.76536686473018,2.96157056080646 2.39018064403226,
3 2,3 1)) (1 行)

在 2D POLYGON 中而不是 POLYGON Z

我怎样才能让它变成 3D？

Answer 1

我不太确定你想要达到什么目的，但你看过 ST_Force3D 了吗？ ？

SELECT 
  ST_AsText(
    ST_Force3D(
      ST_Buffer('LINESTRING Z (1 2 1,1 1 4)'::GEOMETRY, 2)));

它将返回一个 POLYGON Z 几何体:

 POLYGON Z ((3 1 0,2.96157056080646 0.609819355967741 0,2.84775906502257 0.234633135269818 0,2.66293922460509 -0.111140466039206 0,2.41421356237309 -0.414213562373096 0,2.1111404660392 -0.662939224605091 0,1.76536686473018 -0.847759065022574 0,1.39018064403226 -0.961570560806461 0,1 -1 0,0.609819355967745 -0.961570560806461 0,0.234633135269822 -0.847759065022574 0,-0.111140466039202 -0.662939224605092 0,-0.414213562373094 -0.414213562373096 0,-0.662939224605089 -0.111140466039207 0,-0.847759065022572 0.234633135269818 0,-0.96157056080646 0.609819355967739 0,-1 1 0,-1 2 0,-0.96157056080646 2.39018064403226 0,-0.847759065022572 2.76536686473018 0,-0.662939224605089 3.11114046603921 0,-0.414213562373094 3.4142135623731 0,-0.111140466039203 3.66293922460509 0,0.234633135269821 3.84775906502257 0,0.609819355967744 3.96157056080646 0,1 4 0,1.39018064403226 3.96157056080646 0,1.76536686473018 3.84775906502257 0,2.1111404660392 3.66293922460509 0,2.41421356237309 3.4142135623731 0,2.66293922460509 3.11114046603921 0,2.84775906502257 2.76536686473018 0,2.96157056080646 2.39018064403226 0,3 2 0,3 1 0))

函数ST_Buffer丢弃 Z 维度，如文档中所述:

... This function ignores the third dimension (z) and will always give a 2-d buffer even when presented with a 3d-geometry.

编辑:

此查询类似于创建一个缓冲区，其中包含给定 LINESTRING Z 的平均 Z 值。

WITH j AS (
SELECT 
  ST_DumpPoints(
    ST_Buffer('LINESTRING Z (1 2 1,1 1 4)'::GEOMETRY, 2)
  ) AS pt,
  (SELECT AVG(z) AS avg_z 
  FROM (SELECT ST_Z((ST_DumpPoints('LINESTRING Z (1 2 1,1 1 4)'::GEOMETRY)).geom) AS z) AS z) AS lsz
)
SELECT ST_AsText(
  ST_MakePolygon(ST_MakeLine(ST_MakePoint(ST_X((pt).geom),ST_Y((pt).geom),lsz))))
FROM j
GROUP BY lsz;

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
POLYGON Z ((3 1 2.5,2.96157056080646 0.609819355967741 2.5,2.84775906502257 0.234633135269818 2.5,2.66293922460509 -0.111140466039206 2.5,2.41421356237309 -0.414213562373096 2.5,2.1111404660392 -0.662939224605091 2.5,1.76536686473018 -0.847759065022574 2.5,1.39018064403226 -0.961570560806461 2.5,1 -1 2.5,0.609819355967745 -0.961570560806461 2.5,0.234633135269822 -0.847759065022574 2.5,-0.111140466039202 -0.662939224605092 2.5,-0.414213562373094 -0.414213562373096 2.5,-0.662939224605089 -0.111140466039207 2.5,-0.847759065022572 0.234633135269818 2.5,-0.96157056080646 0.609819355967739 2.5,-1 1 2.5,-1 2 2.5,-0.96157056080646 2.39018064403226 2.5,-0.847759065022572 2.76536686473018 2.5,-0.662939224605089 3.11114046603921 2.5,-0.414213562373094 3.4142135623731 2.5,-0.111140466039203 3.66293922460509 2.5,0.234633135269821 3.84775906502257 2.5,0.609819355967744 3.96157056080646 2.5,1 4 2.5,1.39018064403226 3.96157056080646 2.5,1.76536686473018 3.84775906502257 2.5,2.1111404660392 3.66293922460509 2.5,2.41421356237309 3.4142135623731 2.5,2.66293922460509 3.11114046603921 2.5,2.84775906502257 2.76536686473018 2.5,2.96157056080646 2.39018064403226 2.5,3 2 2.5,3 1 2.5))
(1 row)