Guest User

Untitled

a guest
Sep 1st, 2016
70
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.44 KB | None | 0 0
  1. import psycopg2
  2.  
  3. FROM_ST_DICT = {}
  4. TO_ST_DICT = {}
  5.  
  6. CONN = psycopg2.connect("dbname='test' user='postgres' host='localhost' password='pg*admin'")
  7. print 'successfully connected to the database'
  8.  
  9. cur = CONN.cursor()
  10. cur.execute('SELECT * FROM sf_citylots_unique')
  11. for row in cur:
  12. GEOM = row[4]
  13. FROM_ADDRESS = row[0] + ' ' + row[2] + ' ' + row[3]
  14. TO_ADDRESS = row[1] + ' ' + row[2] + ' ' + row[3]
  15.  
  16. if GEOM in FROM_ST_DICT:
  17. if FROM_ADDRESS not in FROM_ST_DICT[GEOM]:
  18. # append the new number to the existing array at this slot
  19. FROM_ST_DICT[GEOM].append(FROM_ADDRESS)
  20. else:
  21. # create a new array in this slot
  22. FROM_ST_DICT[GEOM] = [FROM_ADDRESS]
  23. if GEOM in TO_ST_DICT:
  24. if TO_ADDRESS not in TO_ST_DICT[GEOM]:
  25. TO_ST_DICT[GEOM].append(TO_ADDRESS)
  26. else:
  27. TO_ST_DICT[GEOM] = [TO_ADDRESS]
  28. print 'adding FROM address data...'
  29. for f in FROM_ST_DICT:
  30. cur.execute('UPDATE sf_citylots_unique set from_address = '+"'"+
  31. ','.join(FROM_ST_DICT[f])+ "'" + ' WHERE geom ='+"'" +f+"'")
  32. print 'adding TO address data...'
  33. for t in TO_ST_DICT:
  34. cur.execute('UPDATE sf_citylots_unique set to_address = ' + "'" +
  35. ','.join(TO_ST_DICT[t]) + "'" + ' WHERE geom =' + "'" + t + "'")
  36.  
  37. cur.execute("""select distinct a.from_address, a.to_address, a.geom into sf_citylots_unique_geom_with_addresses
  38. from sf_citylots_unique a""")
  39.  
  40. CONN.commit()
  41. CONN.close()
Add Comment
Please, Sign In to add comment