Note: We no longer publish the latest version of our code here. We primarily use a kumc-bmi github organization. The heron ETL repository, in particular, is not public. Peers in the informatics community should see MultiSiteDev for details on requesting access.

source: heron_load/dem_concepts_load.sql @ 0:42ad7288920a

heron-michigan tip
Last change on this file since 0:42ad7288920a was 0:42ad7288920a, checked in by Matt Hoag <mhoag@…>, 6 years ago

Merge with demo_concepts_3800

File size: 6.5 KB
Line 
1/** dem_concepts_load -- load custom geographic demographic terms/concepts.
2
3Copyright (c) 2012 University of Kansas Medical Center
4part of the HERON* open source codebase; see NOTICE file for license details.
5* http://informatics.kumc.edu/work/wiki/HERON
6
7*/
8
9--  Check that we are in the de-identified server.
10select upload_id from blueherondata.upload_status where 1=0;
11
12-- check for curated data
13-- TODO: load curated data into shared metadata schema
14select district_name from school_districts where 1=0;
15
16
17delete from BlueHeronMetadata.custom_meta
18where c_fullname like '\i2b2\Visit Details\Age at visit\%'
19or  c_fullname like '\i2b2\Demographics\Age\%';
20
21
22/* Compute age concepts.*/
23insert into BlueHeronMetadata.custom_meta (
24  c_hlevel, c_fullname, c_name, c_basecode, c_dimcode,
25  c_visualattributes,
26  c_synonym_cd, c_facttablecolumn, c_tablename, c_columnname, c_columndatatype,
27  c_operator, m_applied_path,
28  update_date, import_date, sourcesystem_cd)
29with
30age_folder as (
31  select
32    2 as c_hlevel, f.*, 'FA' c_visualattributes
33  from
34    dual,
35      (
36      select
37      '\i2b2\Demographics\Age\' c_fullname,
38      'Age' c_name,
39      null base_concept
40      from dual
41     
42      union all
43     
44      select
45      '\i2b2\Visit Details\Age at visit\' c_fullname,
46      'Age at Visit' c_name,
47      'DEM|AGEATV:' base_concept
48      from dual
49      ) f
50  ),
51demo_age as(
52  select
53    t.*, sysdate update_date, sysdate import_date, aud.source_cd
54  from(
55    select
56      *
57    from
58      BlueHeronData.Source_Master -- double-check string constant
59    where source_cd like 'heron-admin@%') aud, (
60      select
61        age_folder.c_hlevel,
62        age_folder.c_fullname,
63        age_folder.c_name,
64        null c_basecode,
65        age_folder.c_fullname c_dimcode,
66        age_folder.c_visualattributes,
67        norm.*
68      from age_folder, BLUEHERONMETADATA.normal_concept norm
69     
70      union all
71 
72      select
73        ta.*,
74        ta.c_fullname c_dimcode,
75        case
76          when ta.c_hlevel > 3 then 'LA'
77          else
78            case when ta.c_name = 'Not Recorded at visit' then 'FH'
79            else 'FA'
80          end
81        end c_visualattributes, norm.*
82      from
83        BLUEHERONMETADATA.normal_concept norm, (
84          select age_folder.c_hlevel + (
85            case
86              when age_terms.leaf_name is not null then 2
87              else 1
88            end)  c_hlevel,
89            age_folder.c_fullname || trim(age_terms.folder_name) || '\' ||
90              case
91                when age_terms.leaf_name is null then null
92                else age_terms.leaf_name || '\'
93              end c_fullname,
94              case
95                when age_terms.leaf_name is null then
96                  case when age_folder.base_concept is null then age_terms.folder_name
97                  else age_terms.folder_name || ' at visit'
98                  end
99                else
100                  case
101                    when age_folder.base_concept is null then age_terms.leaf_name
102                    else age_terms.leaf_name || ' at visit'
103                  end
104              end c_name,
105            case
106              when age_folder.base_concept is null then age_terms.concept_cd
107              else
108                case
109                  when age_terms.age is null then null
110                  else age_folder.base_concept || age_terms.age
111                end
112            end c_basecode
113          from age_folder, age_terms
114        ) ta
115      ) t
116  )
117select da.*
118from demo_age da
119order by da.c_fullname
120;
121
122
123delete from BlueHeronMetadata.custom_meta
124where c_fullname like '\i2b2\Demographics\KUMC radius\%'
125or  c_fullname like '\i2b2\Demographics\SchoolDistrict\%';
126
127insert into BlueHeronMetadata.custom_meta (
128  c_hlevel, c_fullname, c_name, c_basecode, c_dimcode,
129  c_visualattributes,c_tooltip, c_metadataxml,
130  c_synonym_cd, c_facttablecolumn, c_tablename, c_columnname, c_columndatatype,
131  c_operator, m_applied_path,
132  update_date, import_date, sourcesystem_cd)
133select 1 + l
134     , '\i2b2\Demographics\' || concept_path as c_fullname
135     , name_char as c_name
136     , concept_cd as basecode
137     , '\i2b2\Demographics\' || concept_path as c_dimcode
138     , c_visualattributes
139     , name_char as tooltip
140     , null as c_metadataxml
141     , normal.*
142     , sysdate as update_date, sysdate as import_Date, aud.source_cd sourcesystem_cd
143from BLUEHERONMETADATA.normal_concept normal
144  , (select * from BlueHeronData.Source_Master -- double-check string constant
145     where source_cd like 'heron-admin@%') aud, (
146select 1 as l
147     , 'KUMC radius\' as concept_path
148     , null as concept_cd
149     , 'Place: distance from KUMC' as name_char
150     , 'FA' as c_visualattributes
151from dual
152union all (
153select c_hlevel + 1 as c_hlevel
154     , 'KUMC radius\' || path ||
155     case when radius < max_radius then radius || 'mi\' else '' end
156     path
157     , kumc_geo_scheme.c_key || radius || 'mi' as concept_cd
158     , 'within' || to_char(radius, '999') || ' miles of KUMC' as c_name
159     , case when radius = max_radius then 'MA' else 'LH' end as c_visualattributes
160from (
161select * from BlueHeronMetadata.schemes
162where c_name = 'DEM|GEO|KUMC') kumc_geo_scheme, (
163-- see also ../zip_agg/Makefile for list
164-- 5 10 15 20 50 100 200
165select * from (
166select '200mi\' as path, 200 as max_radius, 1 c_hlevel from dual
167union all
168select '100mi\', 100, 1 from dual
169union all
170select '50mi\' as path, 50, 1 from dual
171union all
172select '20mi\', 20, 1 from dual
173union all
174select '15mi\', 15, 1 from dual
175union all
176select '10mi\', 10, 1 from dual
177union all
178select '5mi\', 5, 1 from dual
179) concept
180join (
181  select 200 as radius from dual
182  union all
183  select 100 as radius from dual
184  union all
185  select 50 as radius from dual
186  union all
187  select 20 as radius from dual
188  union all
189  select 15 as radius from dual
190  union all
191  select 10 as radius from dual
192  union all
193  select 5 as radius from dual ) candidate
194  on candidate.radius <= concept.max_radius
195))
196
197
198union all
199select 1 as l
200     , 'SchoolDistrict\' as concept_path
201     , null as concept_cd
202     , 'Place: School District' as name_char
203     , 'FA'
204from dual
205
206union all
207
208select distinct 2 as c_hlevel
209     , 'SchoolDistrict\' || sd.concept_name  || '\' as concept_path
210     , scheme.c_key || sd.concept_name  as concept_cd
211     , sd.district_name || ' (' || sd.concept_name || ')' as name_char
212     , 'LA'
213from school_districts sd
214   , (
215  -- use select to ensure consistency between data loading and metadata
216  select * from BLUEHERONMETADATA.schemes where c_name='DEM|SCHOOL_DISTRICT') scheme
217)
218;
219-- 32 rows inserted.
Note: See TracBrowser for help on using the repository browser.