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

•DT_COORDINATE

•DT_SPATIAL_EXTENT

•DT_GEOGRAPHY

•FAQs

 

This article discusses three tables: DT_COORDINATE, DT_SPATIAL_EXTENT, and DT_GEOGRAPHY. The general purpose of each of these tables is to store spatial data (e.g. coordinates).

 

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_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 dwherever 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.

 

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 spatial data type provided by the Microsoft SQL Server: GEOGRAPHY database.

•Using 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.

•OBJECT_EUID may refer to any other record in any other table, and 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, it is necessary to start with the item of interest (facility, location, sample, etc.) and then find its associated geography record.

 

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

 

 

FAQs

 

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

 

A: Each of the three 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).