SmartRank

Appendix A. DNA Database Export Back to top

Using the query below, a SmartRank-compatible database file can be exported from a CODIS SQLServer database.

SELECT 
    specimenId, CSF1PO_1, CSF1PO_2, CSF1PO_3, CSF1PO_4,
    D10S1248_1, D10S1248_2, D10S1248_3, D10S1248_4, D12S391_1,
    D12S391_2, D12S391_3, D12S391_4, D13S317_1, D13S317_2,
    D13S317_3, D13S317_4, D16S539_1, D16S539_2, D16S539_3,
    D16S539_4, D18S51_1, D18S51_2, D18S51_3, D18S51_4, 
    D19S433_1, D19S433_2, D19S433_3, D19S433_4, D1S1656_1,
    D1S1656_2, D1S1656_3, D1S1656_4, D21S11_1, D21S11_2,
    D21S11_3, D21S11_4, D22S1045_1, D22S1045_2, D22S1045_3,
    D22S1045_4, D2S1338_1, D2S1338_2, D2S1338_3, D2S1338_4,
    D2S441_1, D2S441_2, D2S441_3, D2S441_4, D3S1358_1,
    D3S1358_2, D3S1358_3, D3S1358_4, D5S818_1, D5S818_2,
    D5S818_3, D5S818_4, D7S820_1, D7S820_2, D7S820_3,
    D7S820_4, D8S1179_1, D8S1179_2, D8S1179_3, D8S1179_4,
    F13A1_1, F13A1_2, F13A1_3, F13A1_4, FES_FPS_1,
    FES_FPS_2, FES_FPS_3, FES_FPS_4, FGA_1, FGA_2,
    FGA_3, FGA_4, [Penta D_1], [Penta D_2], [Penta D_3],
    [Penta D_4], [Penta E_1], [Penta E_2], [Penta E_3], 
    [Penta E_4], SE33_1, SE33_2, SE33_3, SE33_4, TH01_1,
    TH01_2, TH01_3, TH01_4, TPOX_1, TPOX_2,
    TPOX_3,TPOX_4, vWA_1, vWA_2, vWA_3, vWA_4
FROM 
(
    SELECT 
        S.Spec_ID AS [specimenId],
        CAST(rtrim(Locus) AS VARCHAR(50)) + '_' + CAST(PV.Band_Num AS VARCHAR(2)) [locus],
        rtrim(PV.PCR_Value) AS [data]
    FROM 
        CODIS.dbo.Specimen S
        INNER JOIN CODIS.dbo.Sizing Z ON S.Spec_CD = Z.Spec_CD 
        INNER JOIN CODIS.dbo.PCR_Value PV ON Z.Locus_CD = PV.Locus_CD 
        AND Z.Spec_CD = PV.Spec_CD 
        INNER JOIN CODIS.dbo.Locus L ON Z.Locus_CD = L.Locus_CD
        WHERE S.Spec_Type_CD IN 
        (
            SELECT 
                Spec_Type_CD 
            FROM 
                CODIS.dbo.Spec_Type 
                WHERE Spec_Type IN 
                (
                    'Convicted Offender',
                    'Suspect, Known', 
                    'Elimination, Known'
                )
        )
) 
x PIVOT
(
    MAX(DATA)
    FOR locus IN
    (
        CSF1PO_1, D10S1248_1, D12S391_1, D13S317_1, D16S539_1,
        D18S51_1, D19S433_1, D1S1656_1, D21S11_1, D22S1045_1, 
        D2S1338_1, D2S441_1, D3S1358_1, D5S818_1, D7S820_1, 
        D8S1179_1, F13A1_1, FES_FPS_1, FGA_1, [Penta D_1],
        [Penta E_1], SE33_1, TH01_1, TPOX_1, vWA_1, CSF1PO_2,
        D10S1248_2, D12S391_2, D13S317_2, D16S539_2, D18S51_2, 
        D19S433_2, D1S1656_2, D21S11_2, D22S1045_2, D2S1338_2,
        D2S441_2, D3S1358_2, D5S818_2, D7S820_2, D8S1179_2, 
        F13A1_2, FES_FPS_2, FGA_2, [Penta D_2], [Penta E_2], 
        SE33_2, TH01_2, TPOX_2, vWA_2, CSF1PO_3, D10S1248_3,
        D12S391_3, D13S317_3, D16S539_3, D18S51_3, D19S433_3, 
        D1S1656_3, D21S11_3, D22S1045_3, D2S1338_3, D2S441_3,
        D3S1358_3, D5S818_3, D7S820_3, D8S1179_3, F13A1_3,
        FES_FPS_3, FGA_3, [Penta D_3], [Penta E_3], SE33_3,
        TH01_3, TPOX_3, vWA_3, CSF1PO_4, D10S1248_4, D12S391_4,
        D13S317_4, D16S539_4, D18S51_4, D19S433_4, D1S1656_4,
        D21S11_4, D22S1045_4, D2S1338_4, D2S441_4, 
        D3S1358_4, D5S818_4, D7S820_4, D8S1179_4, F13A1_4, 
        FES_FPS_4, FGA_4, [Penta D_4], [Penta E_4], SE33_4, 
        TH01_4, TPOX_4, vWA_4
    )
) 
AS p
    

