The following lists allows to plot the specimen grouped by:

Mineralogical Class
Alogenuri  -    (32 pieces)
Borati  -    (3 pieces)
Carbonati e Nitrati  -    (91 pieces)
Elementi Nativi  -    (16 pieces)
Fosfati  -    (25 pieces)
Ossidi e Idrossidi  -    (149 pieces)
Roccia  -    (1 pieces)
Silicati (ciclosilicati)  -    (31 pieces)
Silicati (fillosilicati)  -    (45 pieces)
Silicati (inosilicati)  -    (31 pieces)
Silicati (nesosilicati)  -    (47 pieces)
Silicati (sorosilicati)  -    (31 pieces)
Silicati (tectosilicati)  -    (47 pieces)
Solfati  -    (69 pieces)
Solfuri  -    (82 pieces)

DATABASE SCHEMA

Table that backups the 'Collezione Franchin'.

CREATE TABLE campioni (
idcampione int(11) NOT NULL auto_increment,
Analista varchar(50) character set utf8 default NULL,
Etichetta_Franchin varchar(255) character set utf8 default NULL,
Sigla_assegnata varchar(50) character set utf8 default NULL,
Nome varchar(255) character set utf8 default NULL,
Formula_Chimica varchar(100) character set utf8 default NULL,
Classe varchar(100) character set utf8 default NULL,
Sistema varchar(100) character set utf8 default NULL,
Durezza varchar(50) character set utf8 default NULL,
PesoSP varchar(50) character set utf8 default NULL,
Proprieta_Fisiche_generali varchar(1000) character set utf8 default NULL,
Proprieta_fisiche_campione varchar(1000) character set utf8 default NULL,
Peculiarita varchar(500) character set utf8 default NULL,
NomeFoto varchar(255) character set utf8 default NULL,
Localita varchar(45) character set utf8 default NULL,
PRIMARY KEY (idcampione)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Table containing just the KEY field ('Sigla_assegnata')and Lat/Lon values: the result of geocoding procedure on the 'Localita' attribute of 'campioni' table.

CREATE TABLE camp_loc (
Sigla_assegnata varchar(50) default NULL,
latitudine double default NULL,
longitudine double default NULL,
geocode_addr varchar(100) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Table containing tha Country boundaries.

CREATE TABLE countries (
ID int(11) NOT NULL,
`NAME` varchar(255) default NULL,
GMI_CNTRY varchar(255) default NULL,
REGION varchar(255) default NULL,
thegeom geometry NOT NULL,
PRIMARY KEY (ID),
SPATIAL KEY thegeom (thegeom(32))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Table containing just the KEY and a POINT (OGC geometry data type).

CREATE TABLE locations (
Sigla_assegnata varchar(50) default NULL,
thegeom point NOT NULL,
SPATIAL KEY thegeom (thegeom(32))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Country

Note:The maps are drawn using MySQL Spatial functions.
Currently, MySQL does not implement these functions according to the OGC specification (official docs). Those that are implemented return the same result as the corresponding [Minimum Bounding Rectangles]-based functions.
This causes many specimen to be considered coming from more than one country.

The SQL used for maps is:
SELECT l.Sigla_assegnata,cl.latitudine,cl.longitudine FROM countries c, locations l,camp_loc cl WHERE contains(c.thegeom,l.thegeom) AND c.NAME='$countryname' AND l.Sigla_assegnata=cl.Sigla_assegnata;

The SQL used for the following count-list:
SELECT c.NAME As Country, count(c.NAME) As Count FROM countries c, locations l WHERE contains(c.thegeom,l.thegeom) GROUP BY c.NAME ORDER BY c.NAME;

Afghanistan  -    (27 pieces)
Algeria  -    (37 pieces)
Angola  -    (2 pieces)
Argentina  -    (5 pieces)
Australia  -    (10 pieces)
Austria  -    (20 pieces)
Azerbaijan  -    (1 pieces)
Belgium  -    (1 pieces)
Bolivia  -    (16 pieces)
Brazil  -    (62 pieces)
Bulgaria  -    (2 pieces)
Byelarus  -    (3 pieces)
Canada  -    (17 pieces)
Chile  -    (8 pieces)
China  -    (79 pieces)
Congo  -    (3 pieces)
Czech Republic  -    (9 pieces)
Denmark  -    (1 pieces)
Estonia  -    (1 pieces)
Finland  -    (1 pieces)
France  -    (77 pieces)
Gabon  -    (1 pieces)
Georgia  -    (1 pieces)
Germany  -    (10 pieces)
Ghana  -    (1 pieces)
Greece  -    (6 pieces)
Greenland  -    (1 pieces)
Guinea  -    (1 pieces)
India  -    (67 pieces)
Iran  -    (1 pieces)
Italy  -    (173 pieces)
Japan  -    (9 pieces)
Kazakhstan  -    (11 pieces)
Libya  -    (2 pieces)
Madagascar  -    (11 pieces)
Malawi  -    (2 pieces)
Mexico  -    (30 pieces)
Morocco  -    (31 pieces)
Mozambique  -    (2 pieces)
Namibia  -    (14 pieces)
New Zealand  -    (7 pieces)
Norway  -    (6 pieces)
Pakistan  -    (34 pieces)
Peru  -    (31 pieces)
Philippines  -    (1 pieces)
Poland  -    (11 pieces)
Portugal  -    (3 pieces)
Romania  -    (34 pieces)
Russia  -    (300 pieces)
Slovakia  -    (2 pieces)
South Africa  -    (8 pieces)
Spain  -    (52 pieces)
Sweden  -    (3 pieces)
Switzerland  -    (30 pieces)
Tajikistan  -    (1 pieces)
Togo  -    (1 pieces)
Tunisia  -    (6 pieces)
Turkey  -    (1 pieces)
Turkmenistan  -    (2 pieces)
Ukraine  -    (35 pieces)
United Kingdom  -    (1 pieces)
United States  -    (510 pieces)
Uruguay  -    (1 pieces)
Uzbekistan  -    (2 pieces)
Vietnam  -    (2 pieces)
Yugoslavia  -    (9 pieces)
Zaire  -    (11 pieces)
Zambia  -    (3 pieces)
 
Back to McFoi Homepage