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)