View Name: VW_LOCATION
View Type: Standard
Description: VW_LOCATION combines location, coordinate, facility, geography, and well data from several tables to provide a summary view of location information. VW_LOCATION is used by most reports that contain coordinate data, including Analytical Results II, Action Level Exceedances II reports, Google Earth reports, and many others.
Output Columns: All fields are pulled directly from DT_LOCATION unless otherwise noted.
TOTAL_DEPTH (from DT_LOCATION or DT_WELL, selecting the first non-null value)
X_COORD (from DT_COORDINATE based on coord_type_code and IDENTIFIER chosen in DT_FACILITY)
Y_COORD (from DT_COORDINATE based on coord_type_code and IDENTIFIER chosen in DT_FACILITY)
UNITS (from DT_COORDINATE.COORD_UNIT)
SURF_ELEV (from DT_COORDINATE.ELEV converted to the units in VW_LOCATION.ELEV_UNIT)
ELEV_UNIT (from DT_FACILITY.ELEV_UNIT, DT_LOCATION.UNITS or DT_COORDINATE.ELEV_UNIT, selecting the first non-null value from this list)
LONGITUDE (from DT_COORDINATE, drawing from data where IDENTIFIER = 'PRIMARY' and the coord_type_code is LIKE '%LAT%LONG%')
LATITUDE (from DT_COORDINATE, drawing from data where IDENTIFIER = 'PRIMARY' and the coord_type_code is LIKE '%LAT%LONG%')
GEOGRAPHY (from DT_GEOGRAPHY, e.g. 'POINT (-78.9573618607045 35.0005828130316)' - if not available for that SYS_LOC_CODE, populates with 'NULL')
WELL_STATUS (from DT_WELL.WELL_STATUS)
Tables: DT_LOCATION, DT_FACILITY, DT_COORDINATE, DT_GEOGRAPHY, DT_WELL
Database Type: SQL
Database Schema: Standard (no custom or add-on schemas required)
Database Version: EQuIS 6.0+
Data Requirements: Numeric values in DT_COORDINATE for coordinate values
Output Type: Grid
Example Output: View the following example or download the attachment.
•If VW_LOCATION is empty or missing coordinate data, it can cause data to be omitted from reports that use VW_LOCATION. Common causes of this issue are:
onon-numeric coordinate values - use VW_COORD_NON_NUMERIC to check for non-numeric values, or
oDT_COORDINATE.COORD_TYPE_CODE and IDENTIFIER do not match DT_FACILITY.COORD_TYPE_CODE and IDENTIFIER, or
oif DT_COORDINATE.COORD_TYPE_CODE is LIKE '%LAT%LONG%' but IDENTIFIER does not equal 'PRIMARY'.
•An "Arithmetic overflow error converting varchar to data type numeric" error when running reports that use VW_LOCATION, or missing data from VW_LOCATION can also be a result of coordinate data with too many digits. Coordinate data in the X_COORD and Y_COORD fields of DT_COORDINATE (visible in VW_LOCATION) can have a maximum of nine digits to the left of the decimal, and a maximum of nine digits to the right of the decimal. Records that do not meet this condition will NOT be returned by VW_COORD_NON_NUMERIC but can be identified by running the following script in SQL Server Management Studio:
SELECT c.facility_id, c.sys_loc_code, c.coord_type_code, c.identifier, c.x_coord, c.y_coord
FROM dbo.dt_coordinate c
WHERE equis.to_number(c.x_coord) IS NOT NULL -- entry is a number
AND equis.to_number(c.y_coord) IS NOT NULL -- entry is a number
AND ( CHARINDEX('.', REPLACE(c.x_coord, '-', '') + '.') > 9 -- too many integer digits in X_COORD
OR CHARINDEX('.', REPLACE(c.y_coord, '-', '') + '.') > 9 -- too many integer digits in Y_COORD
Instructions to resolve a "DataReader.GetFieldType(59) Returned Null" or "DataReader.GetFieldType(2) Returned Null" error when attempting to open VW_LOCATION can be found here.
The IDENTIFIER is a facility-specific coordinate Identifier, for example, PRIMARY. Each facility can have a record in DT_FACILITY to indicate which Identifier should be considered for mapping facility data (i.e. PRIMARY, SECONDARY, 1, 2, HISTORIC, etc.).
In the DT_COORDINATE table, the IDENTIFIER is used in conjunction with the COORD_TYPE_CODE to indicate unique coordinate systems for each facility. A facility may contain multiple coordinates for each location in DT_LOCATION. For example, a location may have coordinates in LAT LONG and in STATE PLANE. If a location had more than one set of coordinates in LAT LONG, the IDENTIFIER could be used to make these unique. An IDENTIFIER of PRIMARY could be used for the LAT LONG coordinates that were most recently surveyed. An IDENTIFIER of SECONDARY could be used to indicate that alternate LAT LONG values were from another source (i.e. HISTORIC) and not to be used for mapping. The VW_LOCATION Toolbar GUI is used to indicate which COORD_TYPE_CODE and IDENTIFIER is used for third party mapping applications.
Copyright © 2020 EarthSoft, Inc • Modified: 14 Aug 2020