PostgreSQL Extensions - Design and Use of Relational Databases in Chemistry - page 116

It may be desirable to keep 3-D and 2-D coordinates in separate tables.
However, it is possible to mix them in the same table. The following SQL will
insert 2-D coordinates for 1-methylthymine into coordtest as created above.
Insert Into coordtest (smiles,name,coord) Values
('CN1C=C(C)C(=O)NC1=O','1-methylthymine', '{
{-0.0000,-0.8250},
{0.7145,-0.4125},
{0.7145,0.4125},
{0.0000,0.8250},
{-0.7145,0.4125},
{-0.7145,-0.4125},
{1.4289,0.8250},
{-0.0000,-1.6500},
{-1.4289,-0.8250},
{0.0000,1.6500}}');
The array _ upper function can be used to determine the actual dimen-
sions of an array. The following SQL would select only the 2-D coordi-
nates arrays from the coordtest table.
Select smiles, coord from coordtest where array_upper(coord,2) = 2;
Each row in the coordtest table represents a molecule. The smiles col-
umn is a string of atom symbols and bonds and the coord column is an
array of atom coordinates. How is it possible to keep the ordering of atoms
in the smiles string in sync with the ordering of atom coordinates in the
coord array? When the coordinates are initially entered from the external
source, they are likely to be in a common chemical file format. The pro-
gram that converts from that file format to SMILES would have to output
the atom coordinates in the same order as the atoms in the SMILES.
In order to keep the appropriate SMILES associated with the corre-
sponding coordinates, consider using a new data type. For example:
Create Type mol (smiles Text, coord Float[][3]);
This type could be used in a table that might also contain a canonical
smiles column, or even other variants of SMILES if desired.
Create Table moltest (amol mol, name Text);
When this table is initially populated, the amol.smiles and amol.coord
would be taken from the conversion program, as would the name.
Insert Into moltest (amol,name) Values (
('CN1C=C(C)C(=O)NC1=O','{
{-0.5223,-1.2374,0.2579},