Appendix B. DNA Database Format Back to top

Currently, the only accepted format for a DNA database is a CODIS CSV format. A CODIS CSV file is a comma-separated file listing allelic information for one or multiple specimens. The first line contains a header; starting with specimen ID, followed by identifiers for genetic markers (e.g. THO1 or D3S1358). Specimens are listed below this header with a unique identifier and alleles for the above mentioned genetic markers.

Below is an example of a DNA database file, containing allelic information of 10 different specimens.

Appendix C. Profile format Back to top

The accepted formats for profile files (crime scene- or known) are tab- or comma-separated files. The first line of the file contains a preset header of the profiles; it lists a sample name, a marker and up to eight alleles. Every detected marker in the sample is listed per row, describing what sample it originates from, its identifier and the allelic information associated with that marker.

Below is an example of a crime scene sample file, containing allelic information of 15 identified markers. Known profile files use the same format.

Appendix D. Population Statistics Format Back to top

The accepted format for population statistics files is tab- or comma-separated. Alleles are displayed vertically, loci horizontally. The cells where alleles and loci cross displays the probability of existence of that allele in that specific locus in the population. Should the allele never appear in a locus, the cell remains blank.

Below is an example of a population statistics file, showing allelic probabilities of 16 different loci.

Appendix E. Search Criteria File Format Back to top

Search criteria can be imported from an XML or JSON file. An example of search criteria in XML format is shown below. See the xsd folder of the SmartRank installation folder for an XSD file describing the Search Criteria file format.

        <SmartRankImportFile version="1.0" xmlns="urn:SmartRankImportFile-schema">
            <!-- The case folder is the place where logfiles and reports are written to. 
                    This overrides the location configured in the restrictions file -->
            <CaseFolder>2016/01 januari/2016.01.01.001</CaseFolder>
            
            <!-- The user id of the person who generated this file -->
            <userid>MASLA</userid>
            
            <!-- The date and time that the file was generated (not used in smartrank, but present for tracability reasons) -->
            <dateTime>2016-08-22 15:24:23</dateTime>
            
            <!-- This contains references to all replicates (crime-scene profiles) -->
            <Replicates>
                <!-- Each replicate get a Replicate item here. The name refers to the name of a Specimen (see below) -->
                <Replicate name="CEDU0059FR#07-KOEL0126LU#21"/>
            </Replicates>
            
            <!-- The value to use for dropin -->
            <Dropin>0.10</Dropin>
            
            <!-- The value to use for theta -->
            <Theta>0.03</Theta>
            
            <!-- Specification of Hp. The candidateDropout attribute defines the dropout for the candidate sample. -->
            <HP candidateDropout="0.10">
                <!-- Contains references to the samples that contributors under Hp, and their corresponding dropout -->
                <Contributors>
                    <!-- Each sample that is a contributor under Hp gets a Contributor item here. 
                            The name refers to the name of a Specimen (see below) -->
                    <Contributor name="CEDU0059FR#07" dropout="0.11"/>
                </Contributors>
                
                <!-- Contains the settings for unknown contributors under Hp -->
                <Unknowns count="1" dropout="0.12"/>
            </HP>

            <!-- Specification of Hd. -->
            <HD>
                <!-- Contains references to the samples that contributors under Hd, and their corresponding dropout -->
                <Contributors>
                    <!-- Each sample that is a contributor under Hd gets a Contributor item here. 
                            The name refers to the name of a Specimen (see below) -->
                    <Contributor name="CEDU0059FR#07" dropout="0.13"/>
                </Contributors>
                
                <!-- Contains the settings for unknown contributors under Hd -->
                <Unknowns count="2" dropout="0.14"/>
            </HD>
            
            <!-- For every loaded crime-scene profile and known profile, a Specimen entry 
                    is included listing the contents of the sample. -->
            <Specimen name="CEDU0059FR#07-KOEL0126LU#21">
                <!-- Every locus in the sample gets a Locus item here -->
                <Locus name="D16S539">
                    <!-- Every allele in this locus gets an Allele item here -->
                    <Allele value="11"/>
                    <Allele value="12"/>
                    <Allele value="13"/>
                </Locus>
                <Locus name="D18S51">
                    <Allele value="14"/>
                    <Allele value="15"/>
                </Locus>
            </Specimen>
            
            <Specimen name="CEDU0059FR#07">
                <Locus name="D16S539">
                    <Allele value="11"/>
                    <Allele value="11"/>
                </Locus>
                <Locus name="D18S51">
                    <Allele value="14"/>
                    <Allele value="15"/>
                </Locus>
            </Specimen>                
        </SmartRankImportFile>
        

