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)