[Cod-bugs] Information for PhD thesis

[Antanas Vaitkus]

Hello, Sam,

The COD allows to retrieve core pieces of information (unit cell constants,
determination
method, bibliography, etc.) by directly querying the MySQL database [1].

The following queries might be useful to you:

1. Get the number of entries deposited in the COD each year:

mysql -u cod_reader -h www.crystallography.net cod -e \
    'SELECT year, count(*) FROM data GROUP BY year ORDER BY year DESC';

Note, that there are ~500 COD entries with the NULL year. These entries are
either prepublication entries that are still to be publicly released or
historic entries
with incomplete information.

2. Get information on entries solved using electron diffraction*:

mysql -u cod_reader -h www.crystallography.net cod -e \
    'SELECT file, year, radiation, radType, onhold FROM data
     WHERE radiation LIKE "%electron%" OR \
     radType LIKE "%electron%" order by year desc, file desc;'

Note, however, that this query includes all entries where the radiation
type is declared as the probing particle (including transmission electron
microscope and similar techniques), therefore you might need to further
refine the query (e.g. filter on the publication title) or even do some
manual
filtering based on your criteria. Currently, there are only 217 such
entries in
the COD so this should still be feasible.

For entries without a year, the 'onhold' field can be used to approximate
the
deposition date since it corresponds to the planned public release date of
the entries.

As for comparing the number of structures solved via by electron diffraction
vs XRD, you can more or less reasonably assume that all entries that were
not solved via electron diffraction or neutron diffraction were solved by
XRD (single crystal, powder, etc.). Then the per-year XRD entry deposition
can be retrieved by slightly modifying the first query:

mysql -u cod_reader -h www.crystallography.net cod -e \
    'SELECT year, count(*) FROM data \
     WHERE radiation is NULL OR \
           ( \
             radiation NOT LIKE "%electron%" AND \
             radiation NOT LIKE "%neutron%" \
           ) AND \
           radType is NULL OR \
           ( \
             radType NOT LIKE "%electron%" AND \
             radType NOT LIKE "%neutron%" \
           ) \
     GROUP BY year ORDER BY year DESC';

I attach files with the query results that were run today (2024-09-03) for
your
convenience.

[1] https://wiki.crystallography.net/howtoquerycod/

Hope this helps and please let us know if you have any further questions.

Sincerely
Antanas

On Tue, 3 Sept 2024 at 09:18, Sam Fairman <fairmans at physik.hu-berlin.de>
wrote:

> Hello,
>
> I would like to get some information for my PhD defense. I would like to
> show plots of the number of structures added to the database by year,
> and also compare numbers of structures by year solved by electron
> diffraction vs XRD. Would you be able to provide me with that
> information? I tried to download the whole database to get the numbers
> myself but it's too large for my PC.
>
> --
> Best regards,
> Sam Fairman
> PhD Student
> Humboldt Universität zu Berlin
> Department of Physics
> Structure Research & Electron Microscopy group
> Room 3’305
> Newtonstraße 15
> 12489 Berlin, Germany
> +49 30 20937866
>
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>
> _______________________________________________
> Cod-bugs mailing list
> Cod-bugs at lists.crystallography.net
> http://lists.crystallography.net/cgi-bin/mailman/listinfo/cod-bugs
>


-- 
Antanas Vaitkus,
Vilnius University,
Life Sciences Center,
Institute of Biotechnology,
room C521, Saulėtekio al. 7,
LT-10257 Vilnius, Lithuania

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.crystallography.net/pipermail/cod-bugs/attachments/20240903/22dda221/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: cod-growth-by-year_2024-09-03.tsv
Type: text/tab-separated-values
Size: 1039 bytes
Desc: not available
URL: <http://lists.crystallography.net/pipermail/cod-bugs/attachments/20240903/22dda221/attachment.tsv>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: cod-electron-probe_2024-09-03.tsv
Type: text/tab-separated-values
Size: 7845 bytes
Desc: not available
URL: <http://lists.crystallography.net/pipermail/cod-bugs/attachments/20240903/22dda221/attachment-0001.tsv>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: cod-growth-by-year-XRD_2024-09-03.tsv
Type: text/tab-separated-values
Size: 1039 bytes
Desc: not available
URL: <http://lists.crystallography.net/pipermail/cod-bugs/attachments/20240903/22dda221/attachment-0002.tsv>