<< Click to Display Table of Contents >> Navigation:  Professional > Tables & Views > Data Tables (DT) > Spatial Data Tables

•DT_COORDINATE

•DT_GEOGRAPHY

•DT_MEASURE_DATUM and the EQUIS.MEASURE_DATUM... Functions

•DT_SPATIAL_EXTENT

•FAQs

 

This article discusses the following tables: DT_COORDINATE, DT_GEOGRAPHY, DT_MEASURE_DATUM, and DT_SPATIAL_EXTENT. The general purpose of each of these tables is to store spatial data (coordinates and elevation). For further reading on spatial references, see RT_COORD_TYPE.ESRI_SPATIAL_REF.

 

DT_COORDINATE

 

The DT_COORDINATE table has existed in the EQuIS data structure for many years and is the primary table for storing location coordinates. Some points to consider regarding this table:

•Only points may be stored (X_COORD, Y_COORD, and ELEV). Other spatial data types cannot (easily) be stored.

•This table is a child of DT_LOCATION, which means that only locations may have coordinates (i.e. cannot associate coordinates with facilities, samples, etc.).

•The primary key includes COORD_TYPE_CODE and IDENTIFIER, which means that more than one coordinate per location is possible, including:

oCoordinates of different types (e.g., lat/long, state plane, and UTM).

oDifferent coordinates of the same type (e.g., planned, actual/observed, resurvey).

•The X_COORD and Y_COORD fields are VARCHAR (not numeric) so that:

oSignificant digits are preserved in numeric values.

oNon-numeric coordinate values (such as township and range) may be stored.

•The VW_LOCATION view includes coordinates from DT_COORDINATE, including X_COORD, Y_COORD, LONGITUDE, and LATITUDE.

 

 

DT_GEOGRAPHY

 

DT_GEOGRAPHY improves upon both DT_SPATIAL_EXTENT and DT_COORDINATE. DT_GEOGRAPHY was introduced primarily to take advantage of the spatial data types (and query functionality**) available in Microsoft SQL Server. Another objective of this table is to provide spatial context for any other item in the database (not just locations or survey observations). Some points to consider regarding this table:

•This table is neither a child or a parent of any other table; the OBJECT_EUID field makes it possible to implicitly associate geography/spatial context with any other record in the database via its EQuIS Unique Identifier (EUID).

•This table does not include separate X_COORD, Y_COORD, and ELEV columns; instead, it uses the GEOGRAPHY spatial data type provided by the Microsoft SQL Server.

oUsing the native spatial data type, this table is not limited to storing just points; it may also store polygons, lines, etc.

•This table does not contain a COORD_TYPE_CODE column. Spatial queries are more efficient when all data is stored consistently; EQuIS will store spatial information in latitude/longitude (specifically, Spatial Reference ID = 4326).

•OBJECT_EUID may refer to any other record in any other table; as a result, it is not possible to look at a record in DT_GEOGRAPHY and know what that record is associated with (facility, location, sample, etc.). Instead, do the following:

ostart with the item of interest (facility, location, sample, etc.) and then find its associated geography record, or

orun the equis.find_euid stored procedure for that EUID value in SQL Server Management Studio (SSMS) to find the associated record

•When tying a Geography record in an EDD to an existing record in the database, the existing database record must be recreated in the EDD. The EUID value from the recreated record in the EDD should then be populated in the OBJECT_EUID field of the Geography section record.

•When entering coordinate values, DT_GEOGRAPHY.GEOGRAPHY expects the longitude value to come first and the latitude value to come second.

oFor example, a point at latitude 38.889484 and longitude -77.035278 would be written as POINT (-77.035278 38.889484).

•Values that do not fit within the bounds of the latitude-longitude coordinate type (e.g, >|180|) will fail to save.

 

**Future builds of EQuIS may utilize the spatial query functionality, including distance and buffer queries.

 

 

 

DT_MEASURE_DATUM and the EQUIS.MEASURE_DATUM... Functions

 

The DT_MEASURE_DATUM table stores datum elevations by location, date, and (in EQuIS 7.24.2+) type. Reports can use these values to calculate elevations from measurements such as water levels. The table can also store other datum changes over time.

 

There are two types of datum changes, step and linear, as defined in the STEP_OR_LINEAR column:

1.Step changes are sudden; a date prior to a measurement will use the previous measurement, and a date following a measurement will use that measurement.

