1

In my Postgres 9.5 database with PostGis 2.2.0 installed, I have a table buildings with geometric data (points) centroid. The table contains about 3 million buildings, of which about 300.000 contain special information.

Now, for each buildings.gid I want to know, how many other buildings of the same table are within a certain radius (I want to test this for different radiuses: 20meter, 50meter, 100m, 200m, 500m if it can be done in an adequat amount of time) and add this information to a column of buildings. The related columns are N20, N50,...

Query

I figured out to use something like:

UPDATE buildings
SET N50=sub.N
FROM    (SELECT Count(n.gid) AS N
         FROM   buildings n, buildings b
         WHERE  ST_DWithin(b.centroid, n.centroid, 50) -- distance in meter
         ) sub

Related to this solution of @ErwinBrandstetter, where there is a coordinate given, around with the radius is produced. But even when testing for only one gid, I did not recieve an result in an acceptable amount of time.

The difference to my problem is, that I want this to be done for every building.

Table definitions

CREATE TABLE public.buildings
(
  gid integer 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 double precision,
  instdate date,
  pvid integer,
  dist double precision,
  gemewz integer,
  n50 integer,
  n100 integer,
  n200 integer,
  n500 integer,
  n1000 integer,
  IBASE numeric,
  CONSTRAINT buildings_pkey PRIMARY KEY (gid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.buildings
  OWNER TO postgres;

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);

Advanced Problem

(The following might be another problem, hence should be another question on stackoverflow, but there might be the chance to implement this in the first question)

Furthermore, referring to the "special information", 268238 of the buildings contain information about dist,instdate,capac. These columns of the remaining buildings are NULL.

instdate is the date, at which a building had a "PV" installed. I need to transform the table buildings to a panel datatype table, which means that for each period (in my case 11 periods) exists one row for the same building.

Now I need to check, how many other buildings within the radius already had a "PV" installed. To do so, I want to query all buildings within a radius (like in first question) where for example capac IS NOT NULL, but now the buildings shall not be counted, but their information about dist,instdate,capac shall be added as a string to IBASE.

Community
  • 1
  • 1
NewbieNeedsHelp
  • 165
  • 1
  • 1
  • 9
  • One issue is that your geometries are in lat/long (EPSG:4326), but your distance queries are in metres. You either need to choose to use a `geography` type (or at least build a GiST index on that cast), or choose and reproject to a suitable Cartesian projection system (if everything fits nicely in one). As it is, the subquery does a non-sense count. – Mike T Jan 28 '16 at 20:13
  • UPDATE: I want to use the `centroid` POINTS in my query, for which I already use an Index `CREATE INDEX build_centroid_gix ON public.buildings USING gist (st_transform(centroid, 31467))`. – NewbieNeedsHelp Jan 29 '16 at 13:59

1 Answers1

1

Try building an index on a geography cast, which can be used for ST_DWithin (so you can calculated metric distances with geographic data)

CREATE INDEX buildings_geog_idx ON buildings USING gist (geom::geography);

UPDATE buildings SET n50=c.count
FROM (
  SELECT a.gid, count(b.gid)
  FROM buildings a
  LEFT JOIN buildings b ON ST_DWithin(a.geom::geography, b.geom::geography, 50.0)
      AND a.gid <> b.gid
  GROUP BY a.gid
) c
WHERE c.gid = buildings.gid;

You could also try calculating on a sphere for faster performance, but potential errors from spheroid distances:

ST_DWithin(a.geom::geography, b.geom::geography, 50.0, false)
Mike T
  • 41,085
  • 18
  • 152
  • 203
  • What if I use the `CREATE INDEX build_centroid_gix ON public.buildings USING gist (st_transform(centroid, 31467));`? Does the SRID 31467 support metric distances? Because I think the query is faster using the geometric 31467 SRID. Also I want to use the `Centroid` Points, not the `geom`.I updated this in the question above. – NewbieNeedsHelp Jan 29 '16 at 14:01