两个表之间最近点的唯一赋值

Question

在我的Postgres 9.5数据库中，安装了PostGis 2.2.0，我有两个带有几何数据( points )的表，我希望将一个表中的点分配给另一个表中的各个点，但我不希望为buildings.gid分配两次。一旦分配了一个buildings.gid，就不应该将它分配给另一个pvanlagen.buildid。

表定义

buildings

CREATE TABLE public.buildings (
  gid numeric NOT NULL DEFAULT nextval('buildings_gid_seq'::regclass),
  osm_id character varying(11),
  name character varying(48),
  type character varying(16),
  geom geometry(MultiPolygon,4326),
  centroid geometry(Point,4326),
  gembez character varying(50),
  gemname character varying(50),
  krsbez character varying(50),
  krsname character varying(50),
  pv boolean,
  gr numeric,
  capac numeric,
  instdate date,
  pvid numeric,
  dist numeric,
  CONSTRAINT buildings_pkey PRIMARY KEY (gid)
);

CREATE INDEX build_centroid_gix
  ON public.buildings
  USING gist
  (st_transform(centroid, 31467));

CREATE INDEX buildings_geom_idx
  ON public.buildings
  USING gist
  (geom);

pvanlagen

CREATE TABLE public.pvanlagen (
  gid integer NOT NULL DEFAULT nextval('pv_bis2010_bayern_wgs84_gid_seq'::regclass),
  tso character varying(254),
  tso_number numeric(10,0),
  system_ope character varying(254),
  system_key character varying(254),
  location character varying(254),
  postal_cod numeric(10,0),
  street character varying(254),
  capacity numeric,
  voltage_le character varying(254),
  energy_sou character varying(254),
  beginning_ date,
  end_operat character varying(254),
  id numeric(10,0),
  kkz numeric(10,0),
  geom geometry(Point,4326),
  gembez character varying(50),
  gemname character varying(50),
  krsbez character varying(50),
  krsname character varying(50),
  buildid numeric,
  dist numeric,
  trans boolean,
  CONSTRAINT pv_bis2010_bayern_wgs84_pkey PRIMARY KEY (gid),
  CONSTRAINT pvanlagen_buildid_fkey FOREIGN KEY (buildid)
      REFERENCES public.buildings (gid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT pvanlagen_buildid_uni UNIQUE (buildid)
);

CREATE INDEX pv_bis2010_bayern_wgs84_geom_idx
  ON public.pvanlagen
  USING gist
  (geom);

查询

我的想法是在boolean表中添加一个pv列，这是在分配buildings.gid时设置的：

UPDATE pvanlagen 
SET buildid=buildings.gid, dist='50'
FROM buildings
WHERE buildid IS NULL 
AND buildings.pv is NULL
AND pvanlagen.gemname=buildings.gemname 
AND ST_Distance(ST_Transform(pvanlagen.geom,31467)
               ,ST_Transform(buildings.centroid,31467))<50;

UPDATE buildings 
SET pv=true
FROM pvanlagen
WHERE buildings.gid=pvanlagen.buildid;

我在buildings中测试了50行，但是所有这些行的应用时间太长了。我有3.200.000建筑物和260.000 PV。

最近的建筑物的gid应指定。如果是联系，那么分配给哪个gid就不重要了。如果我们需要制定一个规则，我们可以使用较低的gid。

50米是用来限制工作的。我使用ST_Distance()，因为它返回最小距离，应该在50米以内。后来我把它提高了好几次，直到分配了所有的光伏电池。

建筑物和光伏被分配到各自的区域(gemname)。这将使任务更便宜，因为我知道最近的建筑必须在同一区域(gemname)内。

在以下反馈之后，我尝试了这个查询：

UPDATE pvanlagen p1
SET    buildid = buildings.gid
 , dist = buildings.dist  
FROM (
   SELECT DISTINCT ON (b.gid)
          p.id, b.gid, b.dist::numeric  
   FROM  (
      SELECT id, ST_Transform(geom, 31467) 
      FROM   pvanlagen
      WHERE  buildid IS NULL  -- not assigned yet
      ) p
        , LATERAL (
      SELECT b.gid, ST_Distance(ST_Transform(p1.geom, 31467), ST_Transform(b.centroid, 31467)) AS dist
      FROM   buildings      b
      LEFT   JOIN pvanlagen p1 ON p1.buildid = b.gid  
      WHERE  p1.buildid IS NULL                        
      AND    b.gemname = p1.gemname
      ORDER  BY ST_Transform(p1.geom, 31467) <-> ST_Transform(b.centroid, 31467)
      LIMIT  1
            ) b
       ORDER  BY b.gid, b.dist, p.id  -- tie breaker
       ) x, buildings
 WHERE   p1.id = x.id;

但是它会返回0 rows affected in 234 ms execution time。

我哪里出问题了？

Answer 1

表模式

要执行您的规则，只需声明pvanlagen.buildid UNIQUE

ALTER TABLE pvanlagen ADD CONSTRAINT pvanlagen_buildid_uni UNIQUE (buildid);

building.gid是PK，正如您的更新所揭示的。若要执行引用完整性，请将 constraint添加到buildings.gid。

到目前为止，您已经实现了这两个目标。但是，在添加这些约束之前，运行下面的大型UPDATE会更有效。

您的表定义中还有很多需要改进的地方。首先，buildings.gid和pvanlagen.buildid应该是integer类型(如果消耗了大量PK值，也可能是bigint )。numeric是昂贵的胡说八道。

让我们关注核心问题：

查找最近的建筑物的基本查询

情况并不像看上去那么简单。这是一个"nearest neighbour"问题，伴随着独特的赋值的额外复杂性。

此查询为每个PV (pvanlagen中PV Anlage - row的缩写)找到最近的一栋楼，其中两个都没有分配，但：

SELECT pv_gid, b_gid, dist
FROM  (
   SELECT gid AS pv_gid, ST_Transform(geom, 31467) AS geom31467
   FROM   pvanlagen
   WHERE  buildid IS NULL  -- not assigned yet
   ) p
     , LATERAL (
   SELECT b.gid AS b_gid
        , round(ST_Distance(p.geom31467
                      , ST_Transform(b.centroid, 31467))::numeric, 2) AS dist  -- see below
   FROM   buildings b
   LEFT   JOIN pvanlagen p1 ON p1.buildid = b.gid  -- also not assigned ...
   WHERE  p1.buildid IS NULL                       -- ... yet  
   -- AND    p.gemname = b.gemname                 -- not needed for performance, see below
   ORDER  BY p.geom31467 <-> ST_Transform(b.centroid, 31467)
   LIMIT  1
   ) b;

为了加快查询速度，需要-- buildings上的一个空间的、功能性的GiST索引--使其速度快得多：

CREATE INDEX build_centroid_gix ON buildings USING gist (ST_Transform(centroid, 31467));

不知道你为什么不

有更多解释的相关答案：

• Spatial query on large table with multiple self joins performing slow
• How do I query all rows within a 5-mile radius of my coordinates?

进一步读：

• http://workshops.boundlessgeo.com/postgis-intro/knn.html
• http://www.postgresonline.com/journal/archives/306-KNN-GIST-with-a-Lateral-twist-Coming-soon-to-a-database-near-you.html

有了索引之后，为了性能，我们不需要将匹配限制在相同的gemname上。只有当这是实际执行的规则时才这么做。如果必须在任何时候都遵守它，请将列包含在FK约束中：

• Restrict foreign key relationship to rows of related subtypes

剩余问题

我们可以在UPDATE语句中使用上面的查询。每个PV只使用一次，但多个PV可能仍然会发现相同的构建是最近的。每栋楼只允许一个PV。那你怎么解决这个问题？

换句话说，您将如何在这里分配对象？

​

​

简单解

一个简单的解决办法是：

UPDATE pvanlagen p1
SET    buildid = sub.b_gid
     , dist    = sub.dist  -- actual distance
FROM  (
   SELECT DISTINCT ON (b_gid)
          pv_gid, b_gid, dist
   FROM  (
      SELECT gid AS pv_gid, ST_Transform(geom, 31467) AS geom31467
      FROM   pvanlagen
      WHERE  buildid IS NULL  -- not assigned yet
      ) p
        , LATERAL (
      SELECT b.gid AS b_gid
           , round(ST_Distance(p.geom31467
                         , ST_Transform(b.centroid, 31467))::numeric, 2) AS dist  -- see below
      FROM   buildings      b
      LEFT   JOIN pvanlagen p1 ON p1.buildid = b.gid  -- also not assigned ...
      WHERE  p1.buildid IS NULL                       -- ... yet  
      -- AND    p.gemname = b.gemname                 -- not needed for performance, see below
      ORDER  BY p.geom31467 <-> ST_Transform(b.centroid, 31467)
      LIMIT  1
      ) b
   ORDER  BY b_gid, dist, pv_gid  -- tie breaker
   ) sub
WHERE   p1.gid = sub.pv_gid;

我使用DISTINCT ON (b_gid)将每栋建筑精确地减少到一行，选择最短距离的PV。详细信息：

• Select first row in each GROUP BY group?

对于任何最接近于多个PV的建筑物，只分配最接近的PV。PK列gid (别名pv_gid)作为平局者，如果两者是相同接近的。在这种情况下，一些PV被从更新中删除并保持未分配。重复查询，直到分配所有PV为止。

--尽管这仍然是一个简单的算法。从上面的图表来看，这分配了建筑4到PV 4和建筑5到PV 5，而4-5和5-4可能是一个更好的解决方案.

旁白：dist列的类型

目前您使用的是numeric。您的原始查询分配了一个常量integer，在numeric中没有意义。

在我的新查询中，ST_Distance()作为double precision返回实际距离(以米为单位)。如果我们简单地赋值，我们在numeric数据类型中得到大约15个小数位数，而这个数字并不是那么精确。我很怀疑你是否想浪费储藏室。

我宁愿从计算中保存原始的double precision。或者，，更好的是，按需要循环。如果仪表足够精确，只需将其转换为并保存一个integer (自动舍入该数字)。或先用100乘以节省厘米：

(ST_Distance(...) * 100)::int