Contributed by Rick Cusimano of Richlyn Systems on
November 9th, 2006

***********************************************************
* How to install the MaxMind GEO Country and GEO City     *
* CSV's into an MS Access Database                        *
***********************************************************

Latest GEO IP Country CSV's available at:
http://www.maxmind.com/app/geoip_country

Latest GEO IP City CSV's available at:
http://www.maxmind.com/app/geolitecity



***********************************************************
* How to install the GEO IP Country CSV's                 *
***********************************************************

1. Import the CSV's into a new table

2. Delete the IP String and the Country Name fields, they can be hard coded elsewhere

3. Rename the remaining fields startIPNum, endIPNum, countryCode

4. Change the types to be Double, Double, Text(2)

5. Now we need to make the IP Numbers fit in the range of a Long Int 
   (at the moment they're unsigned). Run the following queries on the table:

   UPDATE GeoIPCountryWhois SET startIPNum = startIPNum - 2147483648
   UPDATE GeoIPCountryWhois SET endIPNum = endIPNum - 2147483648
   
6. Change the type of the startIPNum and endIPNum fields to be Long Integer

7. Copy the structure only of the Country and Country_Blocks tables into new tables

8. Run the following query to copy the countryCode into the new CountryNew table:

   INSERT INTO CountryNew SELECT DISTINCT countryCode FROM GeoIPCountryWhois;
   
9. Run the following query to copy the startIPNum and endIPNum into the new Country_BlocksNew table:

   INSERT INTO Country_BlocksNew SELECT startIPNum,endIPNum,locID FROM GeoIPCountryWhois LEFT JOIN CountryNew ON GeoIPCountryWhois.countryCode = CountryNew.countryCode

10. Change the locID type to be Byte in the Country_BlocksNew table

11. Delete the old tables, and rename the new 

12. Compact and repair the database


***********************************************************
* How to install the GEO IP City CSV's                    *
***********************************************************

1. These should come as 2 seperate CSV's, one for the blocks, and one for the locations.
   Import both of these into new tables as follows:
   
   Blocks(startIPNum Double, endIPNum Double, locID Long Int)
   Location(locID Long Int, country Text(2), region Text(2), city Text(40), latitude Single, longitude Single) 
   
2. Now we need to make the IP Numbers fit in the range of a Long Int 
   (at the moment they're unsigned). Run the following queries on the table:

   UPDATE Blocks SET startIPNum = startIPNum - 2147483648
   UPDATE Blocks SET endIPNum = endIPNum - 2147483648   
   
3. Change the type of the startIPNum and endIPNum fields to be Long Integer   

4. The only field in the two tables to be indexed should be Location(locID), it is also the only primary key

5. Now you're left with 2 tables, one of which should have around 3,000,000 records.
   This is simply too big a table for access to efficiently run queries.  We have to split the table up.
   After some testing it seems that splitting the table into 16 tables seems to work well.
   
   Copy the structure ONLY of the Blocks table into 15 other tables, Blocks1..15
   
6. Calculate how many records should go into each table as follows
   n = Ceil(Blocks->recordCount / 16)

7. Use the following queries to repeatedly copy the top n rows from the Blocks table
   and insert them into the Blocks1..15 tables.  On each iteration delete the racords from the
   Blocks table.
   
   INSERT INTO BLOCKS1 SELECT TOP 175948 * FROM BLOCKS
 
   DELETE FROM BLOCKS WHERE STARTIPNUM IN (SELECT STARTIPNUM FROM BLOCKS1)
   
   NOTE: The records in the Blocks table should be sorted by startIPNum.  
   If for some reason they're not you'll have to sort the table in the queries

8. Now you should be left with the Blocks table, which should now contain ~ n rows.
   Rename this table to be Blocks16
   
9. Compact and repair the database