pdacclassify/pdacgrow.sql at main · ImageGuidedTherapyLab/pdacclassify · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
-- cat pdacgrow.sql | sqlite3
-- sqlite3  -init pdacgrow.sql
.headers on
-- attach database ':memory:' as tmp;
attach database 'regiongrow/ctkDICOM.sql' as sl;

.mode csv
.import dicom/NNdatabase07_07updated.csv  edrn

-- sqlite> select distinct im.seriesDescription from sl.series im;
-- "Recon 2: LIVER/PANCREAS"
-- "Series Description"
-- "PreXRT-PRE resampled"
-- "PreXRT-PV resampled"
-- "PRE resampled"
-- "PreXRT - PV resampled"
-- "PreXRT-PV added resampled"
-- LIVER/PANCREAS
-- "PreXRT-Pre resampled"
-- "PreXRT-Portal resampled"
-- "normal pancreas"
-- "Portovenous resampled"
-- "PostXRT-Pre resampled"
-- "PostXRT-Portal resampled"
-- "Recon 2: LIVER/PANC AP"
-- "PreXRT-PRE  resampled"
-- "PRE LIVER resampled"
-- "Recon 2: C-A-P"
-- ""
-- "Recon 3: LIVER/PANCREAS"
-- "portovenous resampled"
-- "Recon 2: LIVER/PANC CAP"


create table flagdata  as
select sd.PatientsUID,pt.PatientID,im.StudyInstanceUID,im.SeriesInstanceUID,im.seriesDescription,im.SeriesDate,di.FileName,
CASE WHEN (im.seriesDescription == "PreXRT-PRE  resampled" or im.seriesDescription == "PRE LIVER resampled" or im.seriesDescription == "PostXRT-Pre resampled" or im.seriesDescription == "PreXRT-Pre resampled" or im.seriesDescription == "PRE resampled" or im.seriesDescription == "PreXRT-PRE resampled")  THEN 'Pre'
     WHEN (im.seriesDescription like '%Port%' or im.seriesDescription like '%port%' or im.seriesDescription like '%ven%'or im.seriesDescription like '%PV%' )  THEN 'Ven'
     WHEN im.Modality == 'RTSTRUCT'  THEN 'Truth'
     ELSE 'Art' END AS ImageType
from sl.series im
join sl.images di on di.SeriesInstanceUID= im.SeriesInstanceUID
join sl.studies  sd on sd.StudyInstanceUID = im.StudyInstanceUID
join sl.patients pt on sd.PatientsUID = pt.UID;
-- select * from flagdata where ImageType = 'Truth';

create table labeldatatmp  as
select *,
       1 as truthid
--       CASE WHEN Filename like '%normal%'   THEN 1
--            WHEN Filename like '%BL%' THEN 2
--            END AS truthid
       from flagdata where ImageType = 'Truth';
select max(truthid) from labeldatatmp  ;
create table labeldata  as
select *, 'Truth'||truthid truthlabel  from labeldatatmp;

create table widestudy  as
select fg.PatientsUID PatientsUID,fg.PatientID PatientID,ed.DeltaScore,fg.StudyInstanceUID StudyInstanceUID,
            max(CASE WHEN fg.ImageType = 'Pre' THEN fg.SeriesInstanceUID       ELSE NULL END)  Pre,
            max(CASE WHEN fg.ImageType = 'Pre' THEN fg.SeriesDescription       ELSE NULL END)  PreDescription,
            max(CASE WHEN fg.ImageType = 'Pre' THEN rtrim(fg.Filename, replace(fg.Filename, '/', '')) ELSE NULL END) PreFilename,
            max(CASE WHEN fg.ImageType = 'Art' THEN fg.SeriesInstanceUID       ELSE NULL END)  Art,
            max(CASE WHEN fg.ImageType = 'Art' THEN fg.SeriesDescription       ELSE NULL END)  ArtDescription,
            max(CASE WHEN fg.ImageType = 'Art' THEN rtrim(fg.Filename, replace(fg.Filename, '/', '')) ELSE NULL END) ArtFilename,
            max(CASE WHEN fg.ImageType = 'Ven' THEN fg.SeriesInstanceUID       ELSE NULL END)  Ven,
            max(CASE WHEN fg.ImageType = 'Ven' THEN fg.SeriesDescription       ELSE NULL END)  VenDescription,
            max(CASE WHEN fg.ImageType = 'Ven' THEN rtrim(fg.Filename, replace(fg.Filename, '/', '')) ELSE NULL END) VenFilename,
            max(CASE WHEN ld.truthlabel = 'Truth1' THEN ld.SeriesInstanceUID     ELSE NULL END)  Truth1,
            max(CASE WHEN ld.truthlabel = 'Truth1' THEN ld.SeriesDescription     ELSE NULL END)  Truth1Description,
            max(CASE WHEN ld.truthlabel = 'Truth1' THEN ld.Filename              ELSE NULL END)  Truth1FileName,
            max(CASE WHEN ld.truthlabel = 'Truth2' THEN ld.SeriesInstanceUID     ELSE NULL END)  Truth2,
            max(CASE WHEN ld.truthlabel = 'Truth2' THEN ld.SeriesDescription     ELSE NULL END)  Truth2Description,
            max(CASE WHEN ld.truthlabel = 'Truth2' THEN ld.Filename              ELSE NULL END)  Truth2FileName,
            max(CASE WHEN ld.truthlabel = 'Truth3' THEN ld.SeriesInstanceUID     ELSE NULL END)  Truth3,
            max(CASE WHEN ld.truthlabel = 'Truth3' THEN ld.SeriesDescription     ELSE NULL END)  Truth3Description,
            max(CASE WHEN ld.truthlabel = 'Truth3' THEN ld.Filename              ELSE NULL END)  Truth3FileName,
            max(CASE WHEN ld.truthlabel = 'Truth4' THEN ld.SeriesInstanceUID     ELSE NULL END)  Truth4,
            max(CASE WHEN ld.truthlabel = 'Truth4' THEN ld.SeriesDescription     ELSE NULL END)  Truth4Description,
            max(CASE WHEN ld.truthlabel = 'Truth4' THEN ld.Filename              ELSE NULL END)  Truth4FileName,
            max(CASE WHEN ld.truthlabel = 'Truth5' THEN ld.SeriesInstanceUID     ELSE NULL END)  Truth5,
            max(CASE WHEN ld.truthlabel = 'Truth5' THEN ld.SeriesDescription     ELSE NULL END)  Truth5Description,
            max(CASE WHEN ld.truthlabel = 'Truth5' THEN ld.Filename              ELSE NULL END)  Truth5FileName,
            max(CASE WHEN ld.truthlabel = 'Truth6' THEN ld.SeriesInstanceUID     ELSE NULL END)  Truth6,
            max(CASE WHEN ld.truthlabel = 'Truth6' THEN ld.SeriesDescription     ELSE NULL END)  Truth6Description,
            max(CASE WHEN ld.truthlabel = 'Truth6' THEN ld.Filename              ELSE NULL END)  Truth6FileName,
            max(CASE WHEN ld.truthlabel = 'Truth7' THEN ld.SeriesInstanceUID     ELSE NULL END)  Truth7,
            max(CASE WHEN ld.truthlabel = 'Truth7' THEN ld.SeriesDescription     ELSE NULL END)  Truth7Description,
            max(CASE WHEN ld.truthlabel = 'Truth7' THEN ld.Filename              ELSE NULL END)  Truth7FileName
