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

[Alexandre Neto]

Hugues, it worked like a charm!

I also checked this<http://www.postgresql.org/docs/9.2/static/tutorial-window.html>
to
understand what was it doing. And was exactly what I needed.

Like this, the nested loop was processed only once, and I saved around 33%
of the query time.

Thanks,

Alexandre Neto

On Fri, May 3, 2013 at 11:55 AM, Alexandre Neto <senhor.neto at gmail.com>wrote:

> 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/7950bbc5/attachment.html>