View Name: VW_LOCATION
View Type: Standard
Language: English
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.
Output Columns |
||
|---|---|---|
EUID FACILITY_ID SYS_LOC_CODE LOC_NAME DATA_PROVIDER SUBFACILITY_CODE LOC_DESC LOC_TYPE LOC_PURPOSE LOC_TYPE_2 LOC_MAJOR_BASIN WITHIN_FACILITY_YN LOC_COUNTY_CODE LOC_DISTRICT_CODE LOC_STATE_CODE LOC_MINOR_BASIN CUSTOM_FIELD_1 CUSTOM_FIELD_2 CUSTOM_FIELD_3 CUSTOM_FIELD_4 CUSTOM_FIELD_5 STREAM_CODE STREAM_MILE PHASE_CODE |
REMARK_1 REMARK_2 BORE_ID START_DATE END_DATE DRILLING_METHOD GEOLOGIST SAMPLING_METHOD DRAWING_CHECKER DRAWING_CHECK_DATE DRAWING_EDITOR DRAWING_EDIT_DATE DRILLER DEPTH_TO_BEDROCK LOG_DATE BEARING PLUNGE APPROVED DRILLING_SUBCONTRACTOR ENGINEER_SUBCONTRACTOR ENGINEER ESTAB_COMPANY_CODE INSPECTOR INSPECT_SUBCONTRACTOR |
EBATCH MAP_CODE STATUS_FLAG 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) PARENT_LOC_CODE |
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.
Troubleshooting
•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
);
For "DataReader.GetFieldType(59) Returned Null" or "DataReader.GetFieldType(2) Returned Null" errors upon opening VW_LOCATION, see the DataReader.GetFieldType(59) Returned Null Error article.
IDENTIFIER
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.