def socrata_pub(records, cfg_dataset, replace, date_fields=None):
"""Summary
Args:
records (TYPE): Description
cfg_dataset (TYPE): Description
replace (TYPE): Description
date_fields (None, optional): Description
Returns:
TYPE: Description
"""
if cfg_dataset.get("location_fields"):
lat_field = cfg_dataset["location_fields"]["lat"].lower()
lon_field = cfg_dataset["location_fields"]["lon"].lower()
location_field = cfg_dataset["location_fields"][
"location_field"].lower()
else:
lat_field = None
lon_field = None
location_field = None
return socratautil.Soda(
auth=SOCRATA_CREDENTIALS,
records=records,
resource=cfg_dataset["socrata_resource_id"],
date_fields=date_fields,
lat_field=lat_field,
lon_field=lon_field,
location_field=location_field,
source="postgrest",
replace=replace,
)
def main():
"""Summary
Args:
jobs (TYPE): Description
**kwargs: Description
Returns:
TYPE: Descriptio
"""
script_name = os.path.basename(__file__).replace(".py", "")
# job_agol = jobutil.Job(
# name=f"{script_name}_agol",
# url=JOB_DB_API_URL,
# source="dropbox",
# destination="agol",
# auth=JOB_DB_API_TOKEN,
# )
# job_agol.start()
# job_socrata = jobutil.Job(
# name=f"{script_name}_socrata",
# url=JOB_DB_API_URL,
# source="dropbox",
# destination="socrata",
# auth=JOB_DB_API_TOKEN,
# )
# job_socrata.start()
data = get_data(dropbox_path, DROPBOX_BCYCLE_TOKEN)
data = handle_data(data)
data = datautil.upper_case_keys(data)
data = datautil.replace_keys(data, {"STATUS": "KIOSK_STATUS"})
layer = agolutil.get_item(auth=AGOL_CREDENTIALS, service_id=service_id)
res = layer.manager.truncate()
agolutil.handle_response(res)
adds = agolutil.feature_collection(data)
res = layer.edit_features(adds=adds)
agolutil.handle_response(res)
socratautil.Soda(
auth=SOCRATA_CREDENTIALS,
records=data,
resource=socrata_resource_id,
lat_field="latitude",
lon_field="longitude",
location_field="location",
replace=True,
)
return len(data)
def main():
"""Summary
Args:
jobs (TYPE): Description
**kwargs: Description
Returns:
TYPE: Description
"""
# get current traffic signal data from Socrata
socr = socratautil.Soda(resource=SOCR_SIG_RES_ID, fetch_metadata=True)
signal_data = socr.data
kits_query = kitsutil.status_query()
kits_data = kitsutil.data_as_dict(KITS_CREDENTIALS, kits_query)
kits_data = datautil.replace_timezone(kits_data,
["OPERATION_STATE_DATETIME"])
kits_data = datautil.stringify_key_values(kits_data)
# verify the KITS data is current
# sometimes the signal status service goes down
# in which case contact ATMS support
stale = kitsutil.check_for_stale(kits_data, "OPERATION_STATE_DATETIME")
# filter KITS data for statuses of concern
kits_data = datautil.filter_by_val(kits_data, "OPERATION_STATE",
FLASH_STATUSES)
# append kits data to signal data
if kits_data:
new_data = datautil.lower_case_keys(kits_data)
new_data = datautil.merge_dicts(
signal_data,
new_data,
"signal_id",
["operation_state_datetime", "operation_state", "plan_id"],
)
new_data = datautil.stringify_key_values(new_data)
else:
new_data = []
# get current signal status DATASET and metadata from socrata
sig_status = socratautil.Soda(resource=SOCR_SIG_STAT_RES_ID,
fetch_metadata=True)
# add special socrata deleted field
# required for sending delete requests to socrata
fieldnames = sig_status.fieldnames + [":deleted"]
# transform signal status socrata data for comparison
# with "new" data from kits
sig_status_data = datautil.reduce_to_keys(sig_status.data, fieldnames)
date_fields = sig_status.date_fields
sig_status_data = socratautil.strip_geocoding(sig_status_data)
sig_status_data = datautil.stringify_key_values(sig_status_data)
# identify signals whose status (OPERATION_STATE) has changed
cd_results = datautil.detect_changes(
sig_status_data,
new_data,
"signal_id",
# only a change in operation state
# triggers an update to socrata DATASET
keys=["operation_state"],
)
if cd_results["new"] or cd_results["change"] or cd_results["delete"]:
adds = add_ids(cd_results["new"])
deletes = socratautil.prepare_deletes(cd_results["delete"],
"signal_id")
payload = adds + cd_results["change"]
payload = add_timestamps(payload)
payload = payload + deletes
payload = datautil.reduce_to_keys(payload, fieldnames)
results = socratautil.Soda(
auth=SOCRATA_CREDENTIALS,
records=payload,
resource=SOCR_SIG_STAT_RES_ID,
date_fields=None,
lat_field="location_latitude",
lon_field="location_longitude",
location_field="location",
replace=False,
)
return len(payload)
else:
return 0
def main():
"""
Args:
job
**kwargs
Returns:
Raises:
e: Description
"""
dt_current = arrow.now().replace(months=-1)
dt_current_formatted = dt_current.format("MM-YYYY")
up_to_date = False
results = None
while not up_to_date:
socrata_dt = max_date_socrata(resource_id)
socrata_dt_formatted = arrow.get(socrata_dt).format("MM-YYYY")
if dt_current_formatted == socrata_dt_formatted:
up_to_date = True
results = 0
else:
# socrata data is at least one month old
print("entered else")
dropbox_month = arrow.get(socrata_dt).replace(
months=1).format("MM")
dropbox_year = arrow.get(socrata_dt).replace(
months=1).format("YYYY")
current_file = "TripReport-{}{}.csv".format(
dropbox_month, dropbox_year)
root = "austinbcycletripdata" # note the lowercase-ness
path = "/{}/{}/{}".format(root, dropbox_year, current_file)
date_fields = ["checkout_date"]
try:
print("enter try")
try:
data = get_data(path, DROPBOX_BCYCLE_TOKEN)
results = len(data)
except TypeError:
results = 0
except dropbox.exceptions.ApiError as e:
if "LookupError" in str(e):
# end loop when no file can be found
up_to_date = True
break
else:
raise e
data = handle_data(data)
socratautil.Soda(
auth=SOCRATA_CREDENTIALS,
records=data,
resource=resource_id,
location_field=None,
)
results = len(data)
return results
def main():
SOCRATA_RESOURCE_ID = "jqhg-imb3"
FIELDNAMES = [
"COMMENT_FIELD2",
"START_DATE",
"SITE_CODE",
"COMMENT_FIELD1",
"GLOBALID",
"DATA_FILE",
"COMMENT_FIELD4",
"COMMENT_FIELD3",
"LATITUDE",
"LONGITUDE",
]
CONFIG = {
"service_url":
"http://services.arcgis.com/0L95CJ0VTaxqcmED/arcgis/rest/services/Traffic_Count_Location/FeatureServer/0/",
"service_id": "3c56025e645045998ee499c0725dfebb",
"params": {
"f": "json",
"where": "1=1",
"outFields": "*",
"returnGeometry": True,
"outSr": 4326, # return WGS84
},
}
layer = agolutil.get_item(auth=AGOL_CREDENTIALS,
service_id=CONFIG["service_id"])
features = layer.query(**CONFIG["params"])
features_add = []
for feature in features:
feature_add = {
key.upper(): value
for key, value in feature.attributes.items()
if key.upper() in FIELDNAMES
}
feature_add["LONGITUDE"] = float(str(
feature.geometry["x"])[:10]) # truncate coordinate
feature_add["LATITUDE"] = float(str(feature.geometry["y"])[:10])
if feature_add.get("START_DATE"):
feature_add["START_DATE"] = parse_mills(feature_add["START_DATE"])
features_add.append(feature_add)
socratautil.Soda(
auth=SOCRATA_CREDENTIALS,
resource=SOCRATA_RESOURCE_ID,
records=features_add,
lat_field="latitude",
lon_field="longitude",
location_field="location",
replace=True,
)
return len(features_add)
def main():
"""Summary
Args:
job (TYPE): Description
**kwargs: Description
Returns:
TYPE: Description
"""
args = cli_args()
replace = args.replace
# get most recent traffic count record from socrata
socrata_data = socratautil.Soda(resource=socrata_resource,
soql={
"$order": "curdatetime desc",
"$limit": 1
})
socrata_data = socrata_data.data
kits_query_recent = """
SELECT TOP (1) DETID as det_id
,CURDATETIME as dettime
,DETNAME as lane
,VOLUME as vol
,SPEED as spd
FROM [KITS].[SYSDETHISTORYRM]
ORDER BY CURDATETIME DESC
"""
kits_data_recent = kitsutil.data_as_dict(KITS_CREDENTIALS,
kits_query_recent)
for record in kits_data_recent:
new_date = arrow.get(record["dettime"], "US/Central")
record["dettime"] = new_date.timestamp
if replace:
kits_query = """
SELECT i.DETID as detid
,i.CURDATETIME as curdatetime
,i.VOLUME as volume
,i.SPEED as speed
,i.INTNAME as intname
,i.OCCUPANCY as occupancy
,e.INTID as int_id
,e.DETSN as detname
FROM [KITS].[SYSDETHISTORYRM] i
LEFT OUTER JOIN [KITS].[DETECTORSRM] e
ON i.[DETID] = e.[DETID]
ORDER BY CURDATETIME DESC
"""
# send new data if the socrata data is behind KITS data
elif socrata_data[0]["curdatetime"] < kits_data_recent[0]["dettime"]:
# create query for counts since most recent socrata data
# query start time must be in local US/Central time (KITSDB is naive!)
strtime = (arrow.get(socrata_data[0]["curdatetime"]).to(
"US/Central").format("YYYY-MM-DD HH:mm:ss"))
# INTID is KITS_ID in data tracker / socrata
# it uniquely identifies the radar device/location
# detname and the lane and should be queried from the DETECTORSRM
# table note that the values in the detname field in SYSDETHISTORYRM
# are not current and appear to be updated only the first time the
# detector is configured in KITS
kits_query = """
SELECT i.DETID as detid
,i.CURDATETIME as curdatetime
,i.VOLUME as volume
,i.SPEED as speed
,i.INTNAME as intname
,i.OCCUPANCY as occupancy
,e.INTID as int_id
,e.DETSN as detname
FROM [KITS].[SYSDETHISTORYRM] i
LEFT OUTER JOIN [KITS].[DETECTORSRM] e
ON i.[DETID] = e.[DETID]
WHERE (i.[CURDATETIME] >= '{}')
ORDER BY CURDATETIME DESC
""".format(strtime)
else:
# No new data
return 0
kits_data = kitsutil.data_as_dict(KITS_CREDENTIALS, kits_query)
print("Processing date/time fields")
for row in kits_data:
row["month"] = row["curdatetime"].month
row["day"] = row["curdatetime"].day
row["year"] = row["curdatetime"].year
row["day"] = row["curdatetime"].day
row["hour"] = row["curdatetime"].hour
row["minute"] = row["curdatetime"].minute
row["day_of_week"] = row["curdatetime"].weekday()
# day of week is 0 to 6 starting on monday
# shit to 0 to 6 starting on sunday
if row["day_of_week"] == 6:
row["day_of_week"] = 0
else:
row["day_of_week"] = row["day_of_week"] + 1
row["timebin"] = get_timebin(row["minute"], row["hour"])
row["direction"] = get_direction(row["detname"].upper())
kits_data = datautil.replace_timezone(kits_data, "curdatetime")
kits_data = datautil.iso_to_unix(kits_data, ["curdatetime"])
kits_data = datautil.stringify_key_values(kits_data)
hash_fields = ["detid", "curdatetime", "detname"]
for row in kits_data:
hasher = hashlib.md5()
in_str = "".join([str(row[q]) for q in hash_fields])
hasher.update(in_str.encode("utf-8"))
row["row_id"] = hasher.hexdigest()
kits_data = datautil.stringify_key_values(kits_data)
socrata_payload = datautil.lower_case_keys(kits_data)
status_upsert_response = socratautil.Soda(
auth=SOCRATA_CREDENTIALS,
records=socrata_payload,
resource=socrata_resource,
location_field=None,
)
return len(socrata_payload)