|
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;
|
|
|