Erica Zhang
Joined: 19/07/2007 06:39:00
Messages: 12
Offline
|
Well, I just executed the following script and import the tables and use indices.
CREATE TABLE IF NOT EXISTS alternatename (
alternateNameId INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT
'the id of this alternate name',
geonameid INT(10) NULL COMMENT 'geonameId referring to id in table geoname',
isolanguage VARCHAR(4) NULL COMMENT 'iso 693 language code 2- or
3-characters; 4-characters post for postal codes and iata or icao for
airport codes',
alternate_name VARCHAR(200) NULL COMMENT 'alternate name or name variant',
isPreferredName INT(1) NULL COMMENT '1 if this alternate name is an
official/preferred name',
isShortName INT(1) NULL COMMENT '1, if this is a short name like
California for (State of California)'
);
CREATE TABLE IF NOT EXISTS continentcode (
geonameid INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'the
id of continents in geoname table',
name VARCHAR(100) NULL COMMENT 'name of continent',
code VARCHAR(2) NULL COMMENT 'code of continent'
);
REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES
('6255146', 'Africa', 'AF');
REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES
('6255147', 'Asia', 'AS');
REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES
('6255148', 'Europa', 'EU');
REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES
('6255149', 'North America', 'NA');
REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES
('6255151', 'Oceania', 'OC');
REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES
('6255150', 'South America', 'SA');
REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES
('6255152', 'Antarctica', 'AN');
CREATE TABLE IF NOT EXISTS admincodes1 (
code VARCHAR(15) NOT NULL PRIMARY KEY,
name VARCHAR(100) NULL
);
CREATE TABLE IF NOT EXISTS admincodes2 (
code VARCHAR(15) NOT NULL PRIMARY KEY,
name VARCHAR(100) NULL,
name2 VARCHAR(100) NULL,
geonameid INT(10) NULL
);
CREATE TABLE IF NOT EXISTS countryinfo (
iso_alpha2 CHAR(2) NULL, INDEX (iso_alpha2),
iso_alpha3 CHAR(3) NULL,
iso_numeric INT(10) NULL,
fips CHAR(3) NULL,
name VARCHAR(200) NULL, INDEX (name),
capital VARCHAR(200) NULL,
areainsqkm DECIMAL(20,1) NULL,
population INT(20) NULL,
continent CHAR(2) NULL,
languages VARCHAR(200) NULL,
other1 VARCHAR(3) NULL,
other2 INT(20) NULL
);
LOAD DATA INFILE '/export/home/thesis_back/projects/countryInfo.txt'
REPLACE INTO TABLE countryinfo FIELDS TERMINATED BY '\t';
LOAD DATA INFILE '/export/home/thesis_back/projects/admin1Codes.txt'
REPLACE INTO TABLE admincodes1 FIELDS TERMINATED BY '\t';
LOAD DATA INFILE '/export/home/thesis_back/projects/admin2Codes.txt'
REPLACE INTO TABLE admincodes2 FIELDS TERMINATED BY '\t';
LOAD DATA INFILE
'/export/home/thesis_back/projects/alternateNames.txt' REPLACE INTO
TABLE alternatename FIELDS TERMINATED BY '\t';
Then I want to execute following commands :
sqlstr="select count(distinct country_code, admin1_code) from geoname
where latitude<='" + w + "' AND latitude>='" + y + "' AND
longitude<='" + z + "' AND longitude>='" + x +"'";
....
sqlstr="select
g.geonameId,g.asciiname,g.latitude,g.longitude,c.name from geoname g
LEFT JOIN countryinfo c ON c.iso_alpha2=g.country_code where
country_code='" + country + "' AND admin1_code='" + area + "' AND
feature_class='P' AND feature_code='PPLC'" +" LIMIT 0,"+
String.valueOf(percount);
.....
sqlstr="select
g.geonameId,g.asciiname,g.latitude,g.longitude,g.feature_code,c.name
from geoname g LEFT JOIN countryinfo c ON c.iso_alpha2=g.country_code
where country_code='" + country + "' AND admin1_code='" + area + "'
AND feature_class='P' AND (feature_code='PPLC' OR
feature_code='PPLA')" +" LIMIT 0,"+ String.valueOf(percount);
....
sqlstr="select
g.geonameId,g.asciiname,g.latitude,g.longitude,g.feature_code,c.name
from geoname g LEFT JOIN countryinfo c ON c.iso_alpha2=g.country_code
where country_code='" + country + "' AND admin1_code='" + area + "'
AND feature_class='P' AND (feature_code='PPLA' OR feature_code='PPL')"
+" LIMIT 0,"+ String.valueOf(percount);
.....
I use MySQL 5 . I am new to database. I hope you or somebody could help me to improve the performance.
Regards,
Erica
|