SSSD to NASIS Conversion Issues

Flooding

It has been found that during the conversion from SSSD to NASIS that where no beginning and ending months for flooding were listed for “rare” flooding frequency in SSSD, the frequency was posted as “none” in NASIS. This occurred due to the fact that since no months were listed in SSSD, there was no way for the conversion to populate the information by month in the NASIS Component Month table.

This is an unfortunate oversight in developing of the conversion logic. It is also unfortunate that there does not appear to be any quick, easy fix or correction once on the NASIS side.

Suggested Corrections:

A. If data has not been converted to NASIS, you can populate “anflobeg” and “anfloend” months for those component with “rare” flooding in SSSD. This data will then convert into the NASIS structure properly.

B. If data is already converted to NASIS, and you still have the SSSD database available, you may want to query SSSD to identify those components that have “rare” flooding posted. This will give you the surveys and mapunits in which these components occur. This information will assist you in identifying these same components in NASIS.

Various queries can be written to select the desired data from the NASIS database for editing. Global editing will be helpful, but remember that building your selected set is VERY important.

1. The following query may be used to select components that need fixing:
   
   Query Name: Comp by flding freq & interp restrict
   
   Description: Use this query to select components in which flooding frequency is none and the interpretation restriction is flooding for a specified interpretation. The target table is component, interp kind is your choice (but it should be one in which you would expect flooding as a restriction i.e. dwellings with basements), and interp restriction is set to flooding. This query should help you find all components in which flooding frequency in SSSD was rare, but in conversion to NASIS it was converted to none because no months were recorded. A modification of this query or another query will be needed if you wish to globally edit to correct any errors. That query will need to select only the months you wish to change from none to rare for each component. (See #2 below.)
   
   Query Text:
   FROM component
   WHERE component.component_iid IN
   (SELECT component.component_iid FROM component,
   component_month, component_interp, component_interp_restriction
   WHERE component_month.flooding_frequency_class = “none” AND
   component_interp.interpretation_kind = ? AND
   component_interp_restriction.interpretation_restriction = ? AND
   JOIN component_month TO component AND
   JOIN component TO component_interp AND
   JOIN component_interp TO component_interp_restriction GROUP BY component.component_iid
   HAVING SUM(component_month.flooding_frequency_class) = 12)
    
2. You may wish to change all components of a given name to rare flooding during the same months.
   
   The following query will select these records
   
   FROM component_month, component
   WHEREcomponent_month.month IN (?) and
   component.component_name MATCHES ? and
   JOIN component TO component_month
   
   The dialog box in the Select Manager allows you to indicate which months to select and the name of the component to be selected. Multiple months can be identified since the “IN (?)” operator is used.
   Global assign could be used if all selected components are to have the same flooding frequency in the same months.
    
3. You may wish to select certain components by map unit symbol, legend name, or soil survey area name or symbol. Queries can be written to make these selections also.

HEL Classification

It has also been brought to our attention that some states have populated the HEL classification, HEL Water, and/or HEL Wind in the SSSD database with codes in the opposite order from those defined in NASIS. This means that you may have “highly erodible” in NASIS instead of “not highly erodible”, or vice versa.

If your database has this condition, you likely will want to correct it. The following procedure should help you do this:

1. Determine if you have any “null” fields in any of these three HEL rating data elements.
   
   The following query will identify these instances. It can be edited to identify null fields in the HEL water and wind columns.
   
   FROM data_mapunit
   WHERE data_mapunit.mapunit_hel_class IS NULL
   
   You will need to make a decision whether you wish to retain these as null fields or not. If you do wish to retain them, contact one of the individuals at the bottom of this message for assistance. If you want them to be either “highly erodible” or “not highly erodible” temporarily code them to the opposite value. SAVE the changes. They will then be corrected to the correct value in the following steps. Clear the selected set.
    
2. After dealing with the existing null fields, select all data mapunits in your database that are coded to “not highly erodible”
   
   The following query will do this
   
   FROM data_mapunit
   WHERE data_mapunit.mapunit_hel_class = ?
   
   Substitute HEL water or HEL wind into this query as needed to identify the appropriate components.
    
3. The HEL class, HEL water and/or HEL wind fields in these data mapunits can be globally reset to NULL temporarily. SAVE these changes.
    
4. Select File New to clear the selected set.
    
5. Now run the query above to select those map units that are “highly erodible”. Globally reset their HEL class to “not highly erodible”. SAVE these changes.
    
6. Select File New to clear the selected set.
    
7. Now use the following query to select the data mapunits that were set to NULL in Step 3: FROM data_mapunit WHERE data_mapunit.mapunit_hel_class IS NULL
    
8. Now globally assign the HEL class of the data mapunits to “highly erodible”.

If you need assistance in writing queries to make these corrections, Jim Fortner, Paul Finnell, or the Soils Hotline staff are available to assist.