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

[Alexandre Neto]

Hi, thanks for your help.

On Fri, May 3, 2013 at 11:31 AM, Jo <winfixit at gmail.com> wrote:

> As far as I understand SQL, it's normal you repeat clauses. I'm pretty
> sure the optimizer takes care of that and they are not actually executed
> more than once.
>

Using EXPLAIN, it says that it will repeat the a Nested Loop to check the
ST_CONTAIN


>
> Maybe indexes on the ST_Centroid functions would help?
>

For that, do Ihave to "materialize" the point geometry in my table, and
create a index?


>
> Where do g.geom and f.geom come from?
>

My mistake, I tried to adapt the query a bit to make it more obvious here,
and forgot those two.  It should be ST_CONTAIN(p.geom, ST_CENTROID(b.geom))


> Jo
>
>
>
> 2013/5/3 Alexandre Neto <senhor.neto at gmail.com>
>
>> 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
>>
>>
>
> _______________________________________________
> 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/f51caccb/attachment.html>