Appendix G.31

G.31 Incorporate D_LOCATION_COORD_HIST and D_LOCATION_ELEV_HIST records into D_LOCATION_SPATIAL_HIST

Tables
- D_LOCATION
- D_LOCATION_COORD_HIST
- D_LOCATION_ELEV
- D_LOCATION_ELEV_HIST
- D_LOCATION_QA
- D_LOCATION_SPATIAL
- D_LOCATION_SPATIAL_HIST
Estimated Recurrence Time: Single database update due to methodology change.

The tables D_LOCATION_COORD_HIST and D_LOCATION_ELEV_HIST functionality is to be combined in a single table - D_LOCATION_SPATIAL_HIST - to more closely link coordinates and elevations. The original methodology, using the two disparate tables, was found to be inadequate with regard to timely (or, at all) tracking of changes in one or the other of location or elevation without the values becoming disassociated (i.e. one value no longer related to the other). If this information is found in a single table, it is thought, that can be updated with a new record resulting from any change the likelihood of the location and the elevation being unrelated should be lessened.

Current coordinates and elevation, which should be recorded in the appropriate tables, is added into the D_LOCATION_SPATIAL_HIST table. Note that the conversion of LOC_COORD_OUOM_CODE from D_LOCATION (to the appropriate EPSG code) occurs within the query. Where assumptions are made concerning this conversion (as originally found in R_LOC_COORD_OUOM_CODE), these are recorded in the EPSG_CODE_COMMENT field. All pertinent comment (or text) fields from the various tables are appended into the coordinate or elevation method/comment fields in D_LOCATION_SPATIAL_HIST (zero-length text fields are converted to NULL values - not shown here). Differing dates from the variety of tables is examined with the most relevant date used to populate each of the date fields in the new table. Note that the choice of information used should be consistent through each of the following steps.

select
dloc.loc_id
,case
when dlch.loc_coord_hist_code is not null then dlch.loc_coord_hist_code
else 1
end as loc_coord_hist_code
,case
when dlch.sys_time_stamp is not null then dlch.sys_time_stamp
when dloc.sys_last_modified is not null then dloc.sys_last_modified
else getdate()
end as loc_coord_date
,dloc.loc_coord_easting as x
,dloc.loc_coord_northing as y
,cast(26917 as int) as epsg_code
,case
-- the original coordinates in these cases are invalid
when dloc.loc_coord_ouom_code in ( 25, 29, 31, 33, 35 ) then null
else dloc.loc_coord_easting_ouom 
end as x_ouom
,case 
-- the original coordinates in these cases are invalid
when dloc.loc_coord_ouom_code in ( 25, 29, 31, 33, 35 ) then null
else dloc.loc_coord_northing_ouom 
end as y_ouom
,case
when dloc.loc_coord_ouom_code=  1 then null
when dloc.loc_coord_ouom_code=  2 then 26717
when dloc.loc_coord_ouom_code=  3 then 26718
when dloc.loc_coord_ouom_code=  4 then 26917
when dloc.loc_coord_ouom_code=  5 then 26918
when dloc.loc_coord_ouom_code=  6 then 26910
when dloc.loc_coord_ouom_code=  7 then 26915
when dloc.loc_coord_ouom_code=  8 then 26916
when dloc.loc_coord_ouom_code=  9 then 26919
when dloc.loc_coord_ouom_code= 10 then 26917
when dloc.loc_coord_ouom_code= 11 then 26717
when dloc.loc_coord_ouom_code= 12 then 26717
when dloc.loc_coord_ouom_code= 13 then 32190
when dloc.loc_coord_ouom_code= 14 then 7991
when dloc.loc_coord_ouom_code= 15 then 32190
when dloc.loc_coord_ouom_code= 16 then 32196
when dloc.loc_coord_ouom_code= 17 then 26917
when dloc.loc_coord_ouom_code= 18 then 4267
when dloc.loc_coord_ouom_code= 19 then 4269
when dloc.loc_coord_ouom_code= 20 then 4269
when dloc.loc_coord_ouom_code= 21 then 4269
when dloc.loc_coord_ouom_code= 22 then 26917
when dloc.loc_coord_ouom_code= 23 then 26918
when dloc.loc_coord_ouom_code= 24 then 32617
when dloc.loc_coord_ouom_code= 25 then null
when dloc.loc_coord_ouom_code= 26 then 26917
when dloc.loc_coord_ouom_code= 27 then 26917
when dloc.loc_coord_ouom_code= 29 then null
when dloc.loc_coord_ouom_code= 31 then null
when dloc.loc_coord_ouom_code= 33 then null
when dloc.loc_coord_ouom_code= 35 then null
when dloc.loc_coord_ouom_code= 36 then 26917
else null
end as epsg_code_orig
,case
when dloc.loc_coord_ouom_code= 10 then 'UTM NAD83 specified; Z17 assumed'
when dloc.loc_coord_ouom_code= 11 then 'UTM NAD27 specified; Z17 assumed'
when dloc.loc_coord_ouom_code= 12 then 'UTM Z17 specified; NAD27 assumed'
when dloc.loc_coord_ouom_code= 13 then 'MTM specified; Z10 and NAD83 assumed'
when dloc.loc_coord_ouom_code= 20 then 'Lat/Long, Z17 specified; NAD83 assumed'
when dloc.loc_coord_ouom_code= 21 then 'Lat/Long specified; NAD83 assumed'
when dloc.loc_coord_ouom_code= 22 then 'Z17 specified; UTM and NAD83 assumed'
when dloc.loc_coord_ouom_code= 23 then 'Z18 specified; UTM and NAD83 assumed'
when dloc.loc_coord_ouom_code= 26 then 'EPSG_CODE 26911 specified; 26917 assumed'
when dloc.loc_coord_ouom_code= 27 then 'EPSG_CODE 26902 specified; 26917 assumed'
when dloc.loc_coord_ouom_code= 36 then 'UTM Z17 and NAD83 assumed'
else null
end as epsg_code_comment
,dlqa.qa_coord_confidence_code as qa_coord_code
,case
when dlqa.qa_coord_method is null then ''
else rtrim(dlqa.qa_coord_method) + '; '
end
+ 
case
when dlch.coord_hist_loc_method is null then ''
else rtrim(dlch.coord_hist_loc_method) + '; '
end
as loc_coord_method
,case
when dloc.loc_coord_comment is null then ''
else rtrim(dloc.loc_coord_comment) + '; '
end
+
-- dlch.coord_hist_comment
case
when dlch.coord_hist_comment is null then ''
else rtrim(dlch.coord_hist_comment) + '; '
end 
+
-- dlqa.qa_coord_source
case
when dlqa.qa_coord_source is null then ''
else rtrim(dlqa.qa_coord_source) + '; '
end
+
-- dlqa.qa_coord_comment
case
when dlqa.qa_coord_comment is null then ''
else rtrim(dlqa.qa_coord_comment) + '; '
end
as loc_coord_comment
,dlch.data_id as loc_coord_data_id
,case
when dleh.loc_elev_code is not null then dleh.loc_elev_code
else null
end as loc_elev_code
,case
when dleh.loc_elev_hist_date is not null then dleh.loc_elev_hist_date
when dleh.sys_time_stamp is not null then dleh.sys_time_stamp
else cast( null as datetime )
end as loc_elev_date
,delev.assigned_elev as loc_elev
,6 as loc_elev_unit_code
,delev.assigned_elev as loc_elev_ouom
,'masl' as loc_elev_unit_ouom
,case
when dlqa.qa_elev_confidence_code is not null then dlqa.qa_elev_confidence_code
when dleh.qa_elev_confidence_code is not null then dleh.qa_elev_confidence_code
when delev.assigned_elev is not null then 10
else null
end as qa_elev_code
,case
when dlqa.qa_elev_method is null then ''
else rtrim(dlqa.qa_elev_method) + '; '
end
as loc_elev_method
,case
when dlqa.qa_elev_source is null then ''
else rtrim(dlqa.qa_elev_source) + '; '
end 
+
case
when dlqa.qa_elev_comment is null then ''
else rtrim(dlqa.qa_elev_comment) + '; '
end
+ 
case
when dleh.elev_hist_comment is null then ''
else rtrim(dleh.elev_hist_comment) + '; '
end
as loc_elev_comment
,dleh.data_id as loc_elev_data_id
,cast('20200714a' as varchar(255)) as sys_temp1
,cast(20200714 as int) as sys_temp2
--,null as sys_last_modified_by
,case
when dleh.sys_last_modified>dloc.sys_last_modified then dleh.sys_last_modified_by
else dloc.sys_last_modified_by
end as sys_last_modified_by
--,null as sys_last_modified
,case
when dleh.sys_last_modified>dloc.sys_last_modified then dleh.sys_last_modified
else dloc.sys_last_modified
end as sys_last_modified
--,null as sys_user_stamp
,case
--when dlch.sys_user_stamp is not null then dlch.sys_user_stamp
when dloc.sys_last_modified_by is not null then dloc.sys_last_modified_by
else null
end as sys_user_stamp
--,null as sys_time_stamp
,case
when dlch.sys_time_stamp is not null then dlch.sys_time_stamp
when dloc.sys_last_modified is not null then dloc.sys_last_modified
else null
end as sys_time_stamp
from 
d_location as dloc
left outer join d_location_elev as delev
on dloc.loc_id=delev.loc_id
left outer join d_location_elev_hist as dleh
on delev.loc_elev_id=dleh.loc_elev_id
left outer join d_location_coord_hist as dlch
on dloc.loc_id=dlch.loc_id
left outer join d_location_qa as dlqa
on dloc.loc_id=dlqa.loc_id
where 
dlch.current_coord= 1

The resultant information is inserted into D_LOCATION_SPATIAL_HIST (not shown). As these are the most current coordinates, the SPAT_ID from these records are used to update D_LOCATION_SPATIAL which is used to flag the ‘current’ coordinates for a particular location.

insert into d_location_spatial
( loc_id, spat_id, spatial_comment, sys_temp1, sys_temp2 )
select
loc_id
,spat_id
,'Initial assignment 20200714'
,'20200714a' as sys_temp1
,20200714 as sys_temp2
from 
d_location_spatial_hist

During this process, it was noted that some locations had been added to which the coordinates and/or elevations had not been captured. Include these.

select
dloc.loc_id
,1 as loc_coord_hist_code
,case
when dloc.sys_last_modified is not null then dloc.sys_last_modified
else getdate()
end as loc_coord_date
,dloc.loc_coord_easting as x
,dloc.loc_coord_northing as y
,26917 as epsg_code
,dloc.loc_coord_easting_ouom as x_ouom
,dloc.loc_coord_northing_ouom as y_ouom
,case
when dloc.loc_coord_ouom_code=  4 then 26917
when dloc.loc_coord_ouom_code= 12 then 26717
when dloc.loc_coord_ouom_code= 21 then 4269
else null
end as epsg_code_orig
,case
when dloc.loc_coord_ouom_code= 12 then 'UTM Z17 specified; NAD27 assumed'
when dloc.loc_coord_ouom_code= 21 then 'Lat/Long specified; NAD83 assumed'
else null
end as epsg_code_comment
,case 
when dlqa.qa_coord_confidence_code is not null then dlqa.qa_coord_confidence_code
else 5
end 
as qa_coord_code
,case
when dlqa.qa_coord_method is null then ''
else rtrim(dlqa.qa_coord_method) + '; '
end
as loc_coord_method
,case
when dloc.loc_coord_comment is null then ''
else rtrim(dloc.loc_coord_comment) + '; '
end
+
-- dlqa.qa_coord_source
case
when dlqa.qa_coord_source is null then ''
else rtrim(dlqa.qa_coord_source) + '; '
end
+
-- dlqa.qa_coord_comment
case
when dlqa.qa_coord_comment is null then ''
else rtrim(dlqa.qa_coord_comment) + '; '
end
as loc_coord_comment
,case
when dleh.loc_elev_code is not null then dleh.loc_elev_code
else null
end as loc_elev_code
,case
when dleh.loc_elev_hist_date is not null then dleh.loc_elev_hist_date
when dleh.sys_time_stamp is not null then dleh.sys_time_stamp
else cast( null as datetime )
end as loc_elev_date
,delev.assigned_elev as loc_elev
,6 as loc_elev_unit_code
,delev.assigned_elev as loc_elev_ouom
,'masl' as loc_elev_unit_ouom
,case
when dlqa.qa_elev_confidence_code is not null then dlqa.qa_elev_confidence_code
when dleh.qa_elev_confidence_code is not null then dleh.qa_elev_confidence_code
when delev.assigned_elev is not null then 10
else null
end as qa_elev_code
,case
when dlqa.qa_elev_method is null then ''
else rtrim(dlqa.qa_elev_method) + '; '
end
as loc_elev_method
,case
when dlqa.qa_elev_source is null then ''
else rtrim(dlqa.qa_elev_source) + '; '
end 
+
case
when dlqa.qa_elev_comment is null then ''
else rtrim(dlqa.qa_elev_comment) + '; '
end
+ 
case
when dleh.elev_hist_comment is null then ''
else rtrim(dleh.elev_hist_comment) + '; '
end
as loc_elev_comment
,cast('20200714b' as varchar(255)) as sys_temp1
,cast(20200714 as int) as sys_temp2
,case
when dleh.sys_last_modified>dloc.sys_last_modified then dleh.sys_last_modified_by
else dloc.sys_last_modified_by
end as sys_last_modified_by
--,null as sys_last_modified
,case
when dleh.sys_last_modified>dloc.sys_last_modified then dleh.sys_last_modified
else dloc.sys_last_modified
end as sys_last_modified
--,null as sys_user_stamp
,case
when dloc.sys_last_modified_by is not null then dloc.sys_last_modified_by
when dloc.sys_user_stamp is not null then dloc.sys_user_stamp
else null
end as sys_user_stamp
,case
when dloc.sys_last_modified is not null then dloc.sys_last_modified
when dloc.sys_time_stamp is not null then dloc.sys_time_stamp
else null
end as sys_time_stamp
from 
d_location as dloc
left outer join d_location_qa as dlqa
on dloc.loc_id=dlqa.loc_id
left outer join d_location_elev as delev
on dloc.loc_id=delev.loc_id
left outer join d_location_elev_hist as dleh
on delev.loc_elev_id=dleh.loc_elev_id
left outer join d_location_spatial as dls
on dloc.loc_id=dls.loc_id
where 
dls.loc_id is null
and dloc.loc_coord_easting is not null
and dloc.loc_coord_northing is not null

