Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import psycopg2
- FROM_ST_DICT = {}
- TO_ST_DICT = {}
- CONN = psycopg2.connect("dbname='test' user='postgres' host='localhost' password='pg*admin'")
- print 'successfully connected to the database'
- cur = CONN.cursor()
- cur.execute('SELECT * FROM sf_citylots_unique')
- for row in cur:
- GEOM = row[4]
- FROM_ADDRESS = row[0] + ' ' + row[2] + ' ' + row[3]
- TO_ADDRESS = row[1] + ' ' + row[2] + ' ' + row[3]
- if GEOM in FROM_ST_DICT:
- if FROM_ADDRESS not in FROM_ST_DICT[GEOM]:
- # append the new number to the existing array at this slot
- FROM_ST_DICT[GEOM].append(FROM_ADDRESS)
- else:
- # create a new array in this slot
- FROM_ST_DICT[GEOM] = [FROM_ADDRESS]
- if GEOM in TO_ST_DICT:
- if TO_ADDRESS not in TO_ST_DICT[GEOM]:
- TO_ST_DICT[GEOM].append(TO_ADDRESS)
- else:
- TO_ST_DICT[GEOM] = [TO_ADDRESS]
- print 'adding FROM address data...'
- for f in FROM_ST_DICT:
- cur.execute('UPDATE sf_citylots_unique set from_address = '+"'"+
- ','.join(FROM_ST_DICT[f])+ "'" + ' WHERE geom ='+"'" +f+"'")
- print 'adding TO address data...'
- for t in TO_ST_DICT:
- cur.execute('UPDATE sf_citylots_unique set to_address = ' + "'" +
- ','.join(TO_ST_DICT[t]) + "'" + ' WHERE geom =' + "'" + t + "'")
- cur.execute("""select distinct a.from_address, a.to_address, a.geom into sf_citylots_unique_geom_with_addresses
- from sf_citylots_unique a""")
- CONN.commit()
- CONN.close()
Add Comment
Please, Sign In to add comment