SHARE
TWEET

Untitled

a guest Dec 13th, 2016 57 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. query 1. intersects nuke site with mun
  2. query 2. buffers query 1 table
  3. query 3. creates a table of the aggregate receptors that fall  within each nuke buffer
  4.    
  5. def noury(buffer_table,buff_d,buffer_report):
  6.     import os
  7.     import sys
  8.     import psycopg2
  9.     conn = psycopg2.connect("dbname='nuclearingestion' user='postgres' host='localhost' password='sgsdg'") #connecting to DB
  10.     cur = conn.cursor()  #setting up connection cursor
  11.     cur.execute("create table nukesite as select site_name,county,mun,nuke.geom from nuke,mun where ST_INTERSECTS(nuke.geom,mun.geom);")
  12.  
  13.     cur.execute('''create table %s as
  14.     select site_name,county,mun, ST_UNION(ST_BUFFER(geom,%s)) as geom from nukesite group by site_name,county,mun;'''),(buffer_table,buff_d)
  15.  
  16.     cur.execute('''create table %s as
  17.     select n.site_name::text,n.county::text as county,n.mun::text as mun,coalesce(q1.schools,'0') as schools,coalesce(q2.childcares,'0') as childcares,coalesce(q3.hospitals,'0') as hospitals,
  18.     coalesce(q4.nursinghomes,'0') as nursinghom,coalesce(q5.infra,'0')as infra,coalesce(q6.streams,'0') as streams,coalesce(q7.streamsw,'0') as streamsw,coalesce(q8.rez,'0') as rez,n.geom as geom
  19.         from (select site_name,county,mun,geom from %s) as n
  20.             left join(select count(type_)::int::text as schools,site_name from %s,pr where st_intersects(%s.geom,pr.geom) and type_ = 'School' group by site_name) as q1
  21.                 on n.site_name = q1.site_name
  22.             left join(select count(type_)::int::text as childcares,site_name from %s,pr where st_intersects(%s.geom,pr.geom) and type_ = 'Childcare' group by site_name) as q2
  23.                 on n.site_name = q2.site_name
  24.             left join(select count(type_)::int::text as hospitals,site_name from %s,pr where st_intersects(%s.geom,pr.geom) and type_ = 'Hospital' group by site_name) as q3
  25.                 on n.site_name = q3.site_name
  26.             left join(select count(type_)::int::text as nursinghomes,site_name from %s,pr where st_intersects(%s.geom,pr.geom) and type_ = 'Nursinghome' group by site_name) as q4
  27.                 on n.site_name = q4.site_name
  28.             left join(select count(type_)::int::text as infra,site_name from %s,pr where st_intersects(%s.geom,pr.geom) and type_ = 'CriticalInfrastructure' group by site_name) as q5
  29.                 on n.site_name = q5.site_name
  30.             left join(select round(sum(st_length(streams.geom))/5280)::float::text as streams,site_name from %s,streams where st_intersects(%s.geom,streams.geom) group by site_name) as q6
  31.                 on n.site_name = q6.site_name
  32.             left join(select round(sum(st_length(streamsw.geom))/5280)::float::text as streamsw,site_name from %s,streamsw where st_intersects(%s.geom,streamsw.geom) group by site_name) as q7
  33.                 on n.site_name = q7.site_name
  34.             left join(select round(sum(popest*((ST_Area(rez.geom)/43560)/acresnew)))::int::text as rez,site_name from %s,rez where st_intersects(%s.geom,rez.geom) group by site_name) as q8
  35.                 on n.site_name = q8.site_name'''),(buffer_report,buffer_table)
  36.     conn.commit()
  37.  
  38. print noury("nuke10",52800,"nuke10report")
  39.    
  40. Traceback (most recent call last):
  41.   File "C:UsersrzaghaDesktopNukeIngestion.py", line 33, in <module>
  42.     print noury("nuke10",52800,"nuke10report")
  43.   File "C:UsersrzaghaDesktopNukeIngestion.py", line 9, in noury
  44.     cur.execute('''create table %s as select site_name,county,mun, ST_UNION(ST_BUFFER(geom,%s)) as geom from nukesite group by site_name,county,mun;'''),(buffer_table,buff_d)
  45. ProgrammingError: syntax error at or near "%"
  46. LINE 1: create table %s as select site_name,county,mun, ST_UNION(ST_...
  47.                      ^
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top