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

[Jo]

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.

Maybe indexes on the ST_Centroid functions would help?

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

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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130503/8eac13f1/attachment.html>