from flagdata  fg
join edrn      ed on   ed.MRN=fg.PatientID
join labeldata      ld on   ld.PatientID=fg.PatientID
GROUP BY    fg.StudyInstanceUID;
-- select * from widestudy;

-- error check
select distinct im.seriesDescription from sl.series im;
select count(ws.StudyInstanceUID),count(ws.Pre),count(ws.Art),count(ws.Ven),count(ws.Truth1) ,count(ws.Truth2) ,count(ws.Truth3) ,count(ws.Truth4) ,count(ws.Truth5) ,count(ws.Truth6) ,count(ws.Truth7)  from widestudy  ws;
select ws.* , se.SeriesDescription
from widestudy  ws
join sl.studies sd on sd.PatientsUID = ws.PatientsUID
join sl.series  se on sd.StudyInstanceUID=se.StudyInstanceUID
where ws.Art is null;

-- error check
select ws.PatientID from  widestudy ws  where ws.Truth2 is NULL ;

-- FIXME HACK remove problem data
delete FROM widestudy  WHERE PatientsUID = 15;

-- -- wide format
-- -- FIXME use group by to remove duplicates
.mode csv
.output dicom/wideformatgrow.csv
select ws.* from widestudy ws;

.output dicom/wideclassificationgrow.csv
select ws.PatientID as id, 'GrowProcessed/'||ws.PatientID||'/Art.raw.nii.gz' as Art,'GrowProcessed/'||ws.PatientID||'/lesionmask.nii.gz' as Mask,
       ws.DeltaScore as target,
       CASE WHEN ws.DeltaScore = 'High'   THEN 1
            WHEN ws.DeltaScore = 'Low'   THEN 0
            ELSE NULL
            END AS truthid
       from widestudy ws;

.output dicom/wideclassificationroigrow.csv
select ws.PatientID as id, 'GrowProcessed/'||ws.PatientID||'/Artroi.nii.gz' as Art,'GrowProcessed/'||ws.PatientID||'/lesionroi.nii.gz' as Mask,
       ws.DeltaScore as target,
       CASE WHEN ws.DeltaScore = 'High'   THEN 1
            WHEN ws.DeltaScore = 'Low'   THEN 0
            ELSE NULL
            END AS truthid
       from widestudy ws;
.output dicom/wideclassificationgrowrad.csv
select ws.PatientID as id, '/rsrch3/ip/dtfuentes/github/pdacclassify/GrowProcessed/'||ws.PatientID||'/Art.raw.nii.gz' as Image,'/rsrch3/ip/dtfuentes/github/pdacclassify/GrowProcessed/'||ws.PatientID||'/Normal.raw.nii.gz' as Mask, 1 as Label,
       ws.DeltaScore as target,
       CASE WHEN ws.DeltaScore = 'High'   THEN 1
            WHEN ws.DeltaScore = 'Low'   THEN 0
            ELSE NULL
            END AS truthid
       from widestudy ws;
--select ws.PatientID as id, '/rsrch3/ip/dtfuentes/github/pdacclassify/GrowProcessed/'||ws.PatientID||'/Art.raw.nii.gz' as Image,'/rsrch3/ip/dtfuentes/github/pdacclassify/GrowProcessed/'||ws.PatientID||'/Normal.raw.nii.gz' as Mask, 2 as Label,
--       ws.DeltaScore as target,
--       CASE WHEN ws.DeltaScore = 'High'   THEN 1
--            WHEN ws.DeltaScore = 'Low'   THEN 0
--            ELSE NULL
--            END AS truthid
--       from widestudy ws;


.quit