给定下面发布的代码或查询,我想使用XOfLowerLeftOfGridCellIntersectingWithBuffer
sndYOfLowerLeftOfGridCellIntersectingWithBuffer
的值作为以下语句的输入:
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所示。我想将下面发布的with-clasue
部分中所示的子句添加到主SELECT
的末尾
固定
当我只是复制with clasue并粘贴它时,我在with ITS self子句上收到一个错误。请让我知道如何修理它
代码_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.
与克劳斯一起
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
如果它们必须位于单个查询中,则只需在
ST_MakePoint
函数中使用ST_X
和ST_Y
的输出值即可。如果x和y值在列中或是操作的结果,则只需在函数中传递这些值:或者如果它们在几何体内部
使用
CTE
或子查询(请参见注释)。原理类似,但使用CTE可以创建一个临时集合,并将其用作表。下面的示例生成x和y值并将其命名为j
,然后在外部查询中捕获这些值以使用另一个SELECT
创建点,但这次使用j
:将其应用于您的查询
演示(子查询):^{}
演示(CTE):^{}
关于您的问题的一些想法(无法看到全局):
ST_AsText
在你的查询中毫无意义。你可以摆脱它李>ST_DumpPoints
已经返回点。所以,我相信你的逻辑是有缺陷的,因为你正在重新创建你之前分割成不同值的相同点李>相关问题 更多 >
编程相关推荐