Below is an example of search criteria in JSON format

	{
	    "caseFolder": "2016/01 januari/2016.01.01.001",
	    "userid": "MASLA",
	    "dateTime": "2016-08-22 15:24:23",
	    "replicates": {
	        "replicate": [
	            {
	                "name": "CEDU0059FR#07-KOEL0126LU#21"
	            }
	        ]
	    },
	    "dropin":  0.10,
	    "theta":  0.03,
	    "lrThreshold":  1234,
	    "maximumNumberOfResults":  10,
	    "rareAlleleFrequency":  0.000239808153477218,
	    "hp": {
	        "contributors": {
	            "contributor": [
	                {
	                    "name":  "CEDU0059FR#07",
	                    "dropout":  "0.11"
	                }
	            ]
	        },
	        "nonContributors": null,
	        "unknowns": {
	            "count":  1,
	            "dropout":  "0.12"
	        },
	        "candidateDropout":  "0.10"
	    },
	    "hd": {
	        "contributors": {
	            "contributor": [
	                {
	                    "name":  "CEDU0059FR#07",
	                    "dropout":  "0.13"
	                }
	            ]
	        },
	        "nonContributors": null,
	        "unknowns": {
	            "count":  2,
	            "dropout":  "0.14"
	        }
	    },
	    "specimen": [
	        {
	            "locus": [
	                {
	                    "allele": [
	                        {
	                            "value":  "11"
	                        },
	                        {
	                            "value":  "12"
	                        }
	                    ],
	                    "name":  "D16S539"
	                },
	                {
	                    "allele": [
	                        {
	                            "value":  "14"
	                        },
	                        {
	                            "value":  "15"
	                        }
	                    ],
	                    "name":  "D18S51"
	                }
	            ],
	            "name":  "CEDU0059FR#07-KOEL0126LU#21"
	        },
	        {
	            "locus": [
	                {
	                    "allele": [
	                        {
	                            "value":  "11"
	                        },
	                        {
	                            "value":  "12"
	                        }
	                    ],
	                    "name":  "D16S539"
	                },
	                {
	                    "allele": [
	                        {
	                            "value":  "14"
	                        },
	                        {
	                            "value":  "15"
	                        }
	                    ],
	                    "name":  "D18S51"
	                }
	            ],
	            "name":  "KOEL0126LU#21"
	        },
	        {
	            "locus": [
	                {
	                    "allele": [
	                        {
	                            "value":  "11"
	                        },
	                        {
	                            "value":  "12"
	                        }
	                    ],
	                    "name":  "D16S539"
	                },
	                {
	                    "allele": [
	                        {
	                            "value":  "14"
	                        },
	                        {
	                            "value":  "15"
	                        }
	                    ],
	                    "name":  "D18S51"
	                }
	            ],
	            "name":  "CEDU0059FR#07"
	        }
	    ],
	    "properties": {
	        "property": [
	            {
	                "name": "property1",
	                "value": "value1"
	            },
	            {
	                "name": "property2",
	                "value": "value2"
	            }
	        ]
	    },
	    "version":  1.0
	}
        

Appendix F. Suggested queries for CODIS users Back to top

The following queries can be used when accessing a CODIS database directly:

• Specimen Keys Query

  select S.Spec_CD
  FROM CODIS.dbo.Specimen S
  WHERE S.Spec_Type_CD IN (
      Select Spec_Type_CD 
      from CODIS.dbo.Spec_Type 
      where Spec_Type IN ('Convicted Offender','Suspect, Known', 'Elimination, Known'))
  order by Spec_CD
  			

• Specimen Retrieval Query

  SELECT S.Spec_ID as [specimenId], L.Locus, rtrim(PV.PCR_Value) as [allele]
  FROM CODIS.dbo.Specimen S, CODIS.dbo.Locus L, CODIS.dbo.Sizing Z, CODIS.dbo.PCR_Value PV
  WHERE S.Spec_CD = Z.Spec_CD
  AND Z.Spec_CD = PV.Spec_CD 
  AND Z.Locus_CD = L.Locus_CD
  AND Z.Locus_CD = PV.Locus_CD  
  AND S.Spec_CD between ? and ?
  AND S.Spec_Type_CD IN (
      Select Spec_Type_CD 
      from CODIS.dbo.Spec_Type 
      where Spec_Type IN ('Convicted Offender','Suspect, Known', 'Elimination, Known'))
  group by s.spec_id, L.locus, PV.PCR_Value
  			

• Database Revision Query

  select max(Audit_CD)
  FROM CODIS.dbo.Audit_Trail