[UW-GIS-L] tables and shapefiles

[Steven Rentmeester]

Hi Amanda,

Wow, exciting adventure. I'm jealous, wish I could join you as your 
personal database consultant.

We have been working on similar issue here at NOAA and in the Columbia 
River Basin. We have been using the concept of a data collection event 
(DCE) as any time a researcher goes a field site and brings back data. 
To uniquely identify DCEs we simply concatenate the site and a 
date/time. So, in your case, you could use "30hydro-1967" as a unique id 
for that DCE. In Excel the code is:
= concatenate([SiteName], "-", [Date])
In Access it is: [SiteName] & "-" & [Date]

In the northwest, we use LLID to uniquely identify tributary junctions. 
The LLID is just the concatenation of the latitude and longitude rounded 
to the first 4 digits to the right of the decimal. For example: 
1210123460123 is the LLID for a trib junction at -121.01234567 and 
46.01234567
So, one approach to uniquely identifying sites that have many lat/long 
readings is to decrease the number of significant digits until the 
lat/longs define a square large enough to include all readings from a 
single site, but small enough to not include multiple sites (Same as 
Michalis suggested). This would allow you to create a unique id that is 
consistent across years.

Then you can create three tables in Access; tbl_Site, 
tbl_DataCollectionEvent, and tbl_HydrologicParameter and join them in a 
hierarchical relationship.

The other approach would be to snap all of the points to a single 
hydrography layer in ArcGIS. This would require moving each point by 
hand to the stream arc and would be considerable more tedious. Might be 
best to use something similar to LLID to uniquely identify sites. Then 
create a new point for each site as the average of all lat/long 
readings. Then if it is import to associate the data with a stream 
network, snap your new points to the hydrography layer.

Also, I have been working on developing a water quality database 
template in Access. I need clean it up a bit, but can send it to you 
later today or early next week.

take care, glad to hear you are having fun,

Steve Rentmeester
Environmental Data Services
Contractor to NOAA Fisheries
6500 N Princeton St
Portland, OR 97203
503-247-8431


Amanda Henck wrote:

> Hello from Chengdu,
>
> I am writing with kind of an unusual question, and it will take a bit 
> of explanation, so please bear with me. I am in Chengdu right now 
> working with a friend to compile hydrology records published by the 
> Chinese Hydrology Bureau. The records contain 6 types of daily data, 
> but a given station may not have all data types. They are currently in 
> year books organized (roughly) by watersheds.  My friend is typing the 
> data up into Excel spread sheets. So far, so good.
>
> the problem is, the stations don't necessarily operate continuously 
> and are renumbered in every book.  Each book has a list of hydrology 
> stations (4 data types) and weather stations (2 data types) which may 
> or may not overlap. Some hydrology stations are also weather stations, 
> some weather stations are hydrology stations, but there are hydrology 
> and weather stations which are only one type of station.
>
> Each book lists the stations in some order (probably logical, we can't 
> tell what it is though) and numbers them. 1967 could have 50 hydro 
> stations and 100 weather stations, and 1968 have 52 hydro stations and 
> 96 weather stations. So number 30hydro in 1967 could be 50 weather 
> that year and then in 1968 it is 25 hydro and 65 weather.  Each has a 
> latitude and longitude, so we can sort them that way, but it is very 
> time consuming to do it by hand (there 30 years of data in 30 books). 
> Since ultimately we want a map of the stations and what data (and 
> years) are available for each point, we are looking for a way that we 
> could input all the stations into Arc and have them consolidated into 
> a single set of data with years, stations numbers, and data types for 
> each location.
>
> (The fact that the stations occasionally move or the lat-long are 
> mistyped or changed is an entirely different problem that needs to be 
> solved by my reading the chinese station names... so this question 
> about consolidating the locations is only the tip of the ice berg :))
>
> IF anyone knows of a way to do this station consolidating in Arc (or 
> Excel, Access, Word, Notepad, Photoshop, anything!), could you please 
> help!
>
> thanks!
>
> amanda henck (and stephen frister)