VW_LOCATION

<< Click to Display Table of Contents >>

Navigation:  EQuIS Library > Views >

VW_LOCATION

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.

 

Troubleshooting

IDENTIFIER

 

Language: English

 

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.

 

VW_LOCATION

 

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

    );

 

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.

 

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.