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

[Alexandre Neto]

Hi!

Thanks for the hint, I will try to use it and to understand what it is
doing.

Alexandre Neto

On Fri, May 3, 2013 at 11:32 AM, Hugues François
<hugues.francois at irstea.fr>wrote:

> **
> 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)
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130503/3a6017e2/attachment.html>