[postgis-users] Referencing two tables in CREATE TABLE AS calculation

[Paul Ramsey]

You have no join condition between your two tables, so the resultant
table is going to be the cartesian product of the two tables
(nrows(table1) * nrows(table2)). If the tables are big enough, that
alone can take a very long time.

P.

On Thu, Jun 23, 2011 at 5:36 PM, Conor Henley <conor at calthorpe.com> wrote:
> Hi all,
>
>
>
> I’m encountering an incredibly slow run time when I attempt to create a new
> table (CREATE TABLE AS) with fields populated by calculations which
> reference two other tables.  Does referencing multiple tables in a
> calculation like this usually result in a slower run time?
>
>
>
> Here is a portion of my script:
>
>
>
> CREATE TABLE public.sac_parcel_emp_unemp_calc_062311 AS
>
>
>
>                 SELECT
>
>                 a.id_parcel, a.vc_pop, a.pop_age16_up,
>
>
>
>                 CASE
>
>                 WHEN b.p043001 > 0
>
>                 THEN (a.pop_age16_up * (cast(b.p043004 as float) + b.p043006
> + b.p043011 + b.p043013) / b.p043001)
>
>                 ELSE
> 0
>
>                 END as pop_employed
>
>
>
> FROM public.sac_parcel_age16_up as a, public.sac_parcel_block_bg_test_062011
> as b;
>
>
>
> I also cast one of the variables in the calculation as a float so that the
> output field would be the same and not populated with zeros.
>
> Thanks for any help.
>
>
>
> Conor Henley
>
> GIS/Regional Planning Intern
>
>
>
> C A L T H O R P E  A S S O C I A T E S
>
> 2095 ROSE STREET, SUITE 201, BERKELEY, CALIFORNIA, 94709 USA
> 510-548-6800 | 510 548-6848 (fax)
>
> conor at calthorpe.com | www.calthorpe.com
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>