Jump to main content.


EF/Locational Information Logo

Refresh Process

Input to Process: LRT Return File submission
First set of steps (performed on Return file records):

  1. Data Validation
    • Validation processes are executed against files and records as they are applied to LRT.
    • Data of Program systems, regions and states can be loaded into LRT with the following constraints:

      - Files should conform to the Return File Format. Files provided in other formats will need to be provided with its file format to enable the file to be converted to the Return File Format.

      - Records with pgm_sys_acrnm value of 'CERCLIS' can be loaded into LRT only if OSWER provides them. Records with pgm_sys_acrnm value of 'CERCLIS' that were not provided by OSWER can be loaded into LRT only if the pgm_sys_acrnm and pgm_sys_id values are changed to correspond to another Program ID.

      - Records should contain code values that already exist in LRT. Records with new code values can be loaded into LRT if the code values are changed to existing code values or if the new value is loaded into LRT and EDR beforehand.

  2. Verification
    • Automated verification checks are executed against each record in LRT.
    • The automated verification checks compare the reported location to the reported state, county and postal code. The location is compared against the following geometries using Oracle Spatial:
      - bounding box of the reported state-county
      - polygon of the reported state-county
      - bounding box of the reported postal code
      - polygon of the reported postal code
    • Passing an automated verification check will reduce the MOD_SCORE value of the record. The Pick Best Process is more likely to return a record with a lower MOD_SCORE value than a higher mod_score value.
    • Manual verification checks are executed as necessary.
    • Currently, there is a single manual verification check. Records with the manual verification check will always be identified through the Pick Best Process.

  3. MOD SCORE Calculation
    • MOD_SCORE calculations are executed for each record in LRT.
    • MOD_SCORE calculations are based on collection method, scale and verification checks. Note that MOD_SCORE calculations do not consider reported accuracy value.
    • MOD_SCORE is considered during the Pick Best Process. The Pick Best Process is more likely to return a record with a lower MOD_SCORE value than a higher MOD_SCORE value.

  4. PREFERRED FLAG Designation
    • Preferred_flag calculation is executed for every record in LRT.
    • Preferred_flag identfies the best locational data for a Program ID, as identified through the Pick Best Process.
    • Preferred_flag values can be 'Y' or 'N'. 'Y' indicates that the record was returned through the Pick Best Process. 'N' indicates that the record was not returned through the Pick Best Process.

    Source tables to feed the previous process are:

  5. COMPLIANT FLAG
    • Compliant_flag calculation is executed for every record in LRT.
    • Compliant_flag identifies records that have complete MAD documentation of the following data:
      - ACCURACY_VALUE
      - SOURCE_CODE
      - HDATUM_CODE
      - REF_POINT_CODE
      - COLLECTION_DATE
      - COLLECT_MTH_CODE
      - SCALE
    • Note that SCALE is not required for the following COLLECT_MTH_CODE values: '012', '013', '014', '015', '016', '017', '028', '029'.
    • Compliant_flag values can be 'Y' or 'N'. 'Y' indicates that the record has complete MAD documentation. 'N' indicates that the record does not have complete MAD documentation.

Table resulting from first set of steps: LRT_LOC_REF

Second set of steps (performed on records in table LRT_LOC_REF):

  1. Load LRT_BEST_COORDINATE
    • For each registry_id of FRS_FACILITY_SITE, obtain the best locational data, best name and address data, and overlapping state-county FIPS code, CATUNIT and postal code.
    • Identify all Program ID's from FRS_INTEREST for the registry_id. For each program ID, identify a record from LRT where preferred_flag is 'Y' and add it to a temporary group. Apply the Pick Best Process to the temporary group. Obtain lrt_loc_ref_id, pgm_sys_acrnm, pgm_sys_id, latitude, longitude, geometry, conveyor from LRT for the record returned through the Pick Best Process.
    • Obtain primary_name, location_address, city_name, county_name, state_code, postal_code for the registry_id where priority is '0001'. Priority value of '0001' indicates the name and address data is recommended.
    • Obtain the state-county FIPS code, CATUNIT and postal code based on spatial relationships to the geometry value.
    • Insert into LRT_BEST_COORDINATE values for registry_id, lrt_loc_ref_id, pgm_sys_acrnm, pgm_sys_id, latitude, longitude, points, conveyor, primary_name, location_address, city_name, county_name, state_code, postal_code, derived_fips_code, derived_catunit, derived_postal_code.

Source tables to feed the previous process:

Table resulting from second set of steps: LRT_BEST_COORDINATE

Third set of steps (performed on records in table LRT_BEST_COORDINATE):

  1. Load LRT_EF_COVERAGE_SRC
    • Load LRT_EF_COVERAGE_SRC with data from LRT_BEST_COORDINATE, LRT and FRS.
    • Obtain the following facility-level data from LRT_BEST_COORDINATE: lrt_loc_ref_id, pgm_sys_acrnm, pgm_sys_id, latitude, longitude, primary_name, derived_fips_code, derived_catunit, derived_postal_code.
    • Obtain the following program-level data from FRS and LRT where pgm_sys_acrnm exists in LDIP_CODE_LK: pgm_sys_acrnm, pgm_sys_id, lrt_loc_ref_id, latitude, longitude, conveyor.
    • Determine ldip_code based on pgm_sys_acrnm and pgm_sys_id.
    • Determine mad_symbol_code based on ldip_code values and number of Program IDs for the facility_uin in LRT_EF_COVERAGE_SRC.
    • All records in LRT_EF_COVERAGE_SRC except 'CERCLIS' borrow data from LRT_BEST_COORDINATE for the following columns: latitude, longitude, derived_fips_code, derived_catunit, derived_postal_code.
    • All records in LRT_EF_COVERAGE_SRC that except 'CERCLIS' have no acceptable LRT data will borrow data from LRT_BEST_COORDINATE for the following columns: lrt_loc_ref_id, source_acrnm, source_id,

Source tables to feed the previous process:

Table resulting from second set of steps: LRT_EF_COVERAGE_SRC

Fourth set of steps (performed on records in table LRT_EF_COVERAGE_SRC):

  1. Geospatial Enabling
    • After all LRT records are processed, geospatial layers are generated in Oracle Spatial and ArcSDE.
    • These layers are subsequently stored in the Integrated Geospatial Database, and are used in geospatial applications such as Window to My Environment.
    • Can store multi-dimensional features (lines, polygons) in LRT.

Tables resulting from third set of steps: ArcSDE and Oracle Spatial layers

To return to the Refresh Process Diagram, click here.


Local Navigation


Jump to main content.