def sde_to_shp():
"""SDE table to Shapefile."""
logging.info(
'Extracting {layername} layer from SDE.'.format(layername=layername))
df = geospatial.extract_sde_data(table=table)
# where=" ")
logging.info('Processing {layername} df.'.format(layername=layername))
df = df.rename(
columns={
'CD': 'council_di',
'facillity_': 'facility',
'enhanced_': 'enhanced',
'multipurp': 'multi_purp',
'basketb': 'basketball',
'tennisct': 'tennis_ct'
})
df = df.fillna('')
logging.info(
'Converting {layername} df to shapefile.'.format(layername=layername))
geospatial.df2shp(df=df,
folder=prod_dir,
layername=layername,
dtypes=dtypes,
gtype=gtype,
epsg=2230)
return 'Successfully converted {layername} to shapefile.'.format(
layername=layername)
def sde_to_shp():
"""SDE table to Shapefile."""
logging.info('Extracting {layername} layer from SDE.'.format(
layername=layername))
df = geospatial.extract_sde_data(table=table)
logging.info('Processing {layername} df.'.format(layername=layername))
df = df.fillna('')
df = df.rename(columns={
'gymnasuim': 'gymnasium',
'name': 'community'
})
logging.info('Converting {layername} df to shapefile.'.format(
layername=layername))
geospatial.df2shp(df=df,
folder=prod_dir,
layername=layername,
dtypes=dtypes,
gtype=gtype,
epsg=2230)
return 'Successfully converted {layername} to shapefile.'.format(
layername=layername)
def sde_to_shp():
"""SDE table to Shapefile."""
logging.info(
'Extracting {layername} layer from SDE.'.format(layername=layername))
df = geospatial.extract_sde_data(table=table)
logging.info('Processing {layername} df.'.format(layername=layername))
df = df.rename(
columns={
'routeshapename': 'rteshpname',
'route_short_name': 'short_name',
'route_long_name': 'long_name',
'route_type_text': 'rte_type_t',
'route_text_color': 'hex_color'
})
df = df.fillna('')
logging.info(
'Converting {layername} df to shapefile.'.format(layername=layername))
geospatial.df2shp(df=df,
folder=prod_dir,
layername=layername,
dtypes=dtypes,
gtype=gtype,
epsg=2230)
return 'Successfully converted {layername} to shapefile.'.format(
layername=layername)
def sde_to_shp():
"""SDE table to Shapefile."""
logging.info(
'Extracting {layername} layer from SDE.'.format(layername=layername))
sde_server = conf['sde_server']
sde_user = conf['sde_user']
sde_pw = conf['sde_pw']
sde_conn = pymssql.connect(sde_server, sde_user, sde_pw, 'sdw')
query = 'SELECT *,'\
+ ' [Shape].STAsText() as geom, '\
+ ' [Shape].STArea() as geom_area'\
+ f' FROM SDW.CITY.{table}'
logging.info(query)
df = pd.read_sql(query, sde_conn)
df.columns = [x.lower() for x in df.columns]
df = df.drop('shape', 1)
logging.info('Processing {layername} df.'.format(layername=layername))
logging.info(
'Converting {layername} df to shapefile.'.format(layername=layername))
geospatial.df2shp(df=df,
folder=prod_dir,
layername=layername,
dtypes=dtypes,
gtype=gtype,
epsg=2230)
return 'Successfully converted {layername} to shapefile.'.format(
layername=layername)
def sde_to_shp():
"""SDE table to Shapefile."""
logging.info('Extracting {layername} layer from SDE.'.format(
layername=layername))
df = geospatial.extract_sde_data(table=table)
logging.info('Processing {layername} df.'.format(layername=layername))
df = df.rename(columns={
'density_low': 'dens_low',
'density_hi': 'dens_high',
'density_bonus': 'dens_bonus',
'change_type': 'chg_type',
'change_date': 'chg_date'
})
df = df.fillna('')
logging.info('Converting {layername} df to shapefile.'.format(
layername=layername))
geospatial.df2shp(df=df,
folder=prod_dir,
layername=layername,
dtypes=dtypes,
gtype=gtype,
epsg=2230)
return 'Successfully converted {layername} to shapefile.'.format(
layername=layername)
def sde_to_shp():
"""SDE table to Shapefile."""
logging.info(
'Extracting {layername} layer from SDE.'.format(layername=layername))
df = geospatial.extract_sde_data(table=table)
logging.info('Processing {layername} df.'.format(layername=layername))
df = df.rename(
columns={
'stop_agency': 'stop_agncy',
'wheelchair_boarding': 'wheelchair',
'intersection_code': 'intersec',
'parent_station': 'parent_sta'
})
df = df.fillna('')
logging.info(
'Converting {layername} df to shapefile.'.format(layername=layername))
geospatial.df2shp(df=df,
folder=prod_dir,
layername=layername,
dtypes=dtypes,
gtype=gtype,
epsg=2230)
return 'Successfully converted {layername} to shapefile.'.format(
layername=layername)
def sde_to_shp():
"""SDE table to Shapefile."""
logging.info('Extracting {layername} layer from SDE.'.format(
layername=layername))
df = geospatial.extract_sde_data(table=table)
# where=" ")
logging.info('Processing {layername} df.'.format(layername=layername))
df = df.rename(columns={
'CD': 'council_di',
'facillity_': 'facility',
'enhanced_': 'enhanced',
'multipurp': 'multi_purp',
'basketb': 'basketball',
'tennisct': 'tennis_ct'
})
df = df.fillna('')
logging.info('Converting {layername} df to shapefile.'.format(
layername=layername))
geospatial.df2shp(df=df,
folder=prod_dir,
layername=layername,
dtypes=dtypes,
gtype=gtype,
epsg=2230)
return 'Successfully converted {layername} to shapefile.'.format(
layername=layername)
def sde_to_shp():
"""SDE table to Shapefile."""
logging.info(
'Extracting {layername} layer from SDE.'.format(layername=layername))
df = geospatial.extract_sde_data(table=table)
logging.info('Processing {layername} df.'.format(layername=layername))
df = df.rename(
columns={
'density_low': 'dens_low',
'density_hi': 'dens_high',
'density_bonus': 'dens_bonus',
'change_type': 'chg_type',
'change_date': 'chg_date'
})
df = df.fillna('')
logging.info(
'Converting {layername} df to shapefile.'.format(layername=layername))
geospatial.df2shp(df=df,
folder=prod_dir,
layername=layername,
dtypes=dtypes,
gtype=gtype,
epsg=2230)
return 'Successfully converted {layername} to shapefile.'.format(
layername=layername)
def sde_to_shp():
"""SDE table to Shapefile."""
logging.info('Extracting {layername} layer from SDE.'.format(
layername=layername))
df = geospatial.extract_sde_data(table=table)
logging.info('Processing {layername} df.'.format(layername=layername))
df = df.rename(columns={
'routeshapename': 'rteshpname',
'route_short_name': 'short_name',
'route_long_name': 'long_name',
'route_type_text': 'rte_type_t',
'route_text_color': 'hex_color'})
df = df.fillna('')
logging.info('Converting {layername} df to shapefile.'.format(
layername=layername))
geospatial.df2shp(df=df,
folder=prod_dir,
layername=layername,
dtypes=dtypes,
gtype=gtype,
epsg=2230)
return 'Successfully converted {layername} to shapefile.'.format(
layername=layername)
def sde_to_shp():
"""SDE table to Shapefile."""
logging.info('Extracting {layername} layer from SDE.'.format(
layername=layername))
df = geospatial.extract_sde_data(table=table,
where="LJURISDIC = 'SD'")
logging.info('Processing {layername} df.'.format(layername=layername))
df = df.fillna('')
df = df.rename(columns={
'abloaddr': 'min_addr',
'abhiaddr': 'max_addr',
'frxcoord': 'nad_from_x',
'frycoord': 'nad_from_y',
'toxcoord': 'nad_to_x',
'toycoord': 'nad_to_y'
})
df['postdate'] = pd.to_datetime(df['postdate'], errors='coerce')
df['postdate'] = df['postdate'].dt.strftime('%Y-%m-%d')
df['addsegdt'] = pd.to_datetime(df['addsegdt'], errors='coerce')
df['addsegdt'] = df['addsegdt'].dt.strftime('%Y-%m-%d')
for index, row in df.iterrows():
nad_from_x = row['nad_from_x']
nad_from_y = row['nad_from_y']
wgs_from_x, wgs_from_y = geospatial.pt_proj_conversion(lon=nad_from_x,
lat=nad_from_y)
df.set_value(index, 'wgs_from_x', wgs_from_x)
df.set_value(index, 'wgs_from_y', wgs_from_y)
nad_to_x = row['nad_to_x']
nad_to_y = row['nad_to_y']
wgs_to_x, wgs_to_y = geospatial.pt_proj_conversion(lon=nad_to_x,
lat=nad_to_y)
df.set_value(index, 'wgs_to_x', wgs_to_x)
df.set_value(index, 'wgs_to_y', wgs_to_y)
logging.info('Converting {layername} df to shapefile.'.format(
layername=layername))
geospatial.df2shp(df=df,
folder=prod_dir,
layername=layername,
dtypes=dtypes,
gtype=gtype,
epsg=2230)
return 'Successfully converted {layername} to shapefile.'.format(
layername=layername)
def sde_to_shp():
"""SDE table to Shapefile."""
logging.info(
'Extracting {layername} layer from SDE.'.format(layername=layername))
df = geospatial.extract_sde_data(table=table, where="LJURISDIC = 'SD'")
logging.info('Processing {layername} df.'.format(layername=layername))
df = df.fillna('')
df = df.rename(
columns={
'abloaddr': 'min_addr',
'abhiaddr': 'max_addr',
'frxcoord': 'nad_from_x',
'frycoord': 'nad_from_y',
'toxcoord': 'nad_to_x',
'toycoord': 'nad_to_y'
})
df['postdate'] = pd.to_datetime(df['postdate'], errors='coerce')
df['postdate'] = df['postdate'].dt.strftime('%Y-%m-%d')
df['addsegdt'] = pd.to_datetime(df['addsegdt'], errors='coerce')
df['addsegdt'] = df['addsegdt'].dt.strftime('%Y-%m-%d')
for index, row in df.iterrows():
nad_from_x = row['nad_from_x']
nad_from_y = row['nad_from_y']
wgs_from_x, wgs_from_y = geospatial.pt_proj_conversion(lon=nad_from_x,
lat=nad_from_y)
df.set_value(index, 'wgs_from_x', wgs_from_x)
df.set_value(index, 'wgs_from_y', wgs_from_y)
nad_to_x = row['nad_to_x']
nad_to_y = row['nad_to_y']
wgs_to_x, wgs_to_y = geospatial.pt_proj_conversion(lon=nad_to_x,
lat=nad_to_y)
df.set_value(index, 'wgs_to_x', wgs_to_x)
df.set_value(index, 'wgs_to_y', wgs_to_y)
logging.info(
'Converting {layername} df to shapefile.'.format(layername=layername))
geospatial.df2shp(df=df,
folder=prod_dir,
layername=layername,
dtypes=dtypes,
gtype=gtype,
epsg=2230)
return 'Successfully converted {layername} to shapefile.'.format(
layername=layername)
def get_sidewalk_gis(**kwargs):
""" Get sidewalk geodatabase from shared drive"""
sde_server = conf['sde_server']
sde_user = conf['sde_user']
sde_pw = conf['sde_pw']
sde_conn = pymssql.connect(sde_server, sde_user, sde_pw, 'sdw')
query = "SELECT *, [Shape].STAsText() as geom FROM SDW.IAMSD.SIDEWALK"
df = pd.read_sql(query, sde_conn)
df.columns = [x.lower() for x in df.columns]
df = df.drop('shape', 1)
df = df.rename(columns={'sapid':'seg_id',
'cdcode':'council',
'cpcode':'comm_plan',
'legacy_id':'geojoin_id',
'iamfloc':'fun_loc_id',
'loc_descr':'loc_desc'
})
df = df.fillna('')
oci = pd.read_csv(conf['prod_data_dir'] + '/sidewalk_cond_datasd.csv')
oci['oci_yr'] = oci['oci_date'].map(lambda x: pd.to_datetime(x).year)
df_merge = pd.merge(df,
oci,
how="left",
left_on=['seg_id','geojoin_id'],
right_on=['seg_id','geojoin_id']
)
rows = df_merge.shape[0]
logging.info('processed {} rows'.format(rows))
geospatial.df2shp(df=df_merge,
folder=prod_dir,
layername=layername,
dtypes=dtypes,
gtype=gtype,
epsg=2230)
return "Converted table to shapefile"
def sde_to_shp():
"""SDE table to Shapefile."""
logging.info(
'Extracting {layername} layer from SDE.'.format(layername=layername))
df = geospatial.extract_sde_data(table=table)
logging.info('Processing {layername} df.'.format(layername=layername))
df = df.fillna('')
logging.info(
'Converting {layername} df to shapefile.'.format(layername=layername))
geospatial.df2shp(df=df,
folder=prod_dir,
layername=layername,
dtypes=dtypes,
gtype=gtype,
epsg=2230)
return 'Successfully converted {layername} to shapefile.'.format(
layername=layername)
def sde_to_shp():
"""SDE table to shapefile."""
logging.info('Extracting {layername} layer from SDE.'.format(
layername=layername))
df = geospatial.extract_sde_data(table=table,
where="OVERLAY_JURIS = 'SD'")
logging.info('Processing {layername} df.'.format(layername=layername))
df = df.fillna('')
logging.info('Converting {layername} df to shapefile.'.format(
layername=layername))
geospatial.df2shp(df=df,
folder=prod_dir,
layername=layername,
dtypes=dtypes,
gtype=gtype,
epsg=2230)
return 'Successfully converted {layername} to shapefile.'.format(
layername=layername)
def get_sidewalk_gis(**kwargs):
""" Get sidewalk geodatabase from shared drive"""
sde_query = general.file_to_string('./sql/sidewalk_sde.sql', __file__)
sde_conn = MsSqlHook(mssql_conn_id='atlas_sde')
#sde_server = conf['sde_server']
#sde_user = conf['sde_user']
#sde_pw = conf['sde_pw']
#sde_conn = pymssql.connect(sde_server, sde_user, sde_pw, 'sdw')
#query = "SELECT *, [Shape].STAsText() as geom FROM SDW.IAMSD.SIDEWALK"
df = sde_conn.get_pandas_df(sde_query)
df.columns = [x.lower() for x in df.columns]
df = df.drop('shape', 1)
df = df.rename(
columns={
'sapid': 'seg_id',
'cdcode': 'council',
'cpcode': 'comm_plan',
'legacy_id': 'geojoin_id',
'iamfloc': 'fun_loc_id',
'loc_descr': 'loc_desc'
})
df = df.fillna('')
logging.info('processed {} rows'.format(rows))
geospatial.df2shp(df=df,
folder=prod_dir,
layername=layername,
dtypes=dtypes,
gtype=gtype,
epsg=2230)
return "Converted table to shapefile"
def sde_to_shp():
"""SDE table to Shapefile."""
logging.info('Extracting {layername} layer from SDE.'.format(
layername=layername))
df = geospatial.extract_sde_data(table=table,
where="JURISDICTION = 'San Diego'")
logging.info('Processing {layername} df.'.format(layername=layername))
df = df.fillna('')
df = df.rename(columns={'route_class_name': 'class'})
logging.info('Converting {layername} df to shapefile.'.format(
layername=layername))
geospatial.df2shp(df=df,
folder=prod_dir,
layername=layername,
dtypes=dtypes,
gtype=gtype,
epsg=2230)
return 'Successfully converted {layername} to shapefile.'.format(
layername=layername)
def sde_to_shp():
"""SDE table to Shapefile."""
logging.info(
'Extracting {layername} layer from SDE.'.format(layername=layername))
df = geospatial.extract_sde_data(table=table,
where="JURISDICTION = 'San Diego'")
logging.info('Processing {layername} df.'.format(layername=layername))
df = df.fillna('')
df = df.rename(columns={'route_class_name': 'class'})
logging.info(
'Converting {layername} df to shapefile.'.format(layername=layername))
geospatial.df2shp(df=df,
folder=prod_dir,
layername=layername,
dtypes=dtypes,
gtype=gtype,
epsg=2230)
return 'Successfully converted {layername} to shapefile.'.format(
layername=layername)
def sde_to_shp():
"""SDE table to Shapefile."""
logging.info('Extracting {layername} layer from SDE.'.format(
layername=layername))
df = geospatial.extract_sde_data(table=table,
where="TYPE = 'Existing'")
logging.info('Processing {layername} df.'.format(layername=layername))
df = df.rename(columns={'historicsurvey': 'survey'})
df = df.fillna('')
logging.info('Converting {layername} df to shapefile.'.format(
layername=layername))
geospatial.df2shp(df=df,
folder=prod_dir,
layername=layername,
dtypes=dtypes,
gtype=gtype,
epsg=2230)
return 'Successfully converted {layername} to shapefile.'.format(
layername=layername)
def sde_to_shp():
"""SDE table to Shapefile."""
logging.info('Extracting {layername} layer from SDE.'.format(
layername=layername))
df = geospatial.extract_sde_data(table=table,
where="ADDRJUR = 'SD'")
logging.info('Processing {layername} df.'.format(layername=layername))
df = df.rename(columns={'placement_location': 'plcmt_loc',
'address_type': 'add_type'})
df = df.fillna('')
logging.info('Converting {layername} df to shapefile.'.format(
layername=layername))
geospatial.df2shp(df=df,
folder=prod_dir,
layername=layername,
dtypes=dtypes,
gtype=gtype,
epsg=2230)
return 'Successfully converted {layername} to shapefile.'.format(
layername=layername)
def sde_to_shp():
"""SDE table to Shapefile."""
logging.info('Extracting {layername} layer from SDE.'.format(
layername=layername))
df = geospatial.extract_sde_data(table=table)
logging.info('Processing {layername} df.'.format(layername=layername))
df = df.fillna('')
df['imp_date'] = pd.to_datetime(df['imp_date'], errors='coerce')
df['imp_date'] = df['imp_date'].dt.strftime(conf['date_format_ymd'])
logging.info('Converting {layername} df to shapefile.'.format(
layername=layername))
geospatial.df2shp(df=df,
folder=prod_dir,
layername=layername,
dtypes=dtypes,
gtype=gtype,
epsg=2230)
return 'Successfully converted {layername} to shapefile.'.format(
layername=layername)
def sde_to_shp():
"""SDE table to Shapefile."""
logging.info('Extracting {layername} layer from SDE.'.format(
layername=layername))
df = geospatial.extract_sde_data(table=table)
# where=" ")
logging.info('Processing {layername} df.'.format(layername=layername))
df = df.rename(columns={'sum_totalpop': 'total_pop',
'sum_totunits': 'tot_units',
'sum_occunits': 'occ_units',
'sum_vacunits': 'vac_units'})
df = df.fillna('')
logging.info('Converting {layername} df to shapefile.'.format(
layername=layername))
geospatial.df2shp(df=df,
folder=prod_dir,
layername=layername,
dtypes=dtypes,
gtype=gtype,
epsg=2230)
return 'Successfully converted {layername} to shapefile.'.format(
layername=layername)
