Ingres-add-site

From PTAGISWiki

Procedure for updating fields in an Ingres database on sockeye. 3/2/05

[rday@snapper rday]$ rsh sockeye Last login: Tue Mar 1 14:00:48 from snapper.psmfc.or bash-2.03$ su - ptagdev Password: sockeye:ptagdev > tv TERM_INGRES = vt100fx sockeye:ptagdev >

Before going any further, here is the current goal: Add site PRO to juvenile and adult interrogation using PRJ site as a template. Here's what is known about PRJ: site code: PRJ site id: PRJ activity category: remote activity code: interrogation activity suffix: con site type: juvenile bypass site description: constructed environment locality radius: 200 rkm: 539.076 huc: 17030003 loc id: 16 lat decimal: 46.23 lon decimal: -119.678 y coord: 160 x coord: 194

the site type will be either juvenile bypass or adult fish facility most other things can stay the same. rkm and huc had to be determined with sneak database queries. The data may not exist for small streams.

Browse the river segment table to determine seg id Sorting on river_km, I could look up 539.076 and find a match at Chandler Canal, thus the seg_id = 43 But it could be that the seg_id is 279 for the Yakima river. Have to ask Dave.

That should be all the data necessary to add the site PRO adult and PRO juvenile. Now we can continue with the QBF session on sockeye...

In the case of PRO, a site already exists (with loc_id 16) so I just have to add the record to location_in_segment table.

Actually, location_in_segment requires these additional data: FIELD PRJ PROSRD General locality site km in reach 0 0 km total 0 0 category juvenile tag rkm fixed site Y N Y fixed site Y N Y Marking only Y N N Entire river Y N N N Release Act Suffix Y N N N Archaic Y N N N

Therefore, the fields that I need to talk to Dave about are: category (I assume I will add a row for juvenile and a row for adult at PRO) rkm fixed site YN (I just don't know what this is) fixed site Y N (I assume this is a Y for all the dams) marking only Y N (I have to ask what activity is going on for the given sites)

Next, I need to determine the seg_id by looking in the river_segment table I don't really know what river Prosser Dam is on, and the existing entries for PRJ and PROSRD don't know either. Time to google.

Prosser is on the Yakima river. Is that enough info? No. There are two segments to the Yakima divided at Naches River. I'll ask Dave. The seg_id will be either 279 for Yakim1 or 280 for Yakim2.

Next, I need to find the proper enumeration for site_type. I believe I'll be using "juvenile bypass" and "adult fish facility", but I'll verify with Dave.

I think that's all I need.

In summary, for Prosser juvenile, I need to gather the following info:

site code: PRO site id: PRO activity category: remote activity code: interrogation activity suffix: con site type: juvenile bypass site description: constructed environment locality radius: 200 rkm: 539.076 huc: 17030003 loc id: 16 lat decimal: 46.23 lon decimal: -119.678 y coord: 160 x coord: 194 seg_id: 43 or 279? general locality site: ? km in reach: ? km total: ? category: juvenile? rkm fixed site Y N: Y? fixed site Y N: Y? Marking only Y N: N? Entire river Y N: N? Release Act Suffix Y N: N? Archaic Y N: N?

Based on data in the old RKM table, PRJ is in HUC 17030003. That means that it is in river segment Yakim1 and the seg_id is 279.

====================

locality_radius for juvenile should be 10 meters for PRO juvenile PRO adult and juvenile are on Yakima: seg_id = 279

general locality site: ask Doug km in reach and km total: use values for PROSRD category: ask Doug rkm fixed site: ask Doug fixed site: Y marking only: N entire river: ask Doug release act suffix: ask Doug archaic: N

Might have to modify PRJ to mark as archaic.

for ICH use data from IHA for B04 use data from B03

The answer to the question, "what does field X mean?" can be answered through the data dictionary metadata fields. The specific answers from the "G2 Proposed Columns for ALRS" PDF are: general locality site is the parent site for a group of related sites. not relevent here. category is taken from my old spreadsheet, based on obs_site_type this corresponds to the categories displayed in the flash map rkm fixed site corresponds to rkm table (breaking down overloaded field) entire river means site does or does not include the entire river release act suffix indicates whether or not an activity suffix is required. corresponds to a D in the fixed site yn in rkm for dams.

general locality site: blank km in reach: ask Dave km total: 615 (from rkm) category: adult, juvenile, tag, or sbyc rkm fixed site: ask Dave fixed site: Y marking only: N entire river: N release act suffix: ask Dave archaic: N

====================

Time for another summary. These fields are required to add a new site on an existing river segment:

site code: PRO site id: PRO activity category: remote activity code: interrogation activity suffix: con site type: JUVENILE BYPASS or ADULT FISH FACILITY site description: constructed environment locality radius: 10 rkm: 539.076 huc: 17030003 loc id: 16 lat decimal: 46.23 lon decimal: -119.678 y coord: 160 x coord: 194 seg_id: 279 general locality site: blank km in reach: 0 km total: 615 category: juvenile and adult rkm fixed site Y N: Y fixed site Y N: Y Marking only Y N: N Entire river Y N: N Release Act Suffix Y N: blank Archaic Y N: N

Now, the steps are to add a row to location table and a row to location_in_segment table. The location row for Prosser Dam already exists with loc_id 16.

Here are the fields that are required for the location_in_segment table:

site ID: Loc ID Seg ID Site type site description Locality radius general locality site km in reach km total category rkm fixed site yn fixed site yn marking only yn entire river yn release act suffix yn archaic yn

Tried to add PRO juvenile and PRO adult to location_in_segment and got a duplicate key error. Should I be adding to activity_location instead?

Worked with Doug to get a new Site Type called HYBRID that allows both adult and juvenile activities.

TODO: Fix the perl that generates flash XML to appropriately handle the HYBRID site type.

Updated PRO to be a HYBRID site type.

Now I need to add two rows to activity_location table. The fields needed for this table are:

site code: PRO site ID: PRO activity_category (hands-on or remote): REMOTE activity_code (tagging, release, interrogation, recapture): INTERROGATION activity_suffix (non, tal, con, etc): CON contact_id (-1 if not known): -1 activity_loc_description: Dummy description from_km: 0 to_km: 0 from_date (from obs_site_dates): 2/22/99 to_date: blank

Now PRO adult and juvenile is in the ALRS db.

Here are the tables and fields I needed to populate:

location loc_id location_name lat_decimal lon_decimal lat_lon_source lat_estimate lon_estimate est_lat_lon_source elevations_msl y_coord x_coord lat_err lon_err est_piy est_pix y_err x_err modification_date modification_comment location_in_segment site_id loc_id seg_id site_type site_description locality_radius general_locality_site km_in_reach km_total category rkm_fixed_site_y_n fixed_site_y_n marking_only_y_n entire_river_y_n release_act_suffix_y_n archaic_y_n activity_location site_code site_id activity_category activity_code activity_suffix activity_loc_description from_km to_km from_date to_date

In the instance of Prosser juvenile, the location was already established (Prosser dam). A row for PRO was added to location_in_segment. A row for PRO was added to activity_location.