From fairmans at physik.hu-berlin.de  Sat Sep 14 15:52:29 2024
From: fairmans at physik.hu-berlin.de (Sam Fairman)
Date: Sat, 14 Sep 2024 14:52:29 +0200
Subject: [Cod-bugs] Information for PhD thesis
In-Reply-To: <CALHYoX6B5+vhJeQwcc5gag69ks6UZGP8wzO_65574cCCiST-VA@mail.gmail.com>
References: <24021c4c-d173-4333-9570-8e0d2a7b282d@physik.hu-berlin.de>
 <545c4751-feb3-4440-b8aa-7495d33b0fe8@physik.hu-berlin.de>
 <CALHYoX6B5+vhJeQwcc5gag69ks6UZGP8wzO_65574cCCiST-VA@mail.gmail.com>
Message-ID: <e85bd46c-7ef0-45d0-bc99-93c64b220848@physik.hu-berlin.de>

Dear Antanas,

Thank you very much for the detailed answer!!!

Best Regards,

Sam

On 03.09.24 13:42, Antanas Vaitkus wrote:
> 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 
> <http://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 
> <http://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 
> <http://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
>
>
-- 
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.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.crystallography.net/pipermail/cod-bugs/attachments/20240914/e7d067e7/attachment.htm>