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'
