Find location from IP address

Sometimes a webmaster want to know where the visitors coming from, sometimes it is also nice to have a embedded map on the website to indicate where the visitors location is. I am going to show you how to make it happen in this article with MS SQL server and asp.net implementation.

We have 2 questions right now,

1.       How to get the physical location of the visitor from the IP address?

2.       How to display the location on a map (google map perhaps)?

To solve the first question, I found a website providing free IP location database (since it is free, it is not as accurate as commercial version and require regular manual updates), check here for detail.

I downloaded 2 CSV file containing ip locations.

GeoLiteCity-location.csv

GeoLiteCity-blocks.csv

If IP address is w.x.y.z then corresponding ipnum is ipnum = 16777216*w + 65536*x + 256*y + z.

To retrieve the location from the ip address, we simply need to follow logic shown below.

Ip address => ipnum => locId => (City, country, latitude, longtitude)

To solve the second problem, I looked into google map javascript API and found it is able to take latitude and

 longitude as parameter. An example is shown below,

Google map at (Latitude, Longitude)=(30.2553, 120.1689) and scale level 13

So, we have solved our problems, the rest is simply to pick a programming technique and begin to make it real.

I choose .Net in this case. I first created 2 tables in MS sql server 2008 express database and then imported the two

 csv files into the database.

Run following T-SQL code to generate two tables and indexes for startIpNum and endIpNum fields in sql server

 management studio.

use geolite

go

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE Table dbo.geo_locations

(

--id int NOT NULL IDENTITY(1,1) PRIMARY KEY,

location_id int  NOT NULL,

country varchar(2)  NOT NULL,

region varchar(2)  NOT NULL,

city varchar(60)  NOT NULL,

postalCode varchar(10)  NOT NULL,

latitude varchar(10)  NOT NULL,

longitude varchar(10)  NOT NULL,

metroCode varchar(10)  NOT NULL,

areaCode varchar(10)  NOT NULL

)

go

CREATE Table dbo.geo_blocks

(

--id int NOT NULL IDENTITY(1,1) PRIMARY KEY,

startIpNum bigint  NOT NULL,

endIpNum bigint  NOT NULL,

location_id int  NOT NULL

)

go

Create UNIQUE INDEX geo_startIpNum ON dbo.geo_blocks(startIpNum);

go

Create UNIQUE INDEX geo_endIpNum ON dbo.geo_blocks(endIpNum);

go

Please check this post to see how to install and configure ms sql server and sql server management studio.

http://shensitworld.blogspot.com/2011/06/make-nhibernate-work-with-sql-server.html

I used T-SQL Bulk Insert commands to import the 2 csv files.

use geolite

go

BULK INSERT dbo.geo_locations

FROM 'C:\Documents and Settings\Ejer\Skrivebord\GeoLiteCity_20110601\GeoLiteCity_20110601\GeoLiteCity-Location.csv'

WITH

( 

      FIRSTROW = 1,

      --FIELDTERMINATOR = ',',

      ROWTERMINATOR = '\n',

      FORMATFILE = 'C:\Temp\geo_locations_without_id.fmt'

)

go

BULK INSERT dbo.geo_blocks

FROM 'C:\Documents and Settings\Ejer\Skrivebord\GeoLiteCity_20110601\GeoLiteCity_20110601\GeoLiteCity-Block.csv'

WITH

( 

      FIRSTROW = 1,

      --FIELDTERMINATOR = ',',

      ROWTERMINATOR = '\n',

      FORMATFILE = 'C:\Temp\geo_blocks_without_id.fmt'

)

2 things to notice here,

First remove the first 2 lines in csv files and make sure there is an empty line at the end of each csv file.

Secondly, generate format files for each database tables by running bcp command,

C:\Temp>bcp geolite.dbo.geo_locations format nul -c -t, -f geo_locations_without

_id.fmt -S localhost\SQLEXPRESS -U geoliteuser -P geolitepw –T

C:\Temp>bcp geolite.dbo.geo_blocks format nul -c -t, -f geo_blocks_without

