Last active
February 11, 2021 04:23
-
-
Save Sleepingwell/7445312 to your computer and use it in GitHub Desktop.
Example of using geoalchemy.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # A simple script to load data from a shapefile into a spatialite db. | |
| # Transfering the geometries is slow. It would probably be much faster to | |
| # use WKB directly for the load, but I cannot get this to work. | |
| # This is largely copied from | |
| # https://github.com/geoalchemy/geoalchemy/blob/master/examples/spatialite.py | |
| import os, osgeo.ogr | |
| from sqlite3 import dbapi2 as sqlite | |
| from sqlalchemy.orm import sessionmaker | |
| from sqlalchemy import event, create_engine, MetaData | |
| from sqlalchemy import Column, Integer, String | |
| from sqlalchemy.ext.declarative import declarative_base | |
| from geoalchemy import GeometryColumn, Geometry, GeometryDDL | |
| # put the spatialite dll on the path. If one had pyspatialite installed thn | |
| # this would not be required... but trying to get that on a windows machine | |
| # was way too hard to bother | |
| dirDLLspatialite = 'C:/bin' | |
| os.environ['PATH'] = dirDLLspatialite + ';' + os.environ['PATH'] | |
| engine = create_engine('sqlite:///D:\\temp\\test_1.db', module=sqlite, echo=False) | |
| # this enables the extension on each connection | |
| @event.listens_for(engine, "connect") | |
| def connect(dbapi_connection, connection_rec): | |
| dbapi_connection.enable_load_extension(True) | |
| dbapi_connection.execute("SELECT load_extension('libspatialite-4.dll')") | |
| metadata = MetaData(engine) | |
| session = sessionmaker(bind=engine)() | |
| # only need this next line when initially creating the db. | |
| #session.execute("SELECT InitSpatialMetaData();") | |
| Base = declarative_base(metadata=metadata) | |
| class Region(Base): | |
| __tablename__ = 'regions' | |
| id = Column(Integer, primary_key=True) | |
| name = Column(String(64)) | |
| code = Column(String(32)) | |
| geom = GeometryColumn(Geometry(2)) | |
| # enable the DDL extension, which allows CREATE/DROP operations | |
| # to work correctly. This is not needed if working with externally | |
| # defined tables. | |
| #------- this give me exceptions ------- | |
| #GeometryDDL(Region.__table__) | |
| metadata.create_all(engine) | |
| # A shape file to load data from | |
| sfn = r'D:\data\ABS\ASGC\STE09aAust.shp' | |
| shapeData = osgeo.ogr.Open(sfn) | |
| layer = shapeData.GetLayer() | |
| for index in xrange(layer.GetFeatureCount()): | |
| feature = layer.GetFeature(index) | |
| geometry = feature.GetGeometryRef() | |
| name = feature.GetFieldAsString('STATE_NAME') | |
| print 'loading: %s' % name | |
| region = Region( | |
| name = name, | |
| code = feature.GetFieldAsString('STATE_CODE'), | |
| geom = geometry.ExportToWkt()) | |
| session.add(region) | |
| session.commit() | |
| # read the regions out and dump as WKB | |
| regions = session.query(Region) | |
| for r in regions: | |
| out = open('%s.txt' % r.name, 'wb') | |
| out.write(r.geom.geom_wkb) | |
| out.close() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment