def geocode_rows(self): conn = connect_to_pg() res = conn.execute("select distinct {} from {} where {}".format( self.addr_col, self.table, self.where_clause)) self.rows = [r[0] for r in res.fetchall()] # iterate through batches of 1000 for i in range(0, len(self.rows), 1000): rows_to_geocode = self.rows[i:i + 1000] results = geocoding.batch_geocode(rows_to_geocode, out_sr=4326, geocoder=self.geocoder) result_dict = dict(zip(rows_to_geocode, results)) for add, res in result_dict.items(): if res['attributes']['User_fld'] != "" and self.parcel_col: query = "update {} set {} = ST_SetSRID(ST_MakePoint({}, {}), 4326), {} = '{}' where {} = '{}'".format( self.table, self.geom_col, res['location']['x'], res['location']['y'], self.parcel_col, res['attributes']['User_fld'], self.addr_col, add.replace("'", "''")) conn.execute(query) elif res['location']['x'] != 'NaN': query = "update {} set {} = ST_SetSRID(ST_MakePoint({}, {}), 4326) where {} = '{}'".format( self.table, self.geom_col, res['location']['x'], res['location']['y'], self.addr_col, add.replace("'", "''")) conn.execute(query) else: pass
def batch_geocode(l_adress, kind='arcgis'): if kind == 'arcgis': gis = GIS("http://www.arcgis.com", username=username, password=pwd) # use the first of GIS's configured geocoders geocoder = get_geocoders(gis)[0] results = batch_geocode(l_adress) lats = [r['location']['x'] for r in results] lngs = [r['location']['y'] for r in results] return lats, lngs
def getxlonseptic(arow): if pd.isnull(arow['GIS_LONGITUDE']) or arow['GIS_LONGITUDE']==-1: addr = "%s %s %s %s %s, %s %s" % (int(arow['P_STREET_NUMBER']) ,arow['P_STREET_DIRECTION'], arow['P_STREET_NAME'] ,arow['P_STREET_TYPE'],arow['P_CITY'],arow['P_STATE'], arow['P_ZIP']) geoaddr = batch_geocode([addr]) coords = geoaddr[0]['location'] xlon = coords['x'] #ylat = coords['y'] return xlon else: return arow['GIS_LONGITUDE']
def getylatfacilities(arow): if pd.isnull(arow['GIS_LATITUDE']) or arow['GIS_LATITUDE']==-1: addr = "%s %s %s %s %s, %s %s" % (int(arow['STREET_NUMBER']) ,arow['STREET_DIRECTION'], arow['STREET_NAME'] ,arow['STREET_TYPE'],arow['CITY'],arow['STATE'], arow['ZIP']) geoaddr = batch_geocode([addr]) coords = geoaddr[0]['location'] #xlon = coords['x'] ylat = coords['y'] #arow.loc[:,('GIS_LATITUDE')] = ylat #arow.loc[:,('GIS_LONGITUDE')] = xlon return ylat else: return arow['GIS_LATITUDE']
def get_batch_geo_coordinates_from_arcgis_with_login(self, location_addresses: list): """ purpose: Retrieve Latitude and Longitude to a Given Addresses/Locations With Credentials @param location_addresses: Latitude and Longitude needed Addresses/Locations @return: Dict status: True or False based on success, message: Error message if an error occurred result: { 'lat_lng_list': List of Longitudes and Latitudes of the Addresses Provided 'latitude': 'longitude': 'all_results': Whole Response Object Received From Arc GIS } """ # If Username and Password Not Set, Return an error Object # if self.username_password_flag: # return self.__get_error_msg('Username or Password is not Set') try: GIS("http://www.arcgis.com", self.connection_params['username'], self.connection_params['password']) arc_gis_locations = batch_geocode(location_addresses) if len(arc_gis_locations) > 0: # Filter only Latitude and Longitude Values lat_lng_list = list( map(lambda loc: {'longitude': loc['location']['x'], 'latitude': loc['location']['y']}, arc_gis_locations)) return { 'status': True, 'message': None, 'result': { 'lat_lng_list': lat_lng_list, 'all_results': arc_gis_locations } } else: return self.__get_error_msg('Unknown Location. No Results Found') except ConnectionError: return self.__get_error_msg('Connection Error') except TypeError: return self.__get_error_msg('Type Error') except Exception as e: return self.__get_error_msg('Unknown Error Occurred, Error: {}'.format(e))
def addressColToLatLong(self, column): """ geocode an entire column of addresses into LatLong coordinates :param column: pandas.Series :return: pandas.Series """ tot_len = len(column) loc_dict = {} for batch_no in range(int(tot_len / self.conv_batch_size) + 1): start_ind = batch_no * self.conv_batch_size stop_ind = (batch_no + 1) * self.conv_batch_size for ind, loc in zip( column[start_ind:stop_ind].index, batch_geocode(list(column[start_ind:stop_ind]))): loc_dict[ind] = Point(loc['location']['x'], loc['location']['y']) time.sleep(10) return pd.Series(loc_dict)
def batch_geocode_memo(addresses, **kwargs): """Batch geocodes a list of addresses and memoizes the results to avoid repeated calls and credit consumption. args: addresses - the addresses to geocode **kwargs: cache_path - persist the results to a file get_key - function to uniquely identify each address all others from here: https://developers.arcgis.com/python/api-reference/arcgis.geocoding.html?highlight=batch_geocode#arcgis.geocoding.batch_geocode""" output = [] for c in chunk(addresses): # split into chunks for large volumes results = batch_geocode(addresses=c, **kwargs) output += results return output
def geocode_Institution_table(self, table, outputname): institutions = table['INSTIT_NAME'].tolist() # print("INSTITUTION_NAMES: {}".format(institutions)) gis = GIS(username = "******", password="******") OGinstit = [] processed_instit = [] for instit in institutions: OGinstit.append(instit) processed_instit.append(self.process_institution(instit)) # print(processed_instit) i = 0 len_processed_instit = len(processed_instit) for instit in OGinstit: temp = None if ((i+1) < len_processed_instit): temp = {"INSTIT_NAME": instit, "data": processed_instit[i]} i+=1 else: temp = {"INSTIT_NAME": instit, "data": processed_instit[i]} break def update_value(row, data=temp): if row.INSTIT_NAME == data["INSTIT_NAME"]: row.INSTIT_NAME = data["data"] table.apply(update_value, axis=1) geocode_result = batch_geocode(addresses = processed_instit) # print("GEOCODE_RESULT: {}".format(geocode_result)) geocode_dict = {"x": [], "y":[]} table["x"] = 0 table["y"] = 0 for result in geocode_result: loc = result["location"] print("X: {} Y: {}".format(loc["x"], loc["y"])) geocode_dict["x"].append(loc["x"]) geocode_dict["y"].append(loc["y"]) x_ind = 0 y_ind = 0 for index, row in table.iterrows(): row_copy = row table.loc[index, "x"] = geocode_dict["x"][x_ind] table.loc[index, "y"] = geocode_dict["y"][y_ind] x_ind+=1 y_ind+=1
def geocode_rows(**kwargs): from arcgis import geocoding # Expected **kwargs: # table: name of table to geocode # address_column: name of address column or expression # geometry_column: name of geometry column # parcel_column: name of parcel column # where: a Postgres clause to restrict which rows get geocoded # geocoder: enum ['composite', 'address', 'centerline']. default to composite hook = PostgresHook('etl_postgres') # default where clause where = kwargs['where'] if 'where' in kwargs.keys() else "1 = 1" geocoder = kwargs['geocoder'] if 'geocoder' in kwargs.keys( ) else 'composite' # generate an array of distinct addresses addresses = [ r[0] for r in hook.get_records( f"select distinct {kwargs['address_column']} from {kwargs['table']} where {where}" ) ] limit = 1000 for i in range(0, len(addresses), limit): batch_addresses = addresses[i:i + limit] results = geocoding.batch_geocode(batch_addresses, out_sr=4326, geocoder=geocoders[geocoder]) zipped = dict(zip(batch_addresses, results)) for add, res in zipped.items(): if 'parcel_column' in kwargs.keys(): coords = res['location'] pnum = res['attributes']['User_fld'] query = f"update {kwargs['table']} set {kwargs['geometry_column']} = ST_SetSRID(ST_MakePoint({coords['x']}, {coords['y']}), 4326), {kwargs['parcel_column']} = '{pnum}' where {kwargs['address_column']} = '{add}'" elif res['location']['x'] != 'NaN': query = f"update {kwargs['table']} set {kwargs['geometry_column']} = ST_SetSRID(ST_MakePoint({coords['x']}, {coords['y']}), 4326) where {kwargs['address_column']} = '{add}'" else: pass
def geocode_publication_table(self, table, outputname): study_locations = table['STUDY_LOC'].tolist() # print("INSTITUTION_NAMES: {}".format(institutions)) gis = GIS(username = "******", password="******") geocode_result = batch_geocode(addresses = study_locations) # print("GEOCODE_RESULT: {}".format(geocode_result)) geocode_dict = {"x": [], "y":[]} table["x"] = 0 table["y"] = 0 for result in geocode_result: loc = result["location"] # print("X: {} Y: {}".format(loc["x"], loc["y"])) geocode_dict["x"].append(loc["x"]) geocode_dict["y"].append(loc["y"]) x_ind = 0 y_ind = 0 for index, row in table.iterrows(): row_copy = row table.loc[index, "x"] = geocode_dict["x"][x_ind] table.loc[index, "y"] = geocode_dict["y"][y_ind] x_ind+=1 y_ind+=1
if not pd.isna(address): return address + ',' + city + ',' + state + ',' + zipCode else: return city + ',' + state + ',' + zipCode else: return state + ',' + zipCode else: return state else: return np.nan prop_table['fixed_address'] = prop_table.apply(define_address, axis=1) addresses = list(prop_table.fixed_address) results = batch_geocode(addresses) df = pd.DataFrame(columns=['score', 'latitude', 'longitude', 'address']) count = 0 for res in results: if (res == None): row = [np.nan, np.nan, np.nan, np.nan] elif (res['score'] == 0): row = [np.nan, np.nan, np.nan, np.nan] else: row = [ res['score'], res['attributes']['X'], res['attributes']['Y'], res['attributes']['Place_addr'] ] df.loc[len(df)] = row
def main(arguments): # noqa: C901 # initialize logging logger = initialize_logging(arguments.log_file) # Create the GIS logger.info("Authenticating...") # First step is to get authenticate and get a valid token gis = GIS(arguments.org_url, username=arguments.username, password=arguments.password, verify_cert=not arguments.skip_ssl_verification) # Get the project and data item = gis.content.get(arguments.project_id) project = workforce.Project(item) dispatcher = project.dispatchers.search(where="{}='{}'".format( project._dispatcher_schema.user_id, arguments.username)) if not dispatcher: log_critical_and_raise_exception("{} is not a dispatcher".format( args.username)) # Read the csv file logger.info("Reading CSV file: {}...".format(arguments.csv_file)) assignments_in_csv = [] locations = [] with open(arguments.csv_file, 'r') as file: reader = csv.DictReader(file) for row in reader: locations.append(row[args.location_field]) assignments_in_csv.append(row) # Fetch assignment types assignment_types = project.assignment_types.search() assignment_type_dict = {} for assignment_type in assignment_types: assignment_type_dict[assignment_type.name] = assignment_type # Fetch dispatchers dispatchers = project.dispatchers.search() dispatchers_dict = {} for dispatcher in dispatchers: dispatchers_dict[dispatcher.user_id] = dispatcher # Fetch the workers workers = project.workers.search() workers_dict = {} for worker in workers: workers_dict[worker.user_id] = worker if not (args.x_field and args.y_field): geocoder = None if args.custom_geocoder: geocoder = Geocoder.fromitem(gis.content.get(args.custom_geocoder)) addresses = batch_geocode(locations, geocoder=geocoder, out_sr=args.wkid) assignments_to_add = [] for i, assignment in enumerate(assignments_in_csv): assignment_to_add = workforce.Assignment( project, assignment_type=assignment_type_dict[assignment[ args.assignment_type_field]], status="unassigned") # Create the geometry if args.x_field and args.y_field: geometry = dict(x=float(assignment[args.x_field]), y=float(assignment[args.y_field]), spatialReference=dict(wkid=int(args.wkid))) else: try: location_geometry = addresses[i]['location'] except Exception as e: logger.info(e) logger.info( "Geocoding did not work for the assignment with location {}. " "Please check your addresses again".format( assignment[args.location_field])) logger.info("Continuing on to the next assignment") continue location_geometry['spatialReference'] = dict(wkid=int(args.wkid)) geometry = location_geometry assignment_to_add.geometry = geometry # Determine the assignment due date, and if no time is provided, make the due date all day if args.due_date_field and assignment[args.due_date_field]: d = datetime.datetime.strptime(assignment[args.due_date_field], args.date_format) p_date = pendulum.instance(d, tz=args.timezone) if p_date.second == 0 and p_date.hour == 0 and p_date.minute == 0: p_date = p_date.at(hour=23, minute=59, second=59) # Convert date to UTC time assignment_to_add.due_date = datetime.datetime.fromtimestamp( p_date.in_tz('UTC').timestamp()) # Set the location assignment_to_add.location = assignment[args.location_field] # Set the dispatcher if args.dispatcher_field and assignment[args.dispatcher_field]: assignment_to_add.dispatcher = dispatchers_dict[assignment[ args.dispatcher_field]] else: assignment_to_add.dispatcher = dispatcher # Fetch workers and assign the worker to the assignment if args.worker_field and assignment[args.worker_field]: assignment_to_add.worker = workers_dict[assignment[ args.worker_field]] assignment_to_add.assigned_date = datetime.datetime.fromtimestamp( pendulum.now('UTC').timestamp()) assignment_to_add.status = "assigned" else: assignment_to_add.status = "unassigned" # Set the priority if args.priority_field and assignment[args.priority_field]: assignment_to_add.priority = assignment[args.priority_field] # Set the description if args.description_field and assignment[args.description_field]: assignment_to_add.description = assignment[args.description_field] # Set the work order id if args.work_order_id_field and assignment[args.work_order_id_field]: assignment_to_add.work_order_id = assignment[ args.work_order_id_field] # Set attachment if args.attachment_file_field and assignment[ args.attachment_file_field]: assignment_to_add.attachment_file = types.SimpleNamespace() assignment_to_add.attachment_file = assignment[ args.attachment_file_field] # Add all assignments to the list created assignments_to_add.append(assignment_to_add) # Batch add all assignments to the project logger.info("Adding Assignments...") assignments = project.assignments.batch_add(assignments_to_add) logger.info("Adding Attachments...") for assignment in assignments: if hasattr(assignment, "attachment_file"): assignment.attachments.add(assignment.attachment_file) logger.info("Completed")
def get_batch_geo_coordinates_from_arcgis_with_login( self, location_addresses: list, connection_params: dict): GIS("http://www.arcgis.com", connection_params['ARCGIS_USER'], connection_params['ARCGIS_PASSWORD']) arc_gis_locs = batch_geocode(location_addresses) return arc_gis_locs
# -*- coding: utf-8 -*- """ Created on Mon Dec 18 17:39:55 2017 @author: Max """ from arcgis.gis import GIS from arcgis.geocoding import get_geocoders, batch_geocode gis = GIS("http://www.arcgis.com", "nlgis_max", "Onram@north1") # use the first of GIS's configured geocoders geocoder = get_geocoders(gis)[0] test = batch_geocode(['Denver, CO']) print(test[0]['location']) print(type(test[0]['location'])) 'test git gui'