2.Linear changes are gradual, and a date between two measurements will use an interpolated measurement.

 

For more information, see Introduction to Water Level Reports.

 

Previously, records were only distinguished by facility, location, and date, which could conflict with datum records needed for water level measurements. EQuIS 7.24.2 adds support for multiple measure datum types via the following:

•MEASURE_DATUM_TYPE – a new required field to the table, to differentiate between different types of datum changes (e.g., well elevation/height, equipment elevation/height), using a default value of "MEASURE_DATUM"

•the addition of MEASURE_DATUM_TYPE to the primary key, allowing records for the same SYS_LOC_CODE and MEASUREMENT_DATE to be stored as long as the MEASURE_DATUM_TYPE differs

•equis.measure_datum_2 – a new database function, including a variable for @measure_datum_type

•RT_MEASURE_DATUM_TYPE – a new Reference Table

 

Water Levels II reports use the following database functions (depending on parameters selected) to determine the appropriate reference elevation (i.e. MEASURE_DATUM) for a given location on the given day:

•equis.measure_datum(@facility_id, @sys_loc_code, @measurement_date), using the default "MEASURE_DATUM" measure datum type

•equis.measure_datum_2(@facility_id, @sys_loc_code, @measurement_date, @measure_datum_type)

 

Each row returned displays the reference elevation for any given day.

 

Currently, MEASURE_DATUM_TYPE cannot be included as an Additional Field in Water Levels II output. This behavior will be addressed in a future build of EQuIS.

 

DT_SPATIAL_EXTENT

 

EQuIS Alive focuses on surveys of living things and their habitats. Each survey contains one or more observations. While some surveys are designed to observe wildlife at specific (planned) locations, many survey observations are ad hoc. For example, a turtle or a moose is typically observed wherever it happens to be, not at a previously specified and surveyed location. Unlike ongoing groundwater monitoring samples (where samples are taken at the same locations over time), the same wildlife at the same locations time after time, are rarely observed. Given the different nature of EQuIS Alive surveys, the DT_COORDINATE table (as a child of DT_LOCATION) is not a good fit for the spatial data associated with surveys and observations.

 

The DT_SPATIAL_EXTENT table was introduced to support the different spatial data needs of surveys and observations. Some points to consider regarding this table:

•This table is a parent of DT_SURVEY and DT_SURVEY_OBSERV, so either surveys or observations may have spatial extents.

•Only points may be stored (X_COORD, Y_COORD, and Z_COORD). Other spatial data types cannot be stored.

•The X_COORD and Y_COORD fields are VARCHAR (not numeric), so that:

oSignificant digits are preserved in numeric values.

oNon-numeric coordinate values (such as township and range) may be stored.

 

FAQs

 

Q: Why are multiple different tables serving essentially the same purpose (spatial data)?

A: Each of the tables evolved separately and meet different objectives. However, they do all serve similar purposes. Depending on actual usage and client input, one or more of the tables may be phased out in future releases.

--------------------------------------------------------------

Q: Does Alive require DT_GEOGRAPHY?

A: No. Alive still uses DT_SPATIAL_EXTENT, although DT_GEOGRAPHY can also be used.

--------------------------------------------------------------

Q: It looks like DT_SPATIAL_EXTENT is an upgrade to DT_COORDINATE, and that DT_GEOGRAPHY is an upgrade to DT_SPATIAL_EXTENT. Is there any intention for DT_GEOGRAPHY to replace both DT_SPATIAL_EXTENT and DT_COORDINATE?

A: We have no immediate plans to remove any of the tables. Depending on actual usage and client input, one or more of the tables may be phased out in future releases.

--------------------------------------------------------------

Q: What is a EUID?

A: EUID stands for EQuIS Unique Identifier.

--------------------------------------------------------------

Q: In DT_GEOGRAPHY, what is the difference between the EUID and the object EUID?

A: EUID is the unique identifier of the geography record itself. Because DT_GEOGRAPHY's primary key is GEOGRAPHY_ID (a EUID), the EUID column is actually a computed column that shows the same value as GEOGRAPHY_ID. OBJECT_EUID is the EUID of the record with which the geography data is associated. The OBJECT_EUID is the EUID in some other table in the database (such as in DT_FACILITY, DT_LOCATION, or DT_SAMPLE).