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);
""")
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:
