<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Dear Antanas,</p>
<p>Thank you very much for the detailed answer!!!</p>
<p>Best Regards,</p>
<p>Sam<br>
</p>
<div class="moz-cite-prefix">On 03.09.24 13:42, Antanas Vaitkus
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:CALHYoX6B5+vhJeQwcc5gag69ks6UZGP8wzO_65574cCCiST-VA@mail.gmail.com">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<div dir="ltr">
<div>
<div>Hello, Sam,<br>
<br>
</div>
<div>The COD allows to retrieve core pieces of information
(unit cell constants, determination<br>
method, bibliography, etc.) by directly querying the MySQL
database [1].<br>
<br>
</div>
<div>The following queries might be useful to you:<br>
<br>
</div>
<div>1. Get the number of entries deposited in the COD each
year:<br>
<br>
mysql -u cod_reader -h <a
href="http://www.crystallography.net"
moz-do-not-send="true">www.crystallography.net</a> cod -e
\<br>
'SELECT year, count(*) FROM data GROUP BY year ORDER BY
year DESC';</div>
<div><br>
</div>
<div>Note, that there are ~500 COD entries with the NULL year.
These entries are<br>
either prepublication entries that are still to be publicly
released or historic entries<br>
</div>
<div>with incomplete information.<br>
</div>
<div><br>
</div>
<div>2. Get information on entries solved using electron
diffraction*:<br>
<br>
mysql -u cod_reader -h <a
href="http://www.crystallography.net"
moz-do-not-send="true">www.crystallography.net</a> cod -e
\<br>
'SELECT file, year, radiation, radType, onhold FROM data<br>
WHERE radiation LIKE "%electron%" OR \<br>
radType LIKE "%electron%" order by year desc, file
desc;'<br>
<br>
</div>
Note, however, that this query includes all entries where the
radiation<br>
type is declared as the probing particle (including
transmission electron<br>
microscope and similar techniques), therefore you might need
to further<br>
refine the query (e.g. filter on the publication title) or
even do some manual<br>
filtering based on your criteria. Currently, there are only
217 such entries in<br>
the COD so this should still be feasible.<br>
<br>
</div>
<div>For entries without a year, the 'onhold' field can be used
to approximate the<br>
</div>
<div>deposition date since it corresponds to the planned public
release date of<br>
</div>
<div>the entries.<br>
<br>
</div>
<div>As for comparing the number of structures solved via by
electron diffraction<br>
vs XRD, you can more or less reasonably assume that all
entries that were<br>
not solved via electron diffraction or neutron diffraction
were solved by<br>
</div>
<div>XRD (single crystal, powder, etc.). Then the per-year XRD
entry deposition<br>
</div>
<div>can be retrieved by slightly modifying the first query:<br>
<br>
mysql -u cod_reader -h <a
href="http://www.crystallography.net" moz-do-not-send="true">www.crystallography.net</a>
cod -e \<br>
'SELECT year, count(*) FROM data \<br>
WHERE radiation is NULL OR \<br>
( \<br>
radiation NOT LIKE "%electron%" AND \<br>
radiation NOT LIKE "%neutron%" \<br>
) AND \<br>
radType is NULL OR \<br>
( \<br>
radType NOT LIKE "%electron%" AND \<br>
radType NOT LIKE "%neutron%" \<br>
) \<br>
GROUP BY year ORDER BY year DESC';</div>
<div><br>
</div>
<div>I attach files with the query results that were run today
(2024-09-03) for your<br>
</div>
<div>convenience.<br>
</div>
<div>
<div><br>
[1] <a
href="https://wiki.crystallography.net/howtoquerycod/"
moz-do-not-send="true" class="moz-txt-link-freetext">https://wiki.crystallography.net/howtoquerycod/</a></div>
<div><br>
</div>
<div>Hope this helps and please let us know if you have any
further questions.<br>
</div>
<div><br>
</div>
Sincerely<br>
</div>
Antanas<br>
</div>
<br>
<div class="gmail_quote">
<div dir="ltr" class="gmail_attr">On Tue, 3 Sept 2024 at 09:18,
Sam Fairman <<a href="mailto:fairmans@physik.hu-berlin.de"
moz-do-not-send="true" class="moz-txt-link-freetext">fairmans@physik.hu-berlin.de</a>>
wrote:<br>
</div>
<blockquote class="gmail_quote"
style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Hello,<br>
<br>
I would like to get some information for my PhD defense. I
would like to <br>
show plots of the number of structures added to the database
by year, <br>
and also compare numbers of structures by year solved by
electron <br>
diffraction vs XRD. Would you be able to provide me with that
<br>
information? I tried to download the whole database to get the
numbers <br>
myself but it's too large for my PC.<br>
<br>
-- <br>
Best regards,<br>
Sam Fairman<br>
PhD Student<br>
Humboldt Universität zu Berlin<br>
Department of Physics<br>
Structure Research & Electron Microscopy group<br>
Room 3’305<br>
Newtonstraße 15<br>
12489 Berlin, Germany<br>
+49 30 20937866<br>
<br>
<br>
-- <br>
This message has been scanned for viruses and<br>
dangerous content by MailScanner, and is<br>
believed to be clean.<br>
<br>
_______________________________________________<br>
Cod-bugs mailing list<br>
<a href="mailto:Cod-bugs@lists.crystallography.net"
target="_blank" moz-do-not-send="true"
class="moz-txt-link-freetext">Cod-bugs@lists.crystallography.net</a><br>
<a
href="http://lists.crystallography.net/cgi-bin/mailman/listinfo/cod-bugs"
rel="noreferrer" target="_blank" moz-do-not-send="true"
class="moz-txt-link-freetext">http://lists.crystallography.net/cgi-bin/mailman/listinfo/cod-bugs</a><br>
</blockquote>
</div>
<br clear="all">
<br>
<span class="gmail_signature_prefix">-- </span><br>
<div dir="ltr" class="gmail_signature">
<div dir="ltr">
<div>
<div>Antanas Vaitkus,<br>
</div>
Vilnius University,<br>
Life Sciences Center,<br>
Institute of Biotechnology,<br>
<span><span><span>room C521, </span></span></span>Saulėtekio
al. 7,<br>
LT-10257 Vilnius, Lithuania<br>
</div>
<div>
<div>
<div>
<div>
<div dir="ltr">
<div>
<div dir="ltr">
<div>
<div dir="ltr"><br>
<br>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</blockquote>
<pre class="moz-signature" cols="72">--
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</pre>
<br />--
<br />This message has been scanned for viruses and
<br />dangerous content by
<a href="http://www.mailscanner.info/"><b>MailScanner</b></a>, and is
<br />believed to be clean.
</body>
</html>