Advertisement
Guest User

Untitled

a guest
Oct 18th, 2017
126
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.29 KB | None | 0 0
  1. import os.path
  2. import psycopg2
  3. import osgeo.ogr
  4. #connect to my postGIS database
  5. connection = psycopg2.connect("dbname='test_geospatial_database' user='postgres' host='localhost' password='mars1993'")
  6. cursor = connection.cursor()
  7. cursor.execute("DROP TABLE IF EXISTS canterbury")
  8. #create empty table
  9. cursor.execute("""
  10. CREATE TABLE canterbury (
  11. id SERIAL,
  12. OBJECTID VARCHAR(255),
  13. SCHEME VARCHAR(255),
  14. ROCK_HEIGH VARCHAR(255),
  15. LENGTH_M VARCHAR(255),
  16. SOURCECODE VARCHAR(255),
  17. YEAR_BUILT VARCHAR(255),
  18. PRIMARY KEY (id))
  19. """)
  20. #add geometry field
  21. cursor.execute("""
  22. SELECT AddGeometryColumn('canterbury', 'geom',
  23. 2193, 'GEOMETRY', 2)
  24. """)
  25. #add index
  26. cursor.execute("""
  27. CREATE INDEX canterburyIndex ON canterbury
  28. USING GIST(geom)
  29. """)
  30. connection.commit()
  31. #reload the connection
  32. connection = psycopg2.connect("dbname='test_geospatial_database' user='postgres' host='localhost' password='mars1993'")
  33. cursor = connection.cursor()
  34. cursor.execute("DELETE FROM canterbury")
  35. #target shapefile
  36. srcFile = os.path.join("/home/marcus/Desktop/GIS data/Canterbury/Canterbury.shp")
  37. shapefile = osgeo.ogr.Open(srcFile)
  38. layer = shapefile.GetLayer(0)
  39. #convert NULL values to Unknown
  40. for i in range(layer.GetFeatureCount()):
  41. feature = layer.GetFeature(i)
  42. if feature.GetField("OBJECTID") is None:
  43. objectid = "Unknown"
  44. else:
  45. objectid = feature.GetField("OBJECTID")
  46. objectid = str(objectid)
  47. objectid = objectid.decode("Latin-1")
  48. wkt = feature.GetGeometryRef().ExportToWkt()
  49. #insert into database
  50. cursor.execute("INSERT INTO canterbury (OBJECTID, geom) " +"VALUES (%s, ST_GeometryFromText(%s, " +"2193))", (objectid.encode("utf8"), wkt))
  51. #repeat above process for next field
  52. for i in range(layer.GetFeatureCount()):
  53. feature = layer.GetFeature(i)
  54. if feature.GetField("SCHEME") is None:
  55. scheme = "UNNAMED"
  56. else:
  57. scheme = feature.GetField("SCHEME")
  58. scheme = str(scheme)
  59. scheme = scheme.decode("Latin-1")
  60. wkt = feature.GetGeometryRef().ExportToWkt()
  61. cursor.execute("INSERT INTO canterbury (SCHEME, geom) " +"VALUES (%s, ST_GeometryFromText(%s, " +"2193))", (scheme.encode("utf8"), wkt))
  62. connection.commit()
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement