devCode

Nowaday every blogger also have free visitors map on thier side bar that power by the following providers,

• ClustrMaps
• NiftyMaps
• VisitorMaps
• IP2Map
• MapLoco
• etc...

Last week, I decided to implement my own IP to location information services for my blog, and I keep sourcing the free Geo IP information database. At first, I found the ipligence community free edition. Which only offers,

•    Region Code/Name.
•    Country Code/Name.

Later, I found a better free database from IPInfoDB, which offer a much more comprehensive IP Geo information. As it cover the following informations,

•    Country Code/Name.
•    Region Code/Name.
•    City Name.
•    Zip Code.
•    GPS Latitude/Longitude.
•    GMT Offset.
•    DST Offset.

While the entire database is about 95MB (3,486,705 records), which are will best fit into the normal webhosting offer MySQL database size limit (100MB/each database). Another advantage of the IPInfoDB database is they will have a new update on every first week of each month.

Since, I have implemented and I would like to post it up and share with other who need it. Below is the only one SQL query statement used to retrieve the data.

   1:  SELECT l.country_code, c.name AS country_name, l.region_code, r.name as region_name, 

   2:         l.city, l.zipcode, l.latitude, l.longitude, l.gmtoffset, l.dstoffset

   3:  FROM ip_group_city AS i

   4:      LEFT JOIN locations AS l ON i.location = l.id

   5:      LEFT JOIN iso3166_countries AS c ON l.country_code = c.code

   6:      LEFT JOIN fips_regions AS r ON l.region_code = r.code AND l.country_code = r.country_code

   7:  WHERE i.ip_start <= INET_ATON('76.238.227.61')

   8:  ORDER BY i.ip_start DESC LIMIT 1;

The result will be further formated into XML show below,

   1:  <LookUp>

   2:    <Item>

   3:      <IP>76.238.227.61</IP> 

   4:      <CountryCode>US</CountryCode> 

   5:      <CountryName>United States</CountryName> 

   6:      <RegionCode>06</RegionCode> 

   7:      <RegionName>California</RegionName> 

   8:      <City>Vista</City> 

   9:      <ZipCode>92081</ZipCode> 

  10:      <Latitude>33.2007</Latitude> 

  11:      <Longitude>-117.226</Longitude> 

  12:      <GmtOffset>-8</GmtOffset> 

  13:      <DstOffset>-7</DstOffset> 

  14:    </Item>

  15:  </LookUp>

Please download the free database from IPInfoDB while you downloading this sample project (Ip2Location.zip).

Ip2Location.zip (20.92 kb)

Services Services, IP Geo Information, ASP.NET