Bonjour,

There are the steps to install Wikipedia database in mysql 5.1.

I make this migration from this file :
http://www.geonames.org/export/wikipedia/wikipedia.zip 


I modified some fields in the table wikipedia. I separed the field longlat in the 
file wikipedia.sql.txt and create two order in the table : longitude and latitude

Now the table look like this:

CREATE TABLE `wikipedia` (
  `wikipediaid` int(11) NOT NULL,
  `longitude` double DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `countrycode` char(2) DEFAULT NULL,
  `admincode1` varchar(10) DEFAULT NULL,
  `feature` varchar(30) DEFAULT NULL,
  `population` int(11) DEFAULT NULL,
  `altitude` int(11) DEFAULT NULL,
  `relevance` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The other tables are: 

CREATE TABLE `geoname_ds` (
  `geonameid` int(11) DEFAULT NULL,
  `userid` int(11) DEFAULT NULL,
  `nameorid` varchar(100) DEFAULT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


CREATE TABLE `wk` (
  `wikipediaid` int(11) DEFAULT NULL,
  `id` int(11) DEFAULT NULL,
  `lang` varchar(3) DEFAULT NULL,
  `title` varchar(300) DEFAULT NULL,
  `summary` varchar(500) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


You can create an index where it's necessary.


In the mysql client, connect geonames database and do this commands:
Note : change the path where unzip the files.


set sql_mode = '';

load data infile 'C:/Program Files/MySQL/MySQL Server 5.1/wikipedia/ds.sql.txt' INTO TABLE geoname_ds;
load data infile 'C:/Program Files/MySQL/MySQL Server 5.1/wikipedia/wikipedia.sql.txt' INTO TABLE wikipedia;
load data infile 'C:/Program Files/MySQL/MySQL Server 5.1/wikipedia/wk.sql.txt' INTO TABLE wk;

It's possible to have some null rows (25) in wikipedia table. Just drop it.


Now, create a POINT type field name gPoint: 

ALTER TABLE wikipedia
ADD gPoint POINT NOT NULL AFTER relevance;

Fill this field:
 
UPDATE wikipedia 
SET gPoint= PointFromText(CONCAT('POINT(',longitude,' ',latitude,')')); 


Create a index:

ALTER TABLE wikipedia
  ADD SPATIAL INDEX (gPoint);


For testing, do this :

SELECT `wikipedia`.wikipediaid, `wikipedia`.longitude, `wikipedia`.latitude,
       `wikipedia`.countrycode, `wikipedia`.admincode1, `wikipedia`.feature,
       `wikipedia`.population, `wikipedia`.altitude, `wikipedia`.relevance
  FROM geoname.wikipedia `wikipedia`

WHERE MBRContains(

GeomFromText(
'POLYGON((-73.8736944444 45.5482222222,-73.598056 45.71861100000002,-73.45000000000002 45.6000000000000,-73.71426 45.43735499999998,-73.8736944444 45.5482222222))'
), gPoint
)

Voil! Now, you can see my neighbours...

Pierre (CYUL)






