Spatial Databases: SQL Help for COMET


The following SQL statements produce the output necessary to define bounding coordinates, latitude/longitude resolution, and beginning and ending dates for a spatial database.  These fields are required under the Federal Geographic Data Committee (FGDC).  You can simply copy (Ctrl-C) and paste (Ctrl-V) these statements for your use.

Calculating Bounding Coordinates
SELECT Min(LONGITUDE) AS WEST, Max(LONGITUDE) AS EAST, Max(LATITUDE) AS NORTH, Min(LATITUDE) AS SOUTH
FROM STATIONS;

Assumptions:  The database contains a table such as STATIONS that contains points with latitude and longitude values.  Latitude and longitude are in decimal degrees with longitude being negative.  If longitude is positive, the east and west bounding coordinates should be switched.  If the table and/or station field has a different name, simply change the SQL statement to reflect the correct name(s).

Calculating latitude/longitude resolution
SELECT Min(Abs(a.LATITUDE-b.LATITUDE)) AS LAT_MIN, Min(Abs(a.LONGITUDE-b.LONGITUDE)) AS LONG_MIN
FROM STATIONS a, STATIONS b
WHERE a.STATION<>b.STATION;

Assumptions:  The database contains a table such as STATIONS that contains points with latitude and longitude values.  If the table and/or date field has a different name, simply change the SQL statement to reflect the correct name(s).

Calculating beginning and ending dates
SELECT Min(SAMPLE_DATE) AS BEGIN_DATE, Max(SAMPLE_DATE) AS END_DATE
FROM EVENT;

Assumptions:  The database has a temporal component such as SAMPLE_DATE.  There is a table such as EVENT that contains the full range of dates.  If the table and/or date field has a different name, simply change the SQL statement to reflect the correct name(s).