Investigate consistency of coord_datum_cd across all sites

[Kevin-M-Smith]

The coord_datum_cd is the latitude / longitude (horizontal) coordinate datum. How many unique coord_datum_cd values, and with what distribution are these codes, currently in use in the NWIS system?

---------------------------------
Code       Description
---------------------------------
NAD27      North American Datum of 1927
NAD83      North American Datum of 1983
OLDAK      Old Alaska (Mainland) and Aleutian Islands Datum
OLDHI      Old Hawaiian
OLDPR      Old Puerto Rico and Virgin Islands Datum
OLDSAMOA   Old American Samoa Datum
OLDGUAM    Old Guam Datum

References

http://waterdata.usgs.gov/usa/nwis/uv?codes_help

[Kevin-M-Smith]

Which coord_datum_cd values are currently in use?

postgres=# select distinct coord_datum_cd from active_sites;
 coord_datum_cd 
----------------
 NAD27
 NAD83

 WGS84
(4 rows)

How many sites use NAD27?

postgres=# select count(*) from active_sites where coord_datum_cd = 'NAD27';
 count 
-------
  6910
(1 row)

How many sites use NAD83?

postgres=# select count(*) from active_sites where coord_datum_cd = 'NAD83';
 count 
-------
  5299
(1 row)

How many sites use WGS84?

postgres=# select count(*) from active_sites where coord_datum_cd = 'WGS84';
 count 
-------
     9
(1 row)

Which sites are those?

postgres=# select site_no, station_nm from active_sites where coord_datum_cd = 'WGS84';
     site_no     |                     station_nm                     
-----------------+----------------------------------------------------
 01095503        | NASHUA RIVER, WATER STREET BRIDGE, AT CLINTON, MA
 01098499        | LAKE COCHITUATE AT FRAMINGHAM, MA
 01098500        | COCHITUATE BK BL LAKE COCHITUATE AT FRAMINGHAM, MA
 01102500        | ABERJONA RIVER AT WINCHESTER, MA
 422518071162501 | CAMBRIDGE RES., MET. STATION, NEAR LEXINGTON, MA
 09367580        | HOGBACK CANAL NEAR WATERFLOW, NM
 09327000        | FERRON CR BL MILLSITE RES & DIVS NR FERRON, UT
 03062235        | MONONGAHELA RIVER AT FLAGGY MEADOW, WV
 07176321        | Bird Creek at SH 99 at Pawhuska, Ok
(9 rows)

How many sites do not specify datum?

postgres=# select count(*) from active_sites where coord_datum_cd = '';
 count 
-------
     7
(1 row)

Which sites are those?

postgres=# select site_no, station_nm from active_sites where coord_datum_cd = '';
     site_no     |                   station_nm                    
-----------------+-------------------------------------------------
 365451093555501 | Monett 
 05331580        | MISSISSIPPI RIVER BELOW L&D 
 474309096122001 | E04D-R    149N43W18DDBACA01 OW
 02105769        | CAPE FEAR R AT LOCK 
 040871475       | WILSON PARK CREEK @ GMIA OUTFALL 
 040871476       | HOLMES AVE CRK TRIB @ GMIA OUTFALL 
 452359122454500 | DURHAM WASTEWATER TREATMENT PLANT AT DURHAM, OR
(7 rows)