Note that only a subset of the LOC_COORD_OUOM_CODE values were processed in this case.

Now that we’ve processed the ‘current’ coordinates, process those coordinates that have an associated elevation. This only uses D_LOCATION_COORD_HIST and D_LOCATION_ELEV_HIST as a source.

select
dlch.loc_id
,case
when dlch.loc_coord_hist_code is not null then dlch.loc_coord_hist_code
else 1
end as loc_coord_hist_code
,case
when dlch.sys_time_stamp is not null then dlch.sys_time_stamp
else getdate()
end as loc_coord_date
,dlch.x_utmz17nad83 as x
,dlch.y_utmz17nad83 as y
,cast(26917 as int) as epsg_code
,case
-- the original coordinates in these cases are invalid
when dlch.loc_coord_ouom_code in ( 25, 29, 31, 33, 35 ) then null
else dlch.x_ouom
end as x_ouom
,case 
-- the original coordinates in these cases are invalid
when dlch.loc_coord_ouom_code in ( 25, 29, 31, 33, 35 ) then null
else dlch.y_ouom
end as y_ouom
,case
when dlch.loc_coord_ouom_code=  1 then null
when dlch.loc_coord_ouom_code=  2 then 26717
when dlch.loc_coord_ouom_code=  3 then 26718
when dlch.loc_coord_ouom_code=  4 then 26917
when dlch.loc_coord_ouom_code=  5 then 26918
when dlch.loc_coord_ouom_code=  6 then 26910
when dlch.loc_coord_ouom_code=  7 then 26915
when dlch.loc_coord_ouom_code=  8 then 26916
when dlch.loc_coord_ouom_code=  9 then 26919
when dlch.loc_coord_ouom_code= 10 then 26917
when dlch.loc_coord_ouom_code= 11 then 26717
when dlch.loc_coord_ouom_code= 12 then 26717
when dlch.loc_coord_ouom_code= 13 then 32190
when dlch.loc_coord_ouom_code= 14 then 7991
when dlch.loc_coord_ouom_code= 15 then 32190
when dlch.loc_coord_ouom_code= 16 then 32196
when dlch.loc_coord_ouom_code= 17 then 26917
when dlch.loc_coord_ouom_code= 18 then 4267
when dlch.loc_coord_ouom_code= 19 then 4269
when dlch.loc_coord_ouom_code= 20 then 4269
when dlch.loc_coord_ouom_code= 21 then 4269
when dlch.loc_coord_ouom_code= 22 then 26917
when dlch.loc_coord_ouom_code= 23 then 26918
when dlch.loc_coord_ouom_code= 24 then 32617
when dlch.loc_coord_ouom_code= 25 then null
when dlch.loc_coord_ouom_code= 26 then 26917
when dlch.loc_coord_ouom_code= 27 then 26917
when dlch.loc_coord_ouom_code= 29 then null
when dlch.loc_coord_ouom_code= 31 then null
when dlch.loc_coord_ouom_code= 33 then null
when dlch.loc_coord_ouom_code= 35 then null
when dlch.loc_coord_ouom_code= 36 then 26917
else null
end as epsg_code_orig
,case
when dlch.loc_coord_ouom_code= 10 then 'UTM NAD83 specified; Z17 assumed'
when dlch.loc_coord_ouom_code= 11 then 'UTM NAD27 specified; Z17 assumed'
when dlch.loc_coord_ouom_code= 12 then 'UTM Z17 specified; NAD27 assumed'
when dlch.loc_coord_ouom_code= 13 then 'MTM specified; Z10 and NAD83 assumed'
when dlch.loc_coord_ouom_code= 20 then 'Lat/Long, Z17 specified; NAD83 assumed'
when dlch.loc_coord_ouom_code= 21 then 'Lat/Long specified; NAD83 assumed'
when dlch.loc_coord_ouom_code= 22 then 'Z17 specified; UTM and NAD83 assumed'
when dlch.loc_coord_ouom_code= 23 then 'Z18 specified; UTM and NAD83 assumed'
when dlch.loc_coord_ouom_code= 26 then 'EPSG_CODE 26911 specified; 26917 assumed'
when dlch.loc_coord_ouom_code= 27 then 'EPSG_CODE 26902 specified; 26917 assumed'
when dlch.loc_coord_ouom_code= 36 then 'UTM Z17 and NAD83 assumed'
else null
end as epsg_code_comment
,dlch.qa_coord_confidence_code as qa_coord_code
,dlch.coord_hist_loc_method as loc_coord_method
,dlch.coord_hist_comment as loc_coord_comment
,dlch.data_id as loc_coord_data_id
,dleh.loc_elev_code
,case
when dleh.loc_elev_hist_date is not null then dleh.loc_elev_hist_date
else dleh.sys_time_stamp
end as loc_elev_date
,dleh.loc_elev_masl as loc_elev
,6 as loc_elev_unit_code
,dleh.loc_elev_masl as loc_elev_ouom
,'masl' as loc_elev_unit_ouom
,null as loc_elev_method
,rtrim(dleh.elev_hist_comment) as loc_elev_comment
,dleh.data_id as loc_elev_data_id
,cast('20200714c' as varchar(255)) as sys_temp1
,cast(20200714 as int) as sys_temp2
,case
when dleh.sys_last_modified>dlch.sys_last_modified then dleh.sys_last_modified_by
else dlch.sys_last_modified_by
end as sys_last_modified_by
,case
when dleh.sys_last_modified>dlch.sys_last_modified then dleh.sys_last_modified
else dlch.sys_last_modified
end as sys_last_modified
,case
when dlch.sys_user_stamp is not null then dlch.sys_user_stamp
when dleh.sys_user_stamp is not null then dleh.sys_user_stamp
else null
end as sys_user_stamp
,case
when dlch.sys_time_stamp is not null then dlch.sys_time_stamp
when dleh.sys_time_stamp is not null then dleh.sys_time_stamp
else null
end as sys_time_stamp
from 
d_location_coord_hist as dlch
inner join d_location_elev_hist as dleh
on dlch.loc_elev_id=dleh.loc_elev_id
where
dlch.current_coord is null

