Unique assignment of nearest points between two tables

Question

In my Postgres 9.5 database with PostGis 2.2.0 installed , I have two tables with geometric data (points), and I want to assign points from one table to points from another table, but I do not want to buildings.gidbe assigned twice. Once one is buildings.gidassigned, it cannot be assigned to another pvanlagen.buildid.

Table definitions

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

My idea was to add a column boolean pvto the table buildings, which is set when it was assigned 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;

I tested 50 lines in buildings, but it takes too much time to apply them. I have 3.200.000 buildings and 260.000 PV .

gid . , gid . , gid.

50 . ST_Distance(), , 50 . , PV Anlage.

PV (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 - , . , FOREIGN KEY buildings.gid.

. UPDATE , .

. , buildings.gid, pvanlagen.buildid integer (, , bigint, PK). numeric - .

:

, . " " .

PV ( PV Anlage - row 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;

, GiST buildings, :

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

,

:

• ,
• 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 - . 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 . :

• GROUP BY?

, 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. , 15 numeric, . , .

double precision . , . , integer ( ). 100 cm:

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