如何使用列的值作为空间操作的输入 [英] how to use value of a column as input to a spatial operation
问题描述
鉴于下面发布的代码或查询，我想使用 XOfLowerLeftOfGridCellIntersectingWithBuffer snd YOfLowerLeftOfGridCellIntersectingWithBuffer 作为以下语句的输入:
given the code or the query posted below i would like to use the value of XOfLowerLeftOfGridCellIntersectingWithBuffer snd YOfLowerLeftOfGridCellIntersectingWithBufferas input to the following statment:
ST_MakePoint(`XOfLowerLeftOfGridCellIntersectingWithBuffer`,`YOfLowerLeftOfGridCellIntersectingWithBuffer`)
请让我知道我怎样才能做到这一点
please let me know how can i achieve that
代码:
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 语句，如 code1 所示.我想将下面发布的 with-clasue 部分中显示的 WITH 子句添加到主 SELECT 的末尾声明
this is to further clarify my point ans make clearer. i have a main SELECT statement as shown in code1 . and i would like to add the WITH-clause the one shown in with-clasuesection posted below to the end of the main SELECT
statment
当我只是复制 with clasue 并粘贴它时，我收到了一个关于 with 子句的错误.请让我知道如何修复它
when i just copy the with clasue and paste it i receive an error on the with clause it self. please let me know how can i fix it
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.
带子句
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_X 和 ST_Y<的输出值/code> 在 ST_MakePoint 函数中.如果 x 和 y 值在列中或者是操作的结果，您只需在函数中传递这些值:
If they have to be in a single query, you simply need to use the output values of ST_X and ST_Y in the ST_MakePoint function. If the x and y values are in columns or are the result of an operation, you simply need to pass these values in the function:
SELECT ST_MakePoint(column_x,column_y) FROM t;
或者如果它们在几何图形内..
Or in case they're inside geometries..
SELECT ST_MakePoint(ST_X(a_geom),ST_Y(a_geom)) FROM t;
使用 CTE 或子查询(见评论).原理是相似的，但是使用 CTE 可以创建一个临时集并将其用作表.以下示例生成 x 和 y 值并将其命名为 j，然后在外部查询中捕获这些值以使用另一个 SELECT 创建一个点，但这次使用 j:
Using a CTE or a subquery (See comments). The principle is similar, but using a CTE you sort of create a temporary set and use it was as a table. The following example generates x and y values and name it j, then in the outer query you catch these values to create a point with another SELECT, but this time using j:
WITH j AS (
SELECT 1 AS X, 2 AS y -- your big query goes here
)
SELECT ST_MakePoint(X,Y) FROM j;
将其应用于您的查询..
Applying it to your query ..
演示(子查询):db<>fiddle
Demo (subquery): db<>fiddle
演示 (CTE):db<>fiddle
Demo (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
关于您的查询的一些想法(无法看到大图):
A few thoughts on your query (without being able to see the big picture):
ST_AsText在您的查询中绝对没有意义.你可以摆脱它.- 请注意，您用来提取 x 和 y 坐标的代码是相同的，并且
ST_DumpPoints已经返回点.因此，我认为您的逻辑存在缺陷，因为您正在重新创建之前拆分为多个单独值的同一点.
ST_AsTextdefinitely make no sense in you query. You can get rid of it.- Note that the code you're using to extract the x and y coordinates are identical, and
ST_DumpPointsalready returns points. So, I believe your logic is flawed, since you're re-recreating the same point you previously split into separated values.
这篇关于如何使用列的值作为空间操作的输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