We can now look at the records from each of the D_LOCATION_COORD_HIST and D_LOCATION_ELEV_HIST that are unrelated (through a LOC_ELEV_ID); each of these will be handled separately. Examining the former, there appears to be less than 500 records with a subset of information available.

select
loc_id
,loc_coord_hist_code
,sys_time_stamp as loc_coord_date
,x_utmz17nad83 as x
,y_utmz17nad83 as y
,26917 as epsg_code
,x_ouom 
,y_ouom
,case
when dlch.loc_coord_ouom_code=  3 then 26718
when dlch.loc_coord_ouom_code=  4 then 26917
when dlch.loc_coord_ouom_code=  5 then 26918
when dlch.loc_coord_ouom_code= 12 then 26717
when dlch.loc_coord_ouom_code= 17 then 26917
when dlch.loc_coord_ouom_code= 21 then 4269
when dlch.loc_coord_ouom_code= 22 then 26917
when dlch.loc_coord_ouom_code= 24 then 32617
when dlch.loc_coord_ouom_code= 36 then 26917
else null
end as epsg_code_orig
,qa_coord_confidence_code as qa_coord_code 
,'No associated elevation specified' as loc_elev_comment
,'20200714d' as sys_temp1
,20200714 as sys_temp2
,sys_last_modified
,sys_last_modified_by
,sys_time_stamp
,sys_user_stamp
from 
d_location_coord_hist as dlch
where 
dlch.current_coord is null
and dlch.loc_elev_id is null

