在 UPDATE/SET 语句中取消嵌套,PostgreSQL/PHP

问题:在 UPDATE/SET 语句中取消嵌套,PostgreSQL/PHP 我有一个如下表: point_path_id line_path_id season_id gps_time heading roll pitch geometry 1 ___ ___ ___ ___ ___ ___ PostGISGeom 2 ___ ___ ___ ___ ___ ___ PostGISGeom 3

Postgredaxiang

13人浏览 · 2022-09-27 11:59:15

Postgredaxiang · 2022-09-27 11:59:15 发布

问题:在 UPDATE/SET 语句中取消嵌套,PostgreSQL/PHP

我有一个如下表:

point_path_id     line_path_id     season_id     gps_time     heading     roll     pitch       geometry
    1                 ___             ___           ___         ___        ___      ___       PostGISGeom
    2                 ___             ___           ___         ___        ___      ___       PostGISGeom
    3                 ___             ___           ___         ___        ___      ___       PostGISGeom
    4                 ___             ___           ___         ___        ___      ___       PostGISGeom  
    5                 ___             ___           ___         ___        ___      ___       PostGISGeom

在 PHP 中,我还拥有要更新的 ID 列表以及 line_path_id 和 season_id 的值。我也有用于 gps_time、heading、roll、pitch 的数组。

我有以下 SQL 语句(来自 PHP 的回显)

UPDATE greenland.point_paths 

SET 

line_path_id=1,
season_id=2, 
gps_time=unnest(array[1303475178.0031,1303475178.0081,1303475179.0081,1303475180.0081,1303475181.0081]::double precision[]),
heading=unnest(array[-2.0819464,-2.0819407,-2.0820324,-2.08202,-2.0819855]::double precision[]),
roll=unnest(array[-0.007395,-0.007395,-0.0073832,-0.0073949,-0.0073853]::double precision[]),
pitch=unnest(array[-0.0246114,-0.0246115,-0.0246108,-0.024582,-0.0245905]::double precision[]) 

WHERE point_path_id IN (1,2,3,4,5);

这是插入,但不正确。数组的第一个值被插入到 point_path_id 中的所有 5 个 id。我在表中得到以下信息:

在此处输入图像描述

这种取消嵌套在许多其他语句中都可以正常工作,但我似乎无法让它以这种形式正常工作。我的错误在哪里?

以下是在此之前发生的事情的一些背景:通过 URLEAD(MATLAB) 发送 JSON 数据字符串,并通过 PHP 解码/插入到许多表中。

插入一个 PostGIS 线串 GEOM 并返回它的 ID (line_path_id) 插入一个季节并返回它的 ID (season_id)

然后将线串转储到上述表中的点中,并自动生成并返回串行点_path_id。

然后需要插入与每个点相关的数据(这就是我想要做的)

我有线_path_id,season_id,每个点的gps_time/heading/roll/pitch。时间/航向/滚动/俯仰在数组中是点数的长度。

可以有 100,000+ 点,但让我们用 5 来测试。

转储点后,此处再次显示一个表格:

point_path_id     line_path_id     season_id     gps_time     heading     roll     pitch       geometry
    1                 ___             ___           ___         ___        ___      ___       PostGISGeom
    2                 ___             ___           ___         ___        ___      ___       PostGISGeom
    3                 ___             ___           ___         ___        ___      ___       PostGISGeom
    4                 ___             ___           ___         ___        ___      ___       PostGISGeom  
    5                 ___             ___           ___         ___        ___      ___       PostGISGeom

我还有两个变量: $line_path_id u003d # $season_id u003d #

和 4 个数组 $gps_time u003d [#,#,#,#,#];航向...滚动...俯仰...

我需要为每个点几何插入相关值。这是目标。

希望这有助于找到最佳解决方案。

解答

unnest() 是一个集合返回函数。您的问题是 UPDATE 语句中的每个 SET 分配都在寻找一个值来分配给 WHERE 子句找到的每一行的字段。 UPDATE 不知道您打算将第一个数组值转到 point_path_idu003d1 的行,依此类推。

对于小型数组,您可以将查询重写为:

UPDATE greenland.point_paths 
   SET line_path_id=1,
       season_id=2, 
       gps_time=(array[1303475178.0031,1303475178.0081,1303475179.0081,1303475180.0081,1303475181.0081])[point_path_id]::double precision[],
       heading=(array[-2.0819464,-2.0819407,-2.0820324,-2.08202,-2.0819855])[point_path_id]::double precision[],
       roll=(array[-0.007395,-0.007395,-0.0073832,-0.0073949,-0.0073853])[point_path_id]::double precision[],
       pitch=(array[-0.0246114,-0.0246115,-0.0246108,-0.024582,-0.0245905])[point_path_id]::double precision[]
 WHERE point_path_id IN (1,2,3,4,5);

虽然这依赖于与数组索引匹配的 point_path_id。

您可能需要一种不同的方法来将此数据输入系统。如果您希望在此表中一次更新 100,000 行,也许您应该使用 COPY 将数据批量上传到具有 point_path_id、season_id、line_path、gps\ 的临时表中_time、roll、pitch、heading 每个作为每行的单个值。然后你可以这样做:

UPDATE greenland.points_path pp
   SET season_id=s.season_id,
       line_path=s.line_path,
       gps_time=s.gps_time,
       roll=s.roll,
       pitch=s.pitch,
       heading=s.heading
  FROM staging s
 WHERE pp.point_path_id=s.point_path_id