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

Conor Henley conor at calthorpe.com
Thu Jun 23 17:36:06 PDT 2011


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 <mailto:sheara at calthorpe.com>  | www.calthorpe.com

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110623/c93b3171/attachment.html>


More information about the postgis-users mailing list