从SQLite上的WITH子句更新数据

Question

我有一个表**属性**，其中记录了postgis坐标列geoarea(点(，以及一个bool列within_area内(布尔(，以确定其是否在表urbanArea中的另一个postgisst_union中(多边形(

select properties.id, ST_Within(properties.geoarea,st_transform("urbanArea"."st_union",2393)) from properties,"urbanArea"

它按id返回属性中的所有记录，如果不在区域内，则返回bool值。执行查询大约需要10秒

我现在想从select语句中获取值，并将其插入到within_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

Answer 1

在第二个查询中删除对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));