text: import zipfile
  import sqlalchemy as sa
  import geoalchemy2 as ga2
  import shapefile
  from shapely.geometry import MultiPolygon, asShape
  import csv
  from geocoder.app_config import DB_USER, DB_PW, DB_HOST, \
      DB_PORT, DB_NAME
  import psycopg2
  
  DB_CONN_STR = 'host={0} dbname={1} user={2} port={3}'\
      .format(DB_HOST, DB_NAME, DB_USER, DB_PORT)
  
  download_url = 'https://data.cityofchicago.org/api/geospatial/qv97-3bvb?method=export&format=Original'
  
  def loadFootprints(path):
  
      with zipfile.ZipFile(path, 'r') as zf:
          for fname in zf.namelist():
              if fname.endswith('.shp'):
                  zf.extract(fname, path='downloads')
                  shp = open('downloads/%s' % fname, 'rb')
              if fname.endswith('.dbf'):
                  zf.extract(fname, path='downloads')
                  dbf = open('downloads/%s' % fname, 'rb')
              if fname.endswith('.shx'):
                  zf.extract(fname, path='downloads')
                  shx = open('downloads/%s' % fname, 'rb')
          
      shape_reader = shapefile.Reader(shp=shp, dbf=dbf, shx=shx)
  
      fields = shape_reader.fields[1:]
      
      GEO_TYPE_MAP = {
          'C': sa.String,
          'N': sa.Float,
          'L': sa.Float,
          'D': sa.TIMESTAMP,
          'F': sa.Float
      }
  
      columns = []
      for field in fields:
          fname, d_type, f_len, d_len = field
          col_type = GEO_TYPE_MAP[d_type]
          kwargs = {}
          
          if d_type == 'C':
              col_type = col_type(f_len)
          if fname == 'objectid':
              kwargs['primary_key'] = True
  
          columns.append(sa.Column(fname.lower(), col_type, **kwargs))
  
      geo_type = 'MULTIPOLYGON'
      columns.append(sa.Column('geom', ga2.Geometry(geo_type, srid=3435)))
  
      engine = sa.create_engine('postgresql://localhost:5432/geocoder', 
                             convert_unicode=True, 
                             server_side_cursors=True)
  
      table = sa.Table('temp_building_footprints', sa.MetaData(), *columns)
      
      table.drop(engine, checkfirst=True)
      table.create(engine)
  
      ins = table.insert()
      shp_count = 0
      values = []
      
      records = shape_reader.iterShapeRecords()
      
      with open('geocoder/data/building_footprints.csv', 'w') as f:
          writer = csv.DictWriter(f, table.columns.keys())
          writer.writeheader()
  
          for record in records:
              d = {}
              for k,v in zip(table.columns.keys(), record.record):
                  try:
                      d[k] = v.decode('latin-1').replace(' ', '')
                  except AttributeError:
                      d[k] = v
              try:
                  geom = asShape(record.shape.__geo_interface__)
              except AttributeError as e:
                  continue
              geom = MultiPolygon([geom])
              d['geom'] = 'SRID=3435;%s' % geom.wkt
              writer.writerow(d)
      
      copy_st = ''' 
          COPY temp_building_footprints 
          FROM STDIN 
          WITH (FORMAT CSV, HEADER TRUE, DELIMITER ',')  
      '''
  
      with open('geocoder/data/building_footprints.csv', 'r') as f:
          conn = engine.raw_connection()
  
          try:
              cursor = conn.cursor()
              cursor.copy_expert(copy_st, f)
              conn.commit()
          except psycopg2.ProgrammingError as e:
              conn.rollback()
              raise e
          
          conn.close()
  
      engine.dispose()
  
  def cleanupTable():
  
      create = ''' 
          CREATE TABLE building_footprints AS (
              SELECT 
                objectid::int AS id,
                bldg_id::int AS building_id,
                bldg_statu AS building_status,
                f_add1::int AS from_address,
                t_add1::int AS to_address,
                pre_dir1 AS street_direction,
                st_name1 AS street_name,
                st_type1 AS street_type,
                unit_name,
                non_standa AS non_standard,
                bldg_name1 AS building_name_1,
                bldg_name2 AS building_name_2,
                comments,
                stories::int,
                orig_bldg_::int AS original_building_id,
                footprint_ AS footprint_source,
                create_use AS create_user,
                bldg_creat AS building_creation_date,
                bldg_activ AS building_active_date,
                bldg_end_d AS building_end_date,
                demolished AS demolished_date,
                edit_date,
                edit_useri AS edit_user_id,
                edit_sourc AS edit_source,
                qc_date,
                qc_userid,
                qc_source,
                x_coord AS x_coordinate,
                y_coord AS y_coordinate,
                harris_str,
                no_of_unit::int AS number_of_units,
                no_stories::int AS number_of_stories,
                year_built::int AS year_built,
                bldg_sq_fo::int AS building_sqaure_footage,
                bldg_condi AS building_condition,
                condition_ AS condition_date,
                vacancy_st AS vacancy_status,
                label_hous AS label_house,
                suf_dir1 AS street_suffix,
                shape_area,
                shape_len,
                NULL::VARCHAR AS complete_address,
                NULL::VARCHAR AS address_id,
                ST_Transform(geom, 4326) AS geom
              FROM temp_building_footprints
          )
      '''
  
      engine = sa.create_engine('postgresql://localhost:5432/geocoder', 
                             convert_unicode=True, 
                             server_side_cursors=True)
      
      with engine.begin() as conn:
          conn.execute('DROP TABLE IF EXISTS building_footprints')
          conn.execute(create)
  
      with engine.begin() as conn:
          conn.execute("SELECT UpdateGeometrySRID('building_footprints', 'geom', 4326)")
          conn.execute("CREATE INDEX footprint_geom_idx ON building_footprints USING GIST (geom)")
  
      add_complete = ''' 
          UPDATE building_footprints SET
            complete_address = subq.complete_address
          FROM (
              SELECT
                (COALESCE(from_address::varchar, '') || ' ' || 
                 COALESCE(street_direction, '') || ' ' ||
                 COALESCE(street_name, '') || ' ' ||
                 COALESCE(street_type, '') || ' ' ||
                 'Chicago, IL'
                ) AS complete_address,
                id
              FROM building_footprints
              WHERE from_address = to_address
                AND from_address != 0
          ) AS subq 
          WHERE building_footprints.id = subq.id
      '''
      
      with engine.begin() as conn:
          conn.execute(add_complete)
  
      add_complete = ''' 
          UPDATE building_footprints SET
            complete_address = subq.complete_address
          FROM (
              SELECT
                (COALESCE(from_address::varchar, '') || '-' || 
                 right(COALESCE(to_address::varchar, ''), 2) || ' ' ||
                 COALESCE(street_direction, '') || ' ' ||
                 COALESCE(street_name, '') || ' ' ||
                 COALESCE(street_type, '') || ' ' ||
                 'Chicago, IL'
                ) AS complete_address,
                id
              FROM building_footprints
              WHERE from_address != to_address
                AND from_address != 0
          ) AS subq 
          WHERE building_footprints.id = subq.id
      '''
      
      with engine.begin() as conn:
          conn.execute(add_complete)
  
      add_pk = ''' 
          ALTER TABLE building_footprints ADD PRIMARY KEY (id)
      '''
  
      with engine.begin() as conn:
          conn.execute(add_pk)
  
  
  if __name__ == "__main__":
      # loadFootprints('downloads/chicago_building_footprints.zip')
      cleanupTable()
  