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.


Feb relaunched

It’s finally done.

I created a new theme for my site.
I set up a dedicated portfolio section for all the websites I make.
I set up a bb-press forum to support my adobe air apps. No more lost comments.

Here are a bunch of screenshots I took during the design process.



App for the milk – 2010 development update

Dear Aftm Users

I have not gotten any new updates out in the last 2 months, which I am sorry for but my live has been terribly busy. Finishing university, other jobs, new apartment, broken macbook etc… I have not quit working on aftm, so do not delete it just yet. I am currenty reworking my entire website, which will finally include a decent support forum. Once this is complete I will resume working on aftm and finally push out the long promised smart list feature. So I wish everybody a happy new year and bear with me for another month so I can get everything ready.




App for the milk – version 0352 bugfix


Version 0350 crashes upon launch on windows systems this is now fixed.

download: version 0352

Debugging log file
Added a setting that creates a debugging logfile on the desktop to be sent back to me if the app does not behave as it should. It only contains app relevant info and none of your personal information.

note: to enable this setting the app has to be restarted.



App for the milk – version 0350 released

Verion 0350 is another milestone release. It implements many new features as well as an again improved UI and some bugfixes.

download: version 0350

UI improvements

I made some more improvements to the UI, the scrollbars and the dropdown menus are new. I really like what Apple did with the new Itunes 9 UI so I tried to copy it as good as I could, and I am quite happy with it. But as always I am open for suggestions. The icons should also look a bit better now.

Detail view

I finally got the detail view to a level where I could release it to users. In oder to see the detailview for a task just double click the task. This used to let you edit the name of the task but I found that its a rarely used feature. Once in the detail view you can edit the tasks name by double clicking it again. In the detail view are two tabs, one for general info and tags and one for notes. The info currently includes only estimate, url, and due date since repeating tasks is the hardest of all things to implement and not yet supported. Once I figured that out it will be added.


Tags are now fully supported. In the detail view info tab there is a tag module that works online and offline. To add tags just put them in and hit the tag button. In order to remove a tag just click the little blue cross icon next to it. Pretty straight forward.


Notes are not complete yet. It is possible to edit notes in the detail view online and offline. In order to do so just double click the title or the text of a note. It is not yet possible to add or delete notes but its the next thing on my to do list. I just wanted to get this update out so people could provide first feedback and since the last update is already a few weeks back.

Future Outlook

So the next thing will be to finish notes editing. Then I want to take on smart list support. The search / filter system is still not quite ready for that though. Once smart lists are supported I finally want to go beta with the app. The last major feature that is still missing then is repeating tasks, which I am not quite sure yet how to implement but it should be possible.

I recently had the idea that the searchfield could be used for quickly adding tasks like, “add:some task” and other commands. Kind of like rtms smart add feature

Another idea that has been floating around in my head is a kind of planing mode. Currently a task has a due date and an estimate but the app and rtm do not give me any means of organizing when I want to do which task. I could of course create and event in my ical for that but that would not be very user friendly. I envision a ical like view of a given day where you can arrange the tasks that you want to accomplish and therefor get a plan when to do what. This would be very easy to do if rtm implemented a task attributed like “scheduled” . The time when to do it + plus an estimate give me all the info I need to plan the day. As I have doubts that rtm will be able to implement this very quickly will create a slimmed down version of it in the app and this may then serve as a proof of concept to rtm. What do you think about this idea?



App for the milk – version 0257 – bugfix

This version fixes a bug where the app reset due times on each sync.



App for the milk – version 0256 released

This version introduces yet another icon. Rtm asked me to change the current icon to something else since the direct usage of their logo is not permitted by their Remember The Milk API Terms of Use. I want to point out that they asked really nicely and gave me plenty of time to make the changes, which is not always the case when companies see their rights violated. I therefore gladly comply with their request.

the icon again, again

I am kind of back to the blue square for now but it has it least gotten some cow textures. That was the only thing I could come up with right now. I will work on it.



Red & Blue

little actionscript experiment I did during a break.

download the code: Red-Blue