EQuIS 6 - DT_GEOGRAPHY
  • RSS Feed

Last modified on 5/22/2017 5:59 PM by User.

EQuIS 6 - DT_GEOGRAPHY

Keywords: DT_GEOGRAPHY, DT_COORDINATE, DT_SPATIAL_EXTENT, hnm 15458

This article discusses three tables: DT_COORDINATE, DT_SPATIAL_EXTENT, and a table that is new in EQuIS 6, 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:

  • You may only store points (aka X_COORD, Y_COORD, and ELEV); you cannot (easily) store other spatial data types
  • This table is a child of DT_LOCATION, which means that only locations may have coordinates (e.g. you cannot associate coordinates with facilities, samples, etc.)
  • The primary key includes COORD_TYPE_CODE and IDENTIFIER, which means that you can have more than one coordinate per location, including:
    • coordinates of different types (e.g. lat/long, state plane, and UTM)
    • different coordinates of the same type (e.g. planned, actual/observed, re-survey)
  • The X_COORD and Y_COORD fields are VARCHAR (not numeric), so that:
    • significant digits are preserved in numeric values
    • non-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, 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, you typically observe a turtle or a moose wherever it happens to be, not at a previously specified and surveyed location. Unlike ongoing groundwater monitoring samples (where you sample at the same locations over time), you rarely observe the same wildlife at the same locations time after time. 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
  • You may only store points (aka X_COORD, Y_COORD, and Z_COORD); you cannot store other spatial data types
  • The X_COORD and Y_COORD fields are VARCHAR (not numeric), so that:
    • significant digits are preserved in numeric values
    • non-numeric coordinate values (such as township and range) may be stored

DT_GEOGRAPHY

DT_GEOGRAPHY is a new table in EQuIS 6, which 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**) now available in Microsoft SQL Server and Oracle Database (aka Oracle Locator). 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 EUID
  • This table does NOT include separate X_COORD, Y_COORD, and ELEV columns; instead, it uses the spatial data type provided by the 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
  • Because OBJECT_EUID may refer to any other record in any other table, you cannot look at a record in DT_GEOGRAPHY and know what that record is associated with (facility, location, sample, etc.); Instead, you must start with the item of interest (facility, location, sample, etc.) and then find its associated geography record.

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

FAQs

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

A: As explained above, 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: For Alive, is DT_GEOGRAPHY required?

A: No. Alive still uses DT_SPATIAL_EXTENT, but you can use DT_GEOGRAPHY also. While this may change in the future, essentially, both tables are currently available.

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

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 that DT_GEOGRAPHY might 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. Every table in an EQuIS 6 database has a EUID field.

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

Q: In DT_GEOGRAPHY, what is the difference in 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 (aka 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 (e.g. facility, location, sample, etc.)

Return to Top