def loader(request): """Recreate sample schema from shapefiles and tear down when done.""" # Configure TableLoader to use directory containing sample shapefiles. root_path = os.path.dirname(__file__) data_path = os.path.join(root_path, '../../test_data') loader = TableLoader(directory=data_path) # Recreate PostgreSQL sample schema. with loader.database.cursor() as cur: cur.execute(""" CREATE EXTENSION IF NOT EXISTS postgis; DROP SCHEMA IF EXISTS sample CASCADE; CREATE SCHEMA sample; """) loader.database.refresh() # Load all shapefiles in test data directory. for filename in os.listdir(data_path): file_root, file_ext = os.path.splitext(filename) if file_ext.lower() == '.shp': shp_path = os.path.join(data_path, filename) table_name = 'sample.' + file_root loader.load_shp(shp_path, table_name) # Reproject all non-conforming SRIDs into project SRID. conform_srids(loader.srid, schema=loader.tables.sample) # Tear down sample schema when done. def teardown(): with loader.database.cursor() as cur: cur.execute("DROP SCHEMA sample CASCADE;") request.addfinalizer(teardown) return loader
shapefiles = {} for i in range(73): if i < 10: filename = 'p50%s_d00.shp' % i else: filename = 'p5%s_d00.shp' % i filepath = os.path.join(loader.get_path('puma_geom'), filename) if os.path.exists(filepath): subfile_name = filename[:-4] shapefiles['staging.%s' % subfile_name] = 'puma_geom/%s' % filename loader.load_shp_map(shapefiles) conform_srids(loader.srid, schema=loader.tables.staging, fix=True) exec_sql("DROP table if exists staging.puma00;") sql_str = "" for i in range(73): if i < 10: filename = 'p50%s_d00.shp' % i else: filename = 'p5%s_d00.shp' % i filepath = os.path.join(loader.get_path('puma_geom'), filename) if os.path.exists(filepath): subfile_name = filename[:-4] sql_str = sql_str + 'select area, perimeter, puma5, name, geom from staging.%s' % subfile_name if i < 72:
# Install PostGIS and create staging schema. loader = TableLoader() with loader.database.cursor() as cur: cur.execute(""" CREATE EXTENSION IF NOT EXISTS postgis; CREATE SCHEMA IF NOT EXISTS staging; """) loader.database.refresh() # Load shapefiles specified above to the project database. loader.load_shp_map(shapefiles) # Fix invalid geometries and reproject. staging = loader.tables.staging conform_srids(loader.srid, schema=staging, fix=True) # Load county land use code mapping. csv = loader.get_path('built/parcel/2010/rtp13_processing_notes/lucodes.csv') df = pd.read_csv(csv, dtype=str) df.dropna(how='any', inplace=True, subset=['county_id', 'land_use_type_id', 'development_type_id']) df.index.name = 'index' df_to_db(df, 'lucodes', schema=staging) # Add county land use code mapping unique constraint. exec_sql(""" ALTER TABLE staging.lucodes ADD CONSTRAINT lucodes_unique UNIQUE (county_id, land_use_type_id); """)