http://forum.geonames.org/gforum/posts/listByUser/352.page JForum - http://www.jforum.net # open mysql command line mysql --default-character-set=utf8 -u root -p # set character-set to utf8 SET NAMES 'utf8'; # create the database CREATE DATABASE `geonames`; # use the database USE `geonames`; # create the allCountries table for example CREATE TABLE `geonames`.`allCountries` ( `geonameId` int(10) unsigned NOT NULL default '0', `name` varchar(200) NOT NULL default '', `ansiName` varchar(200) NOT NULL default '', `alternateNames` varchar(2000) NOT NULL default '', `latitude` double NOT NULL default '0', `longitude` double NOT NULL default '0', `featureClass` char(1) , `featureCode` varchar(10) , `countryCode` char(2), `cc2` varchar(60), `admin1Code` varchar(20) default '', `admin2Code` varchar(80) default '', `admin3Code` varchar(20) default '', `admin4Code` varchar(20) default '', `population` bigint(11) default '0', `elevation` int(11) default '0', `gtopo30` int(11) default '0', `timezone` varchar(40), `modificationDate` date default '0000-00-00', PRIMARY KEY (`geonameid`) ) CHARACTER SET utf8 ; # here I inserted the character-set property that works for me LOAD DATA INFILE '/tmp/geonames/allCountries.txt' INTO TABLE `geonames`.`allCountries` CHARACTER SET utf8 (geonameId,name,ansiName,alternateNames,latitude,longitude,featureClass,featureCode,countryCode,cc2,admin1Code,admin2Code,admin3Code,admin4Code,population,elevation,gtopo30,timezone,modificationDate); # shoud be over 7 million entries (2010, January) SELECT count(*) FROM `geonames`.`allCountries`; # now you could use the MySQL geo spatial extensions and add the new (geo) column "coordinate" ALTER TABLE `geonames`.`allCountries` ADD COLUMN `coordinate` POINT NOT NULL AFTER `longitude`; # fill the new column "coordinate" with data (lat, lon) from the allCountries table UPDATE `geonames`.`allCountries` SET coordinate = PointFromText(CONCAT('POINT(',latitude,' ',longitude,')')); # set an index to the new geo point "coordinate". It MUST be MyISAM, see MySQL documentation ALTER TABLE `geonames`.`allCountries` ADD SPATIAL INDEX `coordinate`(`coordinate`), ENGINE = MyISAM; # set some MySQL statement variables, e.g. a small bounding box of part of Berlin, Germany SET @NE_Latitude=52.45904246505317, @NE_Longitude=13.354050815105438; # northEast coords Berlin-Stegitz, Germany SET @SW_Latitude=52.44811060147279, @SW_Longitude=13.321183025836945; # southWest coords Berlin-Stegitz, Germany select * from `geonames`.`allCountries` where MBRContains( GeomFromText(CONCAT('Polygon((',@SW_Latitude,' ',@SW_Longitude,', ',@NE_Latitude,' ',@SW_Longitude,', ',@NE_Latitude,' ',@NE_Longitude,', ',@SW_Latitude,' ',@NE_Longitude,', ',@SW_Latitude,' ',@SW_Longitude,'))')), coordinate ); # here is same example, if you're using it in your code select * from `geonames`.`allCountries` where MBRContains( GeomFromText('Polygon((52.44811060147279 13.321183025836945, 52.45904246505317 13.321183025836945, 52.45904246505317 13.354050815105438, 52.44811060147279 13.354050815105438, 52.44811060147279 13.321183025836945))'), coordinate );   I hope, that helps :) So, I do have also a question. I want to get the closest/nearest point to a point in that table. Yes, I could build a bounding box with a rectangle of e.g. 1 mile. But, is there not an more easier way to find the closest point by a coordinate? Thanks! Best regards, Klaus http://www.gpsies.com/]]> http://forum.geonames.org/gforum/posts/preList/732/7060.page#7060 http://forum.geonames.org/gforum/posts/preList/732/7060.page#7060 GMT http://forum.geonames.org/gforum/posts/preList/732/7059.page#7059 http://forum.geonames.org/gforum/posts/preList/732/7059.page#7059 GMT http://forum.geonames.org/gforum/posts/preList/1223/5237.page#5237 http://forum.geonames.org/gforum/posts/preList/1223/5237.page#5237 GMT GPSies.com) using the webservice of geonames.org about 500-1.000 times over the hole day by the service "findNearbyPostalCodes". I'm a heavy user by definition? Where is the limit? Should I better decide to download the database dumps? Thank you for your fantastic service! Best regards from Berlin, Klaus]]> http://forum.geonames.org/gforum/posts/preList/1223/5220.page#5220 http://forum.geonames.org/gforum/posts/preList/1223/5220.page#5220 GMT