[postgis-users] How to improve query with repeated spatial joinquery

[Hugues François]

Hello,
 
I think over() can help you. You should try something like the query below :
 
select b.gid, volume / sum(volume) over(partition by p.gid) asvol_per, p.bgrill as bgrill, b.geom
from buildings as b, 
join stat_parcels as p on st_contains(p.geom, st_centroid(b.geom))
 
Hugues.
 

--

 

Hugues FRANÇOIS

 

________________________________

From: postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Alexandre Neto
Sent: Friday, May 03, 2013 12:25 PM
To: PostGIS Users Discussion
Subject: [postgis-users] How to improve query with repeated spatial joinquery


Hello all,


My goal is to calculate for each building it's volume percentage relatively to the total buildings volume inside the statistical parcel he is in.

I have written the SQL code below (that works), but I think it might be improved, since I see repeated proceedings in it. 

I would greatly appreciate if someone give me some advice about it.

Thanks,

Alexandre Neto

-----------

SELECT 
b.gid,
b.volume / t.total_volume as vol_per,
b.geom,
p.bgri11 as bgri11,
FROM
buildings as b,
stat_parcels as p,
(SELECT 
sum(b.volume) as total_volume,
p.bgri11 as bgri11
FROM
buildings as b,
stat_parcels as p
WHERE
ST_CONTAINS(p.geom, St_centroid(b.geom))
GROUP BY
p.bgri11) as t
WHERE
ST_CONTAINS(g.geom, St_centroid(f.geom))
AND p.bgri11 = t.bgri11;


My tables look like this: 

CREATE TABLE buildings
  gid serial NOT NULL,
  volume double precision,
  geom geometry(MultiPolygon,27493),
  CONSTRAINT edificios_habitacao_pkey PRIMARY KEY (gid);

CREATE TABLE stat_parcels
  gid serial NOT NULL,
  bgri11 character varying(11),
  geom geometry(MultiPolygon,27493),
  CONSTRAINT bgri11_cascais_pkey PRIMARY KEY (gid)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130503/51912ba3/attachment.html>