Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import os.path
- import psycopg2
- import osgeo.ogr
- #connect to my postGIS database
- connection = psycopg2.connect("dbname='test_geospatial_database' user='postgres' host='localhost' password='mars1993'")
- cursor = connection.cursor()
- cursor.execute("DROP TABLE IF EXISTS canterbury")
- #create empty table
- cursor.execute("""
- CREATE TABLE canterbury (
- id SERIAL,
- OBJECTID VARCHAR(255),
- SCHEME VARCHAR(255),
- ROCK_HEIGH VARCHAR(255),
- LENGTH_M VARCHAR(255),
- SOURCECODE VARCHAR(255),
- YEAR_BUILT VARCHAR(255),
- PRIMARY KEY (id))
- """)
- #add geometry field
- cursor.execute("""
- SELECT AddGeometryColumn('canterbury', 'geom',
- 2193, 'GEOMETRY', 2)
- """)
- #add index
- cursor.execute("""
- CREATE INDEX canterburyIndex ON canterbury
- USING GIST(geom)
- """)
- connection.commit()
- #reload the connection
- connection = psycopg2.connect("dbname='test_geospatial_database' user='postgres' host='localhost' password='mars1993'")
- cursor = connection.cursor()
- cursor.execute("DELETE FROM canterbury")
- #target shapefile
- srcFile = os.path.join("/home/marcus/Desktop/GIS data/Canterbury/Canterbury.shp")
- shapefile = osgeo.ogr.Open(srcFile)
- layer = shapefile.GetLayer(0)
- #convert NULL values to Unknown
- for i in range(layer.GetFeatureCount()):
- feature = layer.GetFeature(i)
- if feature.GetField("OBJECTID") is None:
- objectid = "Unknown"
- else:
- objectid = feature.GetField("OBJECTID")
- objectid = str(objectid)
- objectid = objectid.decode("Latin-1")
- wkt = feature.GetGeometryRef().ExportToWkt()
- #insert into database
- cursor.execute("INSERT INTO canterbury (OBJECTID, geom) " +"VALUES (%s, ST_GeometryFromText(%s, " +"2193))", (objectid.encode("utf8"), wkt))
- #repeat above process for next field
- for i in range(layer.GetFeatureCount()):
- feature = layer.GetFeature(i)
- if feature.GetField("SCHEME") is None:
- scheme = "UNNAMED"
- else:
- scheme = feature.GetField("SCHEME")
- scheme = str(scheme)
- scheme = scheme.decode("Latin-1")
- wkt = feature.GetGeometryRef().ExportToWkt()
- cursor.execute("INSERT INTO canterbury (SCHEME, geom) " +"VALUES (%s, ST_GeometryFromText(%s, " +"2193))", (scheme.encode("utf8"), wkt))
- connection.commit()
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement