Archive for August, 2010

Converting IP addresses to lat / lng and plotting them on a map with php / ajax

Monday, August 30th, 2010

I love google analytics but the data is quite “slow” meaning you can not get real time updates, so I decided to set up my own tracking service for the sites I maintain. But I did not want to just keep track of my visitors I also wanted to see where the come from.

The setup / What you need

A map – Its important to know that you have to use a mercator projection – otherwise the latitude and longitude do not convert correctly to x and y values on the map

A copy of the free GEO Ip database – there are two versions, an Apache module and a CSV version. I used the csv one and imported it into a sql database note:The sql version is much slower, but easier to set up.

How to use the GEO IP Database

Setting up the GEO IP database

I used sqlpro alongside this guide to import the csv file in a sql database. This is pretty straight forward.

Converting an IP address to lat / lng

function ip_to_lat_lng($ip){

$sql="SELECT * FROM geo_locations WHERE locId = (SELECT location_id FROM geo_blocks WHERE INET_ATON('".$ip."') BETWEEN ip_start AND ip_end)";

$result= mysql_query($sql) or die(mysql_error());

return mysql_fetch_object($result);


this sql statement takes an IP adress and returns a php object which has a latitude and longitude property

Mapping lat / lng to x and y on a 2D map

so not that we have our lat and long we need to map the on the 2d coordinate system on our map.

This is wrong:


This is right:

function lat_to_y(lat){return 180/Math.PI * Math.log(Math.tan(Math.PI/4+lat*(Math.PI/180)/2));}

shift_x= 0 // depending on your map, you might have to move the entire projection a bit
x=map_width/2+map_width/360*lng +shift_x

Its important to account for the distortion from projecting a spherical map on a 2d flat surface. The function lat_to_y takes care of that. If you are interested in the math behind it take a look at this wikipedia article.