Live Open Data Location Scout

The Open Data Location Scout is an agent available in EQuIS Live used for finding open data locations near a site, or latitude and longitude coordinates.

Currently, it finds locations for the following open data sources:

1.NOAA NCDC – from NOAA's isd-history.csv.

2.USGS Water Services – from USGS's Site Inventory for the Nation.

3.Weather Canada – from Weather Canada's swob-xml_station_list.csv (only contains currently active weather stations).

Note: This agent does NOT add DT_LOGGER rows for the open data locations. To get data for a location, you must add a DT_LOGGER row manually, by following the steps in the open data source's help page. For example, see the Adding Station and Loading Data sections in NOAA NCDC.

Parameters

Parameter	Description
Location Type	Find stations near: 1.Site – The Latitude and Longitude coordinates of a site that you select. 2.Latitude and Longitude – The Latitude and Longitude coordinates that you enter (below).
Site	If Location Type = Site, select a Site from this list. The Site list contains all of the current facility's SYS_LOC_CODEs that have (non-NULL) latitudes and longitudes.
Latitude	If Location Type = Latitude and Longitude, enter coordinates. For example, the San Francisco International Airport, enter 37.618889 (Decimal Degrees), or 37°37'08"N (Degrees Minutes Seconds).
Longitude	If Location Type = Latitude and Longitude, enter coordinates. For example, the San Francisco International Airport, enter -122.375 (Decimal Degrees), or 122°22'30"W (Degrees Minutes Seconds).
Latitude / Longitude Unit	If Location Type = Latitude and Longitude, select the appropriate units to match entered values (i.e., Decimal Degrees or Degrees Minutes Seconds).
Distance	Find stations within a search radius of this distance.
Distance Unit	Select the appropriate units to match entered distance (e.g., kilometers, miles).
Open Data Sources	Check the open data sources to search.
SAVE locations to database?	Check this box to save the locations to the database, or un-check it to display the locations without saving them. Note: The agent always starts by getting the existing open data locations from the database, even if this setting is false.
Group locations by	If SAVE locations to database is true, check one of the following location grouping options: 1.Multiple DT_FACILITIES (one per open data source): Locations are added to multiple DT_FACILITY rows; one per open data source (e.g., a row with DT_FACILITY.FACILITY_NAME = EQuIS Live Open Data: NOAA NCDC). 2.One DT_FACILITY and multiple RT_GROUPS (one per open data source): Locations are added to one DT_FACILITY row, with DT_FACILITY.FACILITY_NAME = EQuIS Live Open Data, with one RT_GROUP row per open data source (e.g., a row with RT_GROUP.GROUP_DESC = EQuIS Live Open Data: NOAA NCDC). 3.One DT_FACILITY and no RT_GROUPS: Locations are added to one DT_FACILITY row, with DT_FACILITY.FACILITY_NAME = EQuIS Live Open Data. Note: A location's SYS_LOC_CODE always starts with the open data source name (e.g. NOAA, USGS, or WC), Thus, multiple DT_FACILITY and/or RT_GROUP rows are not needed to determine which open data source the location belongs.
Update existing locations?	Check this box to update any existing database location info (e.g., a station's name or a station's data end date) if it is different from what the Open Data Source is currently reporting. Note: This setting does not DELETE existing locations from the database. For Example, if you want to ensure that the location information does not contain older sites that are no longer in use, you must delete the locations from the database manually, before you run this agent.
Start Date	Find stations active AFTER this date. If a station does not provide start and end dates, it is assumed to be active.
End Date	Find stations active BEFORE this date. If a station does not provide start and end dates, it is assumed to be active.

Note: The 'SAVE locations to database?' parameter is initially unchecked; it will show the locations, but not save them to the database.

Tip: EQuIS Enterprise's Map Widget allows you to view the locations that this agent saves to the database.

Database

If the locations are saved to the database, use the following SQL to view and/or delete the locations.

Example: View Locations

-- Get all 'EQuIS Live Open Data%' dt_facility.facility_ids.

DECLARE @facility_ids TABLE (id INT);

INSERT INTO @facility_ids SELECT facility_id FROM dt_facility WHERE facility_name LIKE 'EQuIS Live Open Data%';

-- Uncomment the following lines to display all of the location info.

--SELECT * FROM rt_coord_type WHERE coord_type_code LIKE '%LAT%LONG%';

--SELECT * FROM dt_facility WHERE facility_id IN (SELECT id FROM @facility_ids);

--SELECT * FROM rt_group WHERE facility_id IN (SELECT id FROM @facility_ids);

--SELECT * FROM rt_group_member WHERE facility_id IN (SELECT id FROM @facility_ids);

--SELECT * FROM dt_location WHERE facility_id IN (SELECT id FROM @facility_ids);

--SELECT * FROM dt_coordinate WHERE facility_id IN (SELECT id FROM @facility_ids);

-- The vw_location view contains a summary of the dt_location and dt_coordinate tables.

SELECT facility_id, sys_loc_code, loc_desc, latitude, longitude, surf_elev, elev_unit, start_date, end_date, custom_field_1, status_flag FROM vw_location WHERE facility_id IN (SELECT id FROM @facility_ids);

Example: Delete Locations

-- Get all 'EQuIS Live Open Data%' dt_facility.facility_ids.

DECLARE @facility_ids TABLE (id INT);

INSERT INTO @facility_ids SELECT facility_id FROM dt_facility WHERE facility_name LIKE 'EQuIS Live Open Data%';

DELETE FROM dt_coordinate WHERE facility_id IN (SELECT id FROM @facility_ids);

DELETE FROM dt_location WHERE facility_id IN (SELECT id FROM @facility_ids);

DELETE FROM rt_group_member WHERE facility_id IN (SELECT id FROM @facility_ids);

DELETE FROM rt_group WHERE facility_id IN (SELECT id FROM @facility_ids);

DELETE FROM dt_facility WHERE facility_id IN (SELECT id FROM @facility_ids);