我有一個表 **property ** 帶有 postgis 坐標列geoarea (Points) 的記錄和一個 bool 列inside_area (Bool) 來確定它是否在表urbanArea 中的另一個 postgis st_union (Polygon) 的內部
select properties.id, ST_Within(properties.geoarea,st_transform("urbanArea"."st_union",2393)) from properties,"urbanArea"
它按 id 回傳屬性中的所有記錄，如果它在該區域內，則回傳 bool 值。執行查詢大約需要 10 秒
我現在想從那個 select 陳述句中獲取值并將它插入到inside_area列中，我想出了這個 SQL 查詢，但它永遠掛起并且沒有完成，知道為什么嗎？
UPDATE properties p
SET within_area = (
with newarea as (select properties.id, ST_Within(properties.geoarea,st_transform("urbanArea"."st_union",2393)) as "isInside" from properties,"urbanArea")
select u."isInside" from newarea u where u.id = p.id
)
我也試過用 CTE 做它，但它仍然永遠掛起。
with newarea as (select properties.id, ST_Within(properties.geoarea,st_transform("urbanArea"."st_union",2393)) from properties, "urbanArea")
UPDATE properties
SET
withinurban=newa.st_within
FROM properties prop
INNER JOIN
newarea newa
ON prop.id = newa.id
uj5u.com熱心網友回復：
properties在第二個查詢中洗掉額外的連接：
WITH newarea AS (
SELECT p.id,
ST_Within(p.geoarea, ST_Transform(u."st_union", 2393)) st_within
FROM properties p, "urbanArea" u
)
UPDATE properties p
SET withinurban = n.st_within
FROM newarea n
WHERE p.id = n.id;
但是，您的代碼似乎相當于：
UPDATE properties p
SET withinurban = ST_Within(p.geoarea, ST_Transform(u."st_union", 2393))
FROM "urbanArea" u;
或者：
UPDATE properties p
SET withinurban = ST_Within(p.geoarea, ST_Transform((SELECT "st_union" FROM "urbanArea"), 2393));
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/363308.html