_id.fmt -S localhost\SQLEXPRESS -U geoliteuser -P geolitepw –T

The format files generated need to be modified on the separator column because csv files contains columns with

 double quotes, the modified versions are shown below.

10.0

9

1       SQLCHAR             0       12      ",\""      1     location_id                ""

2       SQLCHAR             0       2       "\",\""      2     country                    Danish_Norwegian_CI_AS

3       SQLCHAR             0       2       "\",\""      3     region                     Danish_Norwegian_CI_AS

4       SQLCHAR             0       60      "\",\""      4     city                       Danish_Norwegian_CI_AS

5       SQLCHAR             0       10      "\","      5     postalCode                 Danish_Norwegian_CI_AS

6       SQLCHAR             0       10      ","      6     latitude                   Danish_Norwegian_CI_AS

7       SQLCHAR             0       10      ","      7     longitude                  Danish_Norwegian_CI_AS

8       SQLCHAR             0       10      ","      8     metroCode                  Danish_Norwegian_CI_AS

9       SQLCHAR             0       10      "\n"   9     areaCode                   Danish_Norwegian_CI_AS

geo_locations_without_id.fmt

10.0

4

1       SQLCHAR             0       12      "\""      1     xFactor                 ""

2       SQLCHAR             0       21      "\",\""      2     startIpNum                 ""

3       SQLCHAR             0       21      "\",\""      3     endIpNum                   ""

4       SQLCHAR             0       12      "\"\n"   4     location_id                ""

geo_blocks_without_id.fmt

(PS:make sure there is an empty line at the end of each fmt file.)

Next thing I did is to create an asp.net project in c# express. Retrieving visitor’s ip address is achieved by following

 code,

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Configuration;

using System.Data.SqlClient;

using System.Net;

namespace iplocation

{

    public partial class _Default : System.Web.UI.Page

    {

        protected bool isMyIP = true;

        protected String latitude = "";

        protected String longitude = "";

        protected void Page_Load(object sender, EventArgs e)

        {

            String geoliteConnectionString = ConfigurationManager.ConnectionStrings["geoliteConnectionString"].ToString();

            SqlConnection geoliteconnection = new SqlConnection(geoliteConnectionString);

            try

            {

                geoliteconnection.Open();

            }

            catch (Exception ex0)

            {

                throw ex0;

            }

            String ip = null;

            if (!isMyIP)

            {

                ip = getIpForUrl(TextBox1.Text);

            }

            else

            {

                ip = getVisitorIp();

            }

            Label1.Text = "Your IP address is " + ip;

            String visitorIpNum = fromIpToIpNum(ip);

            string selectCommand = "SELECT geo_locations.country, geo_locations.city, geo_locations.latitude, geo_locations.longitude FROM geo_locations INNER JOIN geo_blocks ON geo_locations.location_id = geo_blocks.location_id WHERE (geo_blocks.startIpNum < " + visitorIpNum + ") AND (geo_blocks.endIpNum > " + visitorIpNum + ")";

            SqlCommand sqlcmd = new SqlCommand(selectCommand, geoliteconnection);

            SqlDataReader reader = null;

            try

            {

                reader = sqlcmd.ExecuteReader();

                while (reader.Read())

                {

                    String country = (String)reader["country"];

                    String city = (String)reader["city"];

                    latitude = (String)reader["latitude"];

                    longitude = (String)reader["longitude"];

                    Label2.Text = "(country=" + country + ",city=" + city + ",latitude=" + latitude + ",longitude=" + longitude + ")";

                }

                reader.Close();

                geoliteconnection.Close();

            }

            catch (Exception ex)

            {

                try

                {

                    if (reader != null) reader.Close();

                }

                catch (Exception ex1)

                {

                    reader = null;

                }

                try

                {

                    if (geoliteconnection != null) geoliteconnection.Close();

                }

                catch (Exception ex2)

                {

                    geoliteconnection = null;

                }

            }

            isMyIP = true;

        }

