如何使用列的值作为空间操作的输入

Question

给定下面发布的代码或查询，我希望使用XOfLowerLeftOfGridCellIntersectingWithBuffer和YOfLowerLeftOfGridCellIntersectingWithBuffer的值作为以下语句的输入：

ST_MakePoint(`XOfLowerLeftOfGridCellIntersectingWithBuffer`,`YOfLowerLeftOfGridCellIntersectingWithBuffer`)

请告诉我怎样才能实现

代码：

SELECT 
ST_X((ST_DumpPoints(ST_AsText(ST_Intersection(
ST_SetSRID(
ST_MakeEnvelope(
ST_X(point),
ST_Y(point),
ST_X(point)+{width}, 
ST_Y(point)+{height}),
25832),ST_Buffer(j.geometry, {bufferRadius}) 
)))).geom) AS XOfLowerLeftOfGridCellIntersectingWithBuffer,
ST_Y((ST_DumpPoints(ST_AsText(ST_Intersection(
ST_SetSRID(
ST_MakeEnvelope(
ST_X(point),
ST_Y(point),
ST_X(point)+{width}, 
ST_Y(point)+{height}),
25832),ST_Buffer(j.geometry, {bufferRadius}) 
)))).geom) AS YOfLowerLeftOfGridCellIntersectingWithBuffer,

更新：

这是为了进一步澄清我的观点，使之更加清楚。我有一个主SELECT语句，如代码1所示。我想在主SELECT的末尾添加WITH子句，如下面发布的with-clasue部分所示统计

当我只是复制with claue并粘贴它时，我在with子句it self上收到一个错误。请告诉我如何修理

code_1：

SELECT
....
....
....
ST_X((ST_DumpPoints(ST_AsText(ST_Intersection(
ST_SetSRID(
ST_MakeEnvelope(
ST_X(point),
ST_Y(point),
ST_X(point)+{width}, 
ST_Y(point)+{height}),
25832),ST_Buffer(j.geometry, {bufferRadius}) 
)))).geom) AS XOfLowerLeftOfGridCellIntersectingWithBufferedZone,
ST_Y((ST_DumpPoints(ST_AsText(ST_Intersection(
ST_SetSRID(
ST_MakeEnvelope(
ST_X(point),
ST_Y(point),
ST_X(point)+{width}, 
ST_Y(point)+{height}),
25832),ST_Buffer(j.geometry, {bufferRadius})
)))).geom) AS YOfLowerLeftOfGridCellIntersectingWithBuffer,


ST_SetSRID(ST_MakePoint((ST_X((ST_DumpPoints(ST_AsText(ST_Intersection(
ST_SetSRID(
ST_MakeEnvelope(
ST_X(point),
ST_Y(point),
ST_X(point)+{width}, 
ST_Y(point)+{height}),
25832),ST_Buffer(j.geometry, {bufferRadius}) 
)))).geom))+5, (ST_Y((ST_DumpPoints(ST_AsText(ST_Intersection(
ST_SetSRID(
ST_MakeEnvelope(
ST_X(point),
ST_Y(point),
ST_X(point)+{width}, 
ST_Y(point)+{height}),
25832),ST_Buffer(j.geometry, {bufferRadius})
)))).geom))+5 ),25832) As midPoint,

--WITH clause is to be added here.

带有clas

WITH j AS (
SELECT 1 AS X, 2 AS y -- your big query goes here
ST_X((ST_DumpPoints(ST_AsText(ST_Intersection(
ST_SetSRID(
ST_MakeEnvelope(
ST_X(point),
ST_Y(point),
ST_X(point)+{width}, 
ST_Y(point)+{height}),
25832),ST_Buffer(j.geometry, {bufferRadius}) 
)))).geom) AS XOfLowerLeftOfGridCellIntersectingWithBufferedZone,
T_Y((ST_DumpPoints(ST_AsText(ST_Intersection(
ST_SetSRID(
ST_MakeEnvelope(
ST_X(point),
ST_Y(point),
ST_X(point)+{width}, 
ST_Y(point)+{height}),
25832),ST_Buffer(j.geometry, {bufferRadius})
)))).geom) AS YOfLowerLeftOfGridCellIntersectingWithBuffer
),
SELECT ST_MakePoint(XOfLowerLeftOfGridCellIntersectingWithBuffer,YOfLowerLeftOfGridCellIntersectingWithBuffer) As XYPointOfLowerLeftGridCellIntersectingWithBufferedZoneInEPSG25832

小提琴：

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=f2928841af169e69c72faf282f04390b

Answer 1

如果它们必须在单个查询中，则只需在ST_MakePoint函数中使用ST_X和ST_Y的输出值。如果x和y值在列中，或者是运算的结果，则只需在函数中传递这些值：

SELECT ST_MakePoint(column_x,column_y) FROM t;

或者如果它们在几何体内部。。

SELECT ST_MakePoint(ST_X(a_geom),ST_Y(a_geom)) FROM t;

使用CTE或子查询(请参见注释(。原理类似，但使用CTE可以创建一个临时集合，并将其用作表。下面的示例生成x和y值并将其命名为j，然后在外部查询中捕获这些值以创建具有另一个SELECT的点，但这次使用j:

WITH j AS (
SELECT 1 AS X, 2 AS y -- your big query goes here
) 
SELECT ST_MakePoint(X,Y) FROM j;

将其应用于查询。。

演示(子查询(：db<>fiddle

演示(CTE(：db<>fiddle

WITH j AS (
SELECT 
ST_X((ST_DumpPoints(ST_AsText(ST_Intersection(
ST_SetSRID(
ST_MakeEnvelope(
ST_X(point),
ST_Y(point),
ST_X(point)+{width}, 
ST_Y(point)+{height}),
25832),ST_Buffer(j.geometry, {bufferRadius}) 
)))).geom) AS XOfLowerLeftOfGridCellIntersectingWithBuffer,
ST_Y((ST_DumpPoints(ST_AsText(ST_Intersection(
ST_SetSRID(
ST_MakeEnvelope(
ST_X(point),
ST_Y(point),
ST_X(point)+{width}, 
ST_Y(point)+{height}),
25832),ST_Buffer(j.geometry, {bufferRadius}) 
)))).geom) AS YOfLowerLeftOfGridCellIntersectingWithBuffer, 
....
)
SELECT ST_MakePoint(XOfLowerLeftOfGridCellIntersectingWithBuffer,
YOfLowerLeftOfGridCellIntersectingWithBuffer)
FROM j                  

关于您的查询的一些想法(无法看到大局(：

• ST_AsText在您的查询中毫无意义。你可以摆脱它
• 请注意，用于提取x和y坐标的代码是相同的，并且ST_DumpPoints已经返回了点。所以，我认为你的逻辑是有缺陷的，因为你正在重新创造你以前分裂成不同价值观的同一点