Displaying IP addresses on the map

This is a weekend project, not much practical use but it was fun to create. Hopefully you find techniques mentioned here useful.

Some our employees need to connect to our FTP server while travelling. As an extra protection measure we maintain a whitelist of IP addresses where connection is allowed from. We thought it would be nice to convert IP addresses to geographical coordinates and display them on a map. It worth saying that IP to latitude/longitude conversion is not 100% accurate and IP addresses get re-assinged all the time. However for our task it is not really important.

And check this live demo.

There are several IP2location services available on the Internet, both free and paid. we are going to use a free database provided by ip2location.com. After a free registration you can download their database as CSV file. In this example we are going to use MySQL database. This database contains about 3 million records that allows to map IP address ranges to lat/lng pairs.

1. Create ip2location_db5 table and import data.

Create table

CREATE DATABASE ip2location;
USE ip2location;
CREATE TABLE `ip2location_db5`(
	`ip_from` INT(10) UNSIGNED,
	`ip_to` INT(10) UNSIGNED,
	`country_code` CHAR(2),
	`country_name` VARCHAR(64),
	`region_name` VARCHAR(128),
	`city_name` VARCHAR(128),
	`latitude` DOUBLE,
	`longitude` DOUBLE,
	INDEX `idx_ip_from` (`ip_from`),
	INDEX `idx_ip_to` (`ip_to`),
	INDEX `idx_ip_from_to` (`ip_from`, `ip_to`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Import database (adjust path to CSV file)

LOAD DATA LOCAL
	INFILE 'IP2LOCATION-LITE-DB5.CSV'
INTO TABLE
	`ip2location_db5`
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 0 LINES;

2. Create table locations and import data

Skip this step if you already have your IP addresses somewhere in the database. In our scenario IP addresses are stored in a text file named ip.txt, one IP address per line.

Create table

CREATE TABLE IF NOT EXISTS `locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lat` decimal(10,2) DEFAULT NULL,
  `lng` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Import data

LOAD DATA LOCAL
	INFILE 'c:\\tmp\\ip.txt'
INTO TABLE
	`locations` 
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 0 LINES
(ip)
;

3. Populate lat/lng fields in locations table.

This query may need a few minutes to run, the database is pretty big.

UPDATE locations l, ip2location_db5 d
SET l.lat = d.latitude , l.lng = d.longitude
WHERE INET_ATON(l.ip) between d.ip_from and d.ip_to

4. Create a new project in PHPRunner or ASPRunner.NET.

Add locations table. On ‘Choose Pages’ screen uncheck all pages but List.

In Page Designer insert a map on the top of the grid. Choose “lat” and “lng” as latitude/longitude fields. Choose “ip” as “addressField”.
Remove visual elements you don’t need like breadcrumbs or search. Do not forget t specify your Google Maps API key.

$mapSettings["addressField"] = "ip";

// name of field in table that used as latitude for map
$mapSettings["latField"] = "lat";

// name of field in table that used as longitude for map
$mapSettings["lngField"] = "lng";

And using our famous PHP to C# converter we get similar code for C#:

dynamic mapSettings = XVar.Array();
mapSettings.InitAndSetArrayItem("ip", "addressField");
mapSettings.InitAndSetArrayItem("lat", "latField");
mapSettings.InitAndSetArrayItem("lng", "lngField");

5. Next steps

While this is a simple project that only took two hours to implement it shows some ideas that you can use in real-life projects.

What is more interesting – what kind of mapping projects would you be interested to see? I was thinking to create a project where you can upload a bunch of pictures, extract geotag info, and shows those pictures on the map, attaching it to locations where they were taken.

If you have similar ideas feel free to share in comments.