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

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.



App for the milk ? version 0360 released

Finally a new version!

New in this version:

  • Autosyncing

This can be set in the syncing tab in the application settings. You can specify a custom interval in minutes. Do not use a value below 10 minutes. It will put unnecessary strain on the Remember the milk servers.


get version 0360


If you encounter any problems please use the new forums.



How to integrate bb-press into your wordpress theme

If you run a wordpress blog bb-press is the forum of choice since it integrates so well with WordPress. The only thing it lacks is the ability to integrate into your wordpress theme nicely, there is however a rather simple hack that makes a tighter integration possible. For the general setup and integration of bb-press please checkout this guide.

Functional integration

I want to focus on the theme part of it, which I think is just as important. So once you have your blog and your forum running you need to add a few lines of code to your bb-config.php file.

 * bbPress Database Table prefix.
 * You can have multiple installations in one database if you give each a unique
 * prefix. Only numbers, letters, and underscores please!
$bb_table_prefix = 'bb_';


note: PATH_TO_WORDPRESS has to be the absolute path to the root folder since it will be called from different files within bb-press and a relative path might throw an error.

Theme header

Ok now we can use all of the wordpress theme tags in the bb-press theme. I recommend building uppon the “kakumei” that you already have but you can start from scratch as well.

In most cases you will want to have the same header and footer used in your blogs theme. You basically have three options

You can call the get_header() method directly inside header.php file in the bb-press theme:

  • remove all bb-press html code or you will end up with two html headers and a huge mess
  • this has the advantage that you only have to change the header file and you do not have to alter the other files

You can remove all calls of bb_get_header() and replace it with get_header() inside every file in bb-press theme

  • straight forward, you only have to deal with the wordpress theme header from then on
  • many bb-press plugins rely on scripts to be included in the header. With this technique you loose some functionality

You can leave your bb_press header and simply alter it by adding specific wordpress theme tags like wp_list_pages() to create the menu.

  • its the most complicated option since you have to deal with two header files and two style sheets
  • it offers the greatest flexibility. I used it to integrate my support forum

A word on CSS

If you choose to use the third option you will have to manually add the wordpress themes stlye.css to the header.php in the bb_press theme. The included bb_press css file is standalone and complete but you probably want your own theme to take the lead. I recommend throwing out the entire global settings part of the bb_press style sheet. I ended up deleting half of the styles and only leaving the styles that apply to elements of the forum. Everything else should come from your wordpress stylesheet. Its just easier to maintain that way.

And there you go – you run a solid forum that fits nicely into your wordpress blog.