sql - 如何查找geom数据类型中不同表中两列之间的距离

Question

通过查找表 2 的 center_geom 和表 3 的 home_location 之间的距离，更新表 1 的“距离”列中的值。

表一

obu_id     end_location     trip_id   end_loc_adj    distance

1          51               1234     
2          57               1357      44
2          63               1351
3          21               1212      20
3          23               4313

表2

id        obu_id          center_geom 
int       int             geom

51        2               "0101000020ED0800006DFFCAA2A2553341B20E4717774E0C41"
52        3               "0101000020ED080000AE47E17A35F73341FE65F764723C0841"
57        3               "0101000020ED0800006DFFCAA2A2553341B20E4717774E0C41"
21        4               "0101000020ED080000B81E852BC555334186048C9EB1C21141"

1. 现在，请参阅表 1:如果我们在 end_loc_adj 中有任何值，则必须使用列:end_loc_adj 而不是 end_loc 提示:使用 if-else。
2. 表 1 中的列名“end_loc”与表 2 中的列“id”具有相同的值。

表3

hhid           obu_id          home_location
(character     Int             geometry 
varying)       
11             1               "0101000020ED08000082E2C7A0B2413341BC5818A21F000941"
15             2               "0101000020ED080000B81E852BC555334186048C9EB1C21141"
17             3               "0101000020ED0800006DFFCAA2A2553341B20E4717774E0C41"
17             4               "0101000020ED080000B81E852BC555334186048C9EB1C21141"
22             7               "0101000020ED080000AE47E17A35F73341FE65F764723C0841"

我尝试了以下方法:

 select ST_Distance_Sphere ( ST_AsText(cast(v.house_location AS text)) , ST_AsText(cast(l.center AS text)))

来自 public.locations l，clean.vehicles v其中 l.obu_id=v.obu_id和 l.obu_id=3

但这对单个 obu_id 手动有效

我想用一个函数一次性完成。请给我一些关于如何执行此操作的想法。

谢谢。

Answer 1

house_loc:=(select house_location from table1 where obu_id=b.obu_id);

house_loc_geom := ST_Transform(house_loc,4269);
IF b.end_location_adj IS null THEN
    end_loc:= (select center from table2 where id=b.end_location and obu_id= b.obu_id);
else 
    end_loc:= (select center from tabl2 where id = b.end_location_adj and obu_id = b.obu_id);
end if;

center_geom:=ST_Transform(end_loc,4269);


UPDATE table 1 set dist_from_home_in_meter=distance 
where obu_id=b.obu_id and trip_id=b.trip_id and end_location=b.end_location;

这接近于回答，需要一些修改。