        private String fromIpToIpNum(String ip)

        {

            string[] ipParts = ip.Split('.');

            long ipnum = Convert.ToInt64(ipParts[0]) * 16777216 + Convert.ToInt64(ipParts[1]) * 65536 + Convert.ToInt64(ipParts[2]) * 256 + Convert.ToInt64(ipParts[3]);

            return Convert.ToString(ipnum);

        }

        private String getVisitorIp()

        {

            String ip = Request.ServerVariables["HTTP_X_FORWARDED_FOR"];

            if (!string.IsNullOrEmpty(ip))

            {

                string[] ipRange = ip.Split(',');

                int le = ipRange.Length - 1;

                ip = ipRange[le];

            }

            else

            {

                ip = Request.ServerVariables["REMOTE_ADDR"];

            }

            if (ip.Equals("127.0.0.1"))

            {

                ip = "61.172.201.194"; // a default randomly picked ip for local machine test, to get real local ip, you can fx. make a request to a website showing your ip address and extract the ip address from html response            }

            return ip;

        }

       

        private String getIpForUrl(String url)

        {

            IPAddress[] addresslist = Dns.GetHostAddresses(url);

            String ip = "";

            foreach (IPAddress theaddress in addresslist)

            {

                ip = theaddress.ToString();

            }

            return ip;

        }

        protected void Button1_Click(object sender, EventArgs e)

        {

            isMyIP = false;

            Page_Load(sender, e);

        }

        protected void Button2_Click(object sender, EventArgs e)

        {

            isMyIP = true;

            Page_Load(sender, e);

        }

    }

}

The sql query is not running fast, but from function point of view, it suffices. You can optimize it for  your own

needs.

Define sqlconnectionstring in web.config file

<connectionStrings>

        <add name="geoliteConnectionString" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=geolite;Persist Security Info=True;User ID=geoliteuser;Password=geolitepw;Timeout=60"

            providerName="System.Data.SqlClient" />

    </connectionStrings>

In the end, I made an asp.net page to display google map pointing to visitor’s location, the javascript segment is

 shown below.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="iplocation._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

    <title>Show me the Location.</title>

    <script src="http://maps.google.com/maps?file=api&v=2&key=abcdefg"

      type="text/javascript"></script>

      <script type="text/javascript">

          //<![CDATA[

          //var WINDOW_HTML = '<div style="width: 210px; padding-right: 10px"><a href="http://www.hostrocket.com">HostRocket</a> servers are located at 21 Corporate Drive, Clifton Park, NY 12065. Phone: 1-866-519-7079</div>';

          function load(latitude, longitude, scale) {

              if (GBrowserIsCompatible()) {

                  var map = new GMap2(document.getElementById("map"));

                  map.addControl(new GSmallMapControl());

                  map.addControl(new GMapTypeControl());

                  map.setCenter(new GLatLng(latitude, longitude), scale);

                  var marker = new GMarker(new GLatLng(latitude, longitude));

                  map.addOverlay(marker);

                  //GEvent.addListener(marker, "click", function() {

                  //marker.openInfoWindowHtml(WINDOW_HTML);

                  //  });

                  //marker.openInfoWindowHtml(WINDOW_HTML);     

              }

          }

          //]]>

    </script>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <asp:Label ID="Label3" runat="server" Text="web site:"></asp:Label>

        <asp:TextBox ID="TextBox1"

            runat="server" Width="270px"></asp:TextBox><asp:Button ID="Button1"

            runat="server" Text="get website ip address" onclick="Button1_Click" /><asp:Button

                ID="Button2" runat="server" Text="get my ip address"

            onclick="Button2_Click" />

    </div>

    <div>

   

    <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>

    </div>

    <div>

   

        <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label>

   

    </div>

    <div id="map" style="width: 500px; height: 300px">

    <script type="text/javascript">

        load(<%= latitude %>, <%= longitude %>, 13);

</script>

    </div>

    </form>

</body>

</html>

Let’s see a screenshot for some fun.