Hi,
we are using conquest in an NKI-based study to collect data to estimate patient dose from CT scans. For this purpose, a temporary windows based server was setup in the diagnostic department of AMC hospital to collect and anonymize data. In this server, Dicom.SQL was adapted to collect all relevant CT dose paramaters as follows:
/*# DICOM Database layout# Example version for built-in DbaseIII driver (denormalized)## (File DICOM.SQL)# ** DO NOT EDIT THIS FILE UNLESS YOU KNOW WHAT YOU ARE DOING **## Version with modality moved to the series level and EchoNumber in image table# Revision 3: Patient birthday and sex, bolus agent, correct field lengths# Revision 4: Studymodality, Station and Department in study# Manufacturer, Model, BodyPart and Protocol in series# Acqdate/time, coil, acqnumber, slicelocation and pixel info in images# Notes for revision 4:# InstitutionalDepartmentName in study (should officially be in series, but eFilm expects it in study)# StationName is in study (should officially be in series, but more useful in study)# Revision 5: Added patientID in series and images for more efficient querying# Revision 6: Added frame of reference UID in series table# Revision 7: Added ImageType in image table, StudyModality to 64 chars, AcqDate to SQL_C_DATE# Revision 8: Denormalized study table (add patient ID, name, birthdate) to show consistency problems# Revision 9: Denormalized series and image table for full query support with built-in DBF driver# Revision 10: Fixed width of ReceivingCoil: to 16 chars# Revision 11: Added Manufacturer to denormalized image table for built-in DBF driver# Revision 12: Removed duplicate STUDYINST field in series for built-in DBF driver# Revision 15: Added patient position to denormalized image table for built-in DBF driver# Revision 13: Added ImageID to image database# Revision 14: Added WorkList database with HL7 tags# Revision 16: Moved Stationname and InstitutionalDepartmentName to series table# Revision 17: EchoNumber, ReqProcDescription to 64 characters; StudyModality, EchoNumber, ImageType to DT_MSTR; use Institution instead of InstitutionalDepartmentName# Revision 18: For ChildMedRad study### 5 databases need to be defined:## *Patient*# *Study*# *Series*# *Image*# *WorkList*### The last defined element of Study is a link back to Patient# The last defined element of Series is a link back to Study# The last defined element of Image is a link back to Series### Format for DICOM databases :# { Group, Element, Column Name, Column Length, SQL-Type, DICOM-Type }# Format for Worklist database :# { Group, Element, Column Name, Column Length, SQL-Type, DICOM-Type, HL7 tag}# HL7 tags include SEQ.N, SEQ.N.M, SEQ.N.DATE, SEQ.N.TIME, *AN, *UI*/*Patient*{ { 0x0010, 0x0020, "PatientID", 64, SQL_C_CHAR, DT_STR }, { 0x0010, 0x0010, "PatientName", 64, SQL_C_CHAR, DT_STR }, { 0x0010, 0x0030, "PatientBirthDate", 8, SQL_C_DATE, DT_DATE }, { 0x0010, 0x0040, "PatientSex", 16, SQL_C_CHAR, DT_STR }}*Study*{ { 0x0020, 0x000d, "StudyInstanceUID", 64, SQL_C_CHAR, DT_UI }, { 0x0008, 0x0020, "StudyDate", 8, SQL_C_DATE, DT_DATE }, { 0x0008, 0x0030, "StudyTime", 16, SQL_C_CHAR, DT_TIME }, { 0x0020, 0x0010, "StudyID", 16, SQL_C_CHAR, DT_STR }, { 0x0008, 0x1030, "StudyDescription", 64, SQL_C_CHAR, DT_STR }, { 0x0008, 0x0050, "AccessionNumber", 16, SQL_C_CHAR, DT_STR }, { 0x0008, 0x0090, "ReferPhysician", 64, SQL_C_CHAR, DT_STR }, { 0x0032, 0x1032, "ReqPhysician", 64, SQL_C_CHAR, DT_STR }, { 0x0008, 0x0080, "Institution", 64, SQL_C_CHAR, DT_STR }, { 0x0008, 0x1040, "Department", 64, SQL_C_CHAR, DT_STR }, { 0x0010, 0x1010, "PatientsAge", 16, SQL_C_CHAR, DT_STR }, { 0x0010, 0x1030, "PatientsWeight", 16, SQL_C_CHAR, DT_STR }, { 0x0008, 0x0061, "StudyModality", 64, SQL_C_CHAR, DT_MSTR }, { 0x0010, 0x0010, "PatientName", 64, SQL_C_CHAR, DT_STR }, { 0x0010, 0x0030, "PatientBirthDate", 8, SQL_C_DATE, DT_DATE }, { 0x0010, 0x0040, "PatientSex", 16, SQL_C_CHAR, DT_STR } { 0x0010, 0x0020, "PatientID", 64, SQL_C_CHAR, DT_STR }}*Series*{ { 0x0020, 0x000e, "SeriesInstanceUID", 64, SQL_C_CHAR, DT_UI }, { 0x0020, 0x0011, "SeriesNumber", 12, SQL_C_CHAR, DT_STR }, { 0x0008, 0x0021, "SeriesDate", 8, SQL_C_DATE, DT_DATE }, { 0x0008, 0x0031, "SeriesTime", 16, SQL_C_CHAR, DT_TIME }, { 0x0008, 0x103e, "SeriesDescription", 64, SQL_C_CHAR, DT_STR }, { 0x0008, 0x0060, "Modality", 16, SQL_C_CHAR, DT_STR }, { 0x0018, 0x5100, "PatientPosition", 16, SQL_C_CHAR, DT_STR }, { 0x0018, 0x0010, "ContrastBolusAgent", 64, SQL_C_CHAR, DT_STR }, { 0x0008, 0x0070, "Manufacturer", 64, SQL_C_CHAR, DT_STR }, { 0x0008, 0x1090, "ModelName", 64, SQL_C_CHAR, DT_STR }, { 0x0018, 0x0015, "BodyPartExamined", 64, SQL_C_CHAR, DT_STR }, { 0x0018, 0x1030, "ProtocolName", 64, SQL_C_CHAR, DT_STR }, { 0x0008, 0x1010, "StationName", 16, SQL_C_CHAR, DT_STR }, { 0x0008, 0x0080, "Institution", 64, SQL_C_CHAR, DT_STR }, { 0x0020, 0x0052, "FrameOfReferenceUID", 64, SQL_C_CHAR, DT_UI }, { 0x0010, 0x0020, "SeriesPat", 64, SQL_C_CHAR, DT_STR }, { 0x0010, 0x0010, "PatientName", 64, SQL_C_CHAR, DT_STR }, { 0x0010, 0x0030, "PatientBirthDate", 8, SQL_C_DATE, DT_DATE }, { 0x0010, 0x0040, "PatientSex", 16, SQL_C_CHAR, DT_STR } { 0x0008, 0x0020, "StudyDate", 8, SQL_C_DATE, DT_DATE }, { 0x0008, 0x0030, "StudyTime", 16, SQL_C_CHAR, DT_TIME }, { 0x0020, 0x0010, "StudyID", 16, SQL_C_CHAR, DT_STR }, { 0x0008, 0x1030, "StudyDescription", 64, SQL_C_CHAR, DT_STR }, { 0x0008, 0x0090, "ReferPhysician", 64, SQL_C_CHAR, DT_STR }, { 0x0032, 0x1032, "ReqPhysician", 64, SQL_C_CHAR, DT_STR }, { 0x0008, 0x1040, "Department", 64, SQL_C_CHAR, DT_STR }, { 0x0020, 0x000d, "StudyInstanceUID", 64, SQL_C_CHAR, DT_UI }}*Image*{ { 0x0008, 0x0018, "SOPInstanceUID", 64, SQL_C_CHAR, DT_UI }, { 0x0008, 0x0016, "SOPClassUID", 64, SQL_C_CHAR, DT_UI }, { 0x0020, 0x0013, "ImageNumber", 12, SQL_C_CHAR, DT_STR }, { 0x0008, 0x0023, "ImageDate", 8, SQL_C_DATE, DT_DATE }, { 0x0008, 0x0033, "ImageTime", 16, SQL_C_CHAR, DT_TIME }, { 0x0018, 0x0086, "EchoNumber", 64, SQL_C_CHAR, DT_MSTR }, { 0x0028, 0x0008, "NumberOfFrames", 12, SQL_C_CHAR, DT_STR }, { 0x0008, 0x0022, "AcqDate", 8, SQL_C_DATE, DT_DATE }, { 0x0008, 0x0032, "AcqTime", 16, SQL_C_CHAR, DT_TIME }, { 0x0018, 0x1250, "ReceivingCoil", 16, SQL_C_CHAR, DT_STR }, { 0x0020, 0x0012, "AcqNumber", 12, SQL_C_CHAR, DT_STR }, { 0x0020, 0x1041, "SliceLocation", 16, SQL_C_CHAR, DT_STR }, { 0x0028, 0x0002, "SamplesPerPixel", 5, SQL_C_CHAR, DT_UINT16 }, { 0x0028, 0x0004, "PhotoMetricInterpretation", 16, SQL_C_CHAR, DT_STR }, { 0x0028, 0x0010, "Rows", 5, SQL_C_CHAR, DT_UINT16 }, { 0x0028, 0x0011, "Colums", 5, SQL_C_CHAR, DT_UINT16 }, { 0x0028, 0x0101, "BitsStored", 5, SQL_C_CHAR, DT_UINT16 }, { 0x0008, 0x0008, "ImageType", 128, SQL_C_CHAR, DT_MSTR }, { 0x0054, 0x0400, "ImageID", 16, SQL_C_CHAR, DT_STR }, { 0x0010, 0x0020, "ImagePat", 64, SQL_C_CHAR, DT_STR }, { 0x0018, 0x0050, "SliceThickness", 16, SQL_C_CHAR, DT_STR }, { 0x0018, 0x0060, "KVP", 16, SQL_C_CHAR, DT_STR }, { 0x0018, 0x0072, "EffectiveDuration", 16, SQL_C_CHAR, DT_STR }, { 0x0018, 0x0090, "DataCollectionDiameter", 16, SQL_C_CHAR, DT_STR }, { 0x0018, 0x1150, "ExposureTime", 16, SQL_C_CHAR, DT_STR }, { 0x0018, 0x1151, "XRayTubeCurrent", 16, SQL_C_CHAR, DT_STR }, { 0x0018, 0x1152, "Exposure", 16, SQL_C_CHAR, DT_STR }, { 0x0018, 0x1153, "ExposureMuas", 16, SQL_C_CHAR, DT_STR }, { 0x0018, 0x1154, "AvgPulseWidth", 16, SQL_C_CHAR, DT_STR }, { 0x0018, 0x1155, "RadiationSetting", 16, SQL_C_CHAR, DT_STR }, { 0x0018, 0x115a, "RadiationMode", 16, SQL_C_CHAR, DT_STR }, { 0x0018, 0x1170, "GeneratorPower", 16, SQL_C_CHAR, DT_STR }, { 0x0018, 0x1181, "CollimationType", 16, SQL_C_CHAR, DT_STR }, { 0x0018, 0x1302, "ScanLength", 16, SQL_C_CHAR, DT_STR }, { 0x0018, 0x9311, "SpiralPitchFactor", 16, SQL_C_CHAR, DT_STR }, { 0x0018, 0x9306, "SingleCollimationWidth", 16, SQL_C_CHAR, DT_STR }, { 0x0018, 0x9307, "TotalCollimationWidth", 16, SQL_C_CHAR, DT_STR }, { 0x0018, 0x9323, "ExposureModulationType", 16, SQL_C_CHAR, DT_STR }, { 0x0018, 0x9305, "RevolutionTime", 16, SQL_C_CHAR, DT_STR }, { 0x0020, 0x0032, "ImagePositionPatient", 64, SQL_C_CHAR, DT_STR }, { 0x0018, 0x0015, "BodyPartExamined", 64, SQL_C_CHAR, DT_STR }, { 0x0018, 0x1030, "ProtocolName", 64, SQL_C_CHAR, DT_STR }, { 0x0008, 0x0070, "Manufacturer", 64, SQL_C_CHAR, DT_STR }, { 0x0008, 0x1090, "ModelName", 64, SQL_C_CHAR, DT_STR }, { 0x0008, 0x0090, "ReferPhysician", 64, SQL_C_CHAR, DT_STR }, { 0x0032, 0x1032, "ReqPhysician", 64, SQL_C_CHAR, DT_STR }, { 0x0008, 0x0080, "Institution", 64, SQL_C_CHAR, DT_STR }, { 0x0008, 0x1040, "Department", 64, SQL_C_CHAR, DT_STR }, { 0x0018, 0x0010, "ContrastBolusAgent", 64, SQL_C_CHAR, DT_STR }, { 0x0010, 0x0010, "PatientName", 64, SQL_C_CHAR, DT_STR }, { 0x0010, 0x0030, "PatientBirthDate", 8, SQL_C_DATE, DT_DATE }, { 0x0010, 0x0040, "PatientSex", 16, SQL_C_CHAR, DT_STR } { 0x0020, 0x000d, "StudyInstanceUID", 64, SQL_C_CHAR, DT_UI }, { 0x0008, 0x0020, "StudyDate", 8, SQL_C_DATE, DT_DATE }, { 0x0008, 0x0030, "StudyTime", 16, SQL_C_CHAR, DT_TIME }, { 0x0020, 0x0010, "StudyID", 16, SQL_C_CHAR, DT_STR }, { 0x0008, 0x1030, "StudyDescription", 64, SQL_C_CHAR, DT_STR }, { 0x0020, 0x0011, "SeriesNumber", 12, SQL_C_CHAR, DT_STR }, { 0x0008, 0x0021, "SeriesDate", 8, SQL_C_DATE, DT_DATE }, { 0x0008, 0x0031, "SeriesTime", 16, SQL_C_CHAR, DT_TIME }, { 0x0008, 0x103e, "SeriesDescription", 64, SQL_C_CHAR, DT_STR }, { 0x0008, 0x0060, "Modality", 16, SQL_C_CHAR, DT_STR }, { 0x0018, 0x5100, "PatientPosition", 16, SQL_C_CHAR, DT_STR }, { 0x0020, 0x0052, "FrameOfReferenceUID", 64, SQL_C_CHAR, DT_UI }, { 0x0020, 0x000e, "SeriesInstanceUID", 64, SQL_C_CHAR, DT_UI }}*WorkList*{ { 0x0008, 0x0050, "AccessionNumber", 16, SQL_C_CHAR, DT_STR, "OBR.3" }, { 0x0010, 0x0020, "PatientID", 64, SQL_C_CHAR, DT_STR, "PID.4" }, { 0x0010, 0x0010, "PatientName", 64, SQL_C_CHAR, DT_STR, "PID.5" }, { 0x0010, 0x0030, "PatientBirthDate", 8, SQL_C_DATE, DT_DATE, "PID.7" }, { 0x0010, 0x0040, "PatientSex", 16, SQL_C_CHAR, DT_STR, "PID.8" }, { 0x0010, 0x2000, "MedicalAlerts", 64, SQL_C_CHAR, DT_STR, "---" }, { 0x0010, 0x2110, "ContrastAllergies", 64, SQL_C_CHAR, DT_STR, "---" }, { 0x0020, 0x000d, "StudyInstanceUID", 64, SQL_C_CHAR, DT_UI, "---" }, { 0x0032, 0x1032, "ReqPhysician", 64, SQL_C_CHAR, DT_STR, "OBR.16" }, { 0x0032, 0x1060, "ReqProcDescription", 64, SQL_C_CHAR, DT_STR, "OBR.4.1" }, { 0x0040, 0x0100, "--------", 0, SQL_C_CHAR, DT_STARTSEQUENCE, "---" }, { 0x0008, 0x0060, "Modality", 16, SQL_C_CHAR, DT_STR, "OBR.21" }, { 0x0032, 0x1070, "ReqContrastAgent", 64, SQL_C_CHAR, DT_STR, "---" }, { 0x0040, 0x0001, "ScheduledAE", 16, SQL_C_CHAR, DT_STR, "---" }, { 0x0040, 0x0002, "StartDate", 8, SQL_C_DATE, DT_DATE, "OBR.7.DATE" }, { 0x0040, 0x0003, "StartTime", 16, SQL_C_CHAR, DT_TIME, "OBR.7.TIME" }, { 0x0040, 0x0006, "PerfPhysician", 64, SQL_C_CHAR, DT_STR, "---" }, { 0x0040, 0x0007, "SchedPSDescription", 64, SQL_C_CHAR, DT_STR, "---" }, { 0x0040, 0x0009, "SchedPSID", 16, SQL_C_CHAR, DT_STR, "OBR.4" }, { 0x0040, 0x0010, "SchedStationName", 16, SQL_C_CHAR, DT_STR, "OBR.24" }, { 0x0040, 0x0011, "SchedPSLocation", 16, SQL_C_CHAR, DT_STR, "---" }, { 0x0040, 0x0012, "PreMedication", 64, SQL_C_CHAR, DT_STR, "---" }, { 0x0040, 0x0400, "SchedPSComments", 64, SQL_C_CHAR, DT_STR, "---" }, { 0x0040, 0x0100, "---------", 0, SQL_C_CHAR, DT_ENDSEQUENCE, "---" }, { 0x0040, 0x1001, "ReqProcID", 16, SQL_C_CHAR, DT_STR, "OBR.4.0" }, { 0x0040, 0x1003, "ReqProcPriority", 16, SQL_C_CHAR, DT_STR, "OBR.27 }}
Then, all recieved images were dowsized to 32x32 pixels, and anomymized by these settings in dicom.ini:
The anonymization script was approved prior by the PACS administrator. The PACS administrator then made a list of all patients in the study period per month, and a corresponding birthdate range to select only patients under the age of 18. The data was sent overnight in batches to conquest, generating a 3 GB SqLite database, and 60 GB of image data (110.000 images) for a period of 10 years of one of the largest hospitals of the Netherlands.
After the data was collected, non-CT, studies from other hospitals, scanned films, and secondary images were deleted to clean up the database. These images could have been rejected during data collection using ImportConverters.
Now, a second query of the PACS is needed to collect PatientID, Address, Name, Sex, Birthdate, Study description, Referring Physician, Study date, Study time, and Institution for all patients with the study criterea. This second list will be used to generate the de-anonymization table to be used by a trusted party.
Hope this is useful to others.
Marcel