两个表之间最近点的唯一分配

In my Postgres 9.5数据库与邮政地理信息系统2.2.0安装后，我有两个带有几何数据（点）的表，我想将一个表中的点分配给另一个表中的点，但我不想要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);

Query

我的想法是添加一个boolean column pv in the buildings表，当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;

我测试了 50 行buildings但申请所有这些都需要很长时间。我有3,200,000 栋建筑物 and 260.000 光伏.

The gid应指定最近的建筑物。如果在平局的情况下，哪个都不重要gid被安排了。如果我们需要制定一个规则，我们可以选择较低的建筑物gid.

50米本来就是一个限制。我用了ST_Distance()因为它返回最小距离，应在 50 米以内。后来我多次提出，直到每个PV Anlage都被分配。

建筑物和光伏被分配到各自的区域（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.
我哪里出错了？

表架构

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

ALTER TABLE pvanlagen ADD CONSTRAINT pvanlagen_buildid_uni UNIQUE (buildid);

building.gid是PK，正如你的更新所揭示的那样。为了还强制引用完整性，请添加FOREIGN KEY约束 to buildings.gid.

到目前为止，您已经实现了两者。但运营大公司效率会更高UPDATE below before你添加这些约束。

表定义中还有很多需要改进的地方。其一，buildings.gid也pvanlagen.buildid应该是类型integer（或者可能bigint如果你烧伤a lotPK 值）。numeric是昂贵的废话。

让我们关注核心问题：

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

案件并不像看上去那么简单。它是“最近的邻居”问题，以及唯一分配的额外复杂性。

该查询找到最近的one为每个 PV 构建（PV Anlage 的缩写 - 排入pvanlagen)，其中两者均未分配，但是：

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;

为了使此查询更快，您need空间、功能 GiST 索引buildings做到这一点much faster:

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

没有把握why你不

相关答案及更多解释：

• 对具有多个自联接的大型表执行缓慢的空间查询
• 如何查询坐标 5 英里半径内的所有行？

进一步阅读：

• 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 约束中：

• 将外键关系限制为相关子类型的行

遗留问题

我们可以使用上面的查询UPDATE陈述。每个PV仅使用一次，但多个PV仍可能找到同一栋楼最接近。你只允许one每栋建筑的光伏发电。那么你会如何解决这个问题呢？

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

简单的解决方案

一种简单的解决方案是：

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;

I use DISTINCT ON (b_gid)精确地减少到one每建筑物排，选择距离最短的 PV。细节：

• 选择每个 GROUP BY 组中的第一行？

对于任何距离多个 PV 最近的建筑物，仅分配最近的 PV。 PK专栏gid (alias pv_gid) 如果两个人同样接近，则充当决胜局。在这种情况下，一些 PV 会从更新中删除并保留未分配的. Repeat查询直到所有PV都被分配。

这仍然是一个简单的算法， 尽管。看看我上面的图表，这将建筑物 4 分配给 PV 4，将建筑物 5 分配给 PV 5，而 4-5 和 5-4 可能是一个更好的整体解决方案...

旁边：输入dist column

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

在我的新查询中ST_Distance()返回以米为单位的实际距离：double precision。如果我们简单地赋值，我们会得到 15 个左右的小数位numeric数据类型，并且数字不是that确切地说。我严重怀疑你想浪费存储空间。

我宁愿保存原来的double precision从计算来看。或者，更好的是，根据需要舍入。如果仪表足够精确，只需投射并保存integer（自动舍入数字）。或者先乘以 100 以节省厘米：

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