The elevation history table has many more records (due, mainly, to the disparate DEM surfaces referenced).

select
loc_id
,'No associated coordinates specified' as loc_coord_comment
,loc_elev_code
,case
when loc_elev_hist_date is not null then loc_elev_hist_date
else sys_time_stamp
end as loc_elev_date
,loc_elev_masl as loc_elev
,6 as loc_elev_unit_code
,loc_elev_masl as loc_elev_ouom
,'masl' as loc_elev_unit_ouom
,qa_elev_confidence_code as qa_elev_code
,elev_hist_comment as loc_elev_comment
,'20200714e' as sys_temp1
,20200714 as sys_temp2
,sys_last_modified
,sys_last_modified_by
,sys_time_stamp
,sys_user_stamp
from 
d_location_elev_hist as dleh
left outer join 
(
select
delev.loc_elev_id as p_loc_elev_id
from 
d_location_elev as delev
union 
select
dlch.loc_elev_id as p_loc_elev_id
from 
d_location_coord_hist as dlch
where 
dlch.loc_elev_id is not null
) as t
on dleh.loc_elev_id=t.p_loc_elev_id
where
t.p_loc_elev_id is null

Now for those locations that only have a single coordinate, we can associate those coordinates with these newly added elevations (that lack coordinates). This leaves approximately 50,000 records without related coordinates or elevations.

update d_location_spatial_hist
set
loc_coord_hist_code= t2.loc_coord_hist_code
,loc_coord_date= t2.loc_coord_date
,x= t2.x
,y= t2.y
,epsg_code= t2.epsg_code
,x_ouom= t2.x_ouom
,y_ouom= t2.y_ouom
,epsg_code_orig= t2.epsg_code_orig
,epsg_code_comment= t2.epsg_code_comment
,qa_coord_code= t2.qa_coord_code
,loc_coord_method= t2.loc_coord_method
,loc_coord_comment= t2.loc_coord_comment
,sys_temp1= t2.sys_temp1
from 
d_location_spatial_hist as dlsh2
inner join
(
select
dlsh.loc_id
,dlsh.spat_id
,dlch.loc_coord_hist_code
,dlch.sys_time_stamp as loc_coord_date
,dlch.x_utmz17nad83 as x
,dlch.y_utmz17nad83 as y
,26917 as epsg_code
,dlch.x_ouom 
,dlch.y_ouom
,case
when dlch.loc_coord_ouom_code=  1 then null
when dlch.loc_coord_ouom_code=  2 then 26717
when dlch.loc_coord_ouom_code=  3 then 26718
when dlch.loc_coord_ouom_code=  4 then 26917
when dlch.loc_coord_ouom_code=  5 then 26918
when dlch.loc_coord_ouom_code=  6 then 26910
when dlch.loc_coord_ouom_code=  7 then 26915
when dlch.loc_coord_ouom_code=  8 then 26916
when dlch.loc_coord_ouom_code=  9 then 26919
when dlch.loc_coord_ouom_code= 10 then 26917
when dlch.loc_coord_ouom_code= 11 then 26717
when dlch.loc_coord_ouom_code= 12 then 26717
when dlch.loc_coord_ouom_code= 13 then 32190
when dlch.loc_coord_ouom_code= 14 then 7991
when dlch.loc_coord_ouom_code= 15 then 32190
when dlch.loc_coord_ouom_code= 16 then 32196
when dlch.loc_coord_ouom_code= 17 then 26917
when dlch.loc_coord_ouom_code= 18 then 4267
when dlch.loc_coord_ouom_code= 19 then 4269
when dlch.loc_coord_ouom_code= 20 then 4269
when dlch.loc_coord_ouom_code= 21 then 4269
when dlch.loc_coord_ouom_code= 22 then 26917
when dlch.loc_coord_ouom_code= 23 then 26918
when dlch.loc_coord_ouom_code= 24 then 32617
when dlch.loc_coord_ouom_code= 25 then null
when dlch.loc_coord_ouom_code= 26 then 26917
when dlch.loc_coord_ouom_code= 27 then 26917
when dlch.loc_coord_ouom_code= 29 then null
when dlch.loc_coord_ouom_code= 31 then null
when dlch.loc_coord_ouom_code= 33 then null
when dlch.loc_coord_ouom_code= 35 then null
when dlch.loc_coord_ouom_code= 36 then 26917
else null
end as epsg_code_orig
,case
when dlch.loc_coord_ouom_code= 10 then 'UTM NAD83 specified; Z17 assumed'
when dlch.loc_coord_ouom_code= 11 then 'UTM NAD27 specified; Z17 assumed'
when dlch.loc_coord_ouom_code= 12 then 'UTM Z17 specified; NAD27 assumed'
when dlch.loc_coord_ouom_code= 13 then 'MTM specified; Z10 and NAD83 assumed'
when dlch.loc_coord_ouom_code= 20 then 'Lat/Long, Z17 specified; NAD83 assumed'
when dlch.loc_coord_ouom_code= 21 then 'Lat/Long specified; NAD83 assumed'
when dlch.loc_coord_ouom_code= 22 then 'Z17 specified; UTM and NAD83 assumed'
when dlch.loc_coord_ouom_code= 23 then 'Z18 specified; UTM and NAD83 assumed'
when dlch.loc_coord_ouom_code= 26 then 'EPSG_CODE 26911 specified; 26917 assumed'
when dlch.loc_coord_ouom_code= 27 then 'EPSG_CODE 26902 specified; 26917 assumed'
when dlch.loc_coord_ouom_code= 36 then 'UTM Z17 and NAD83 assumed'
else null
end as epsg_code_comment
,dlch.qa_coord_confidence_code as qa_coord_code 
,dlch.coord_hist_loc_method as loc_coord_method
,dlch.coord_hist_comment as loc_coord_comment
,'20200714f' as sys_temp1
from 
d_location_spatial_hist as dlsh
inner join d_location_coord_hist as dlch
on dlsh.loc_id=dlch.loc_id
inner join
(
select
loc_id
,count(*) as rcount
from 
d_location_coord_hist
group by
loc_id
) as t
on dlch.loc_id=t.loc_id
where
t.rcount=1
and dlsh.sys_temp1='20200714e'
and dlsh.x is null and dlsh.y is null
) as t2
on dlsh2.spat_id=t2.spat_id

Database Manual

Oak Ridges Moraine Groundwater Program

G.31 Incorporate D_LOCATION_COORD_HIST and D_LOCATION_ELEV_HIST records into D_LOCATION_SPATIAL_HIST