How-To: Plot Server Log File IP Addresses Onto A Map

How To: Plot IP Addresses From Server Log Files Onto A Map

These instructions may be used as a guide for other mapping / plotting systems, however these instructions assume that you will be using
the US governments free online mapping tool at http://tiger.census.gov/instruct.html specifically. The TIGER Mapping Service currently only
maps locations that are part of the United States and is strongly recommended that you familiarize yourself with it before proceeding.

Follow the steps below to map the locations of your server log file IP Addresses that are in the US using the US governments' TIGER Mapping Service.
Please take note when copying code. Some of the code in the instructions following is too long to reasonably display on one line. Code separated by a "à" in this document continues onto the next line.

Two lists will need to be created, a Marker List and a URL List:

1. - The "Marker List" containing up to approximately 3,000 markers that you want to appear on the generated map.
The Marker List will need to be saved to a file on your web server that is accessible to the TIGER Mapping Service.
In the examples that follow, the file name "http://www.yourdomain.com/plot.txt" will be used.
The TIGER Mapping Service markers are in the format of lon,lat:symbol:label.For more see http://tiger.census.gov/instruct.html. Use GeoLyzer and Template 1* to extract the Lon, Lat and City Name from your raw log files.
List 1 is a typical example of what a complete "Marker List" can look like.

-118.284,34.045:redstar:Los Angeles
-122.426,37.771:redstar:San Francisco
-117.148,32.747:redstar:San Diego
-95.393,29.772:redstar:Houston
-93.307,44.966:redpin:Minneapolis
-96.802,32.796:redpin:Dallas
-121.874,37.317:redpin:San Jose
-117.606,33.634:redpin:Rancho Santa Margarita
-73.768,42.661:reddot10:Albany

List 1: "Marker List" / "Plot.txt"

<geobytes longitude>,<geobytes latitude>:redstar:<geobytes city>

GeoLyzer Template 1: Hardcoded marker styles

GeoLyzer Template 2: Template for insertion into database for dynamic marker styles

*Optionally you may wish to use Template 2, import the data from GeoLyzer into SQL Server and perform a "group by" (as shown in Query 1),
which can be used to nominate the style of marker (i.e. ":redstar:", ":redpin:", ":reddot10:" etc.) based upon the frequency of city occurrences
that appear in your log files. Such a "group by" query may look like Query 1.

select LonLat+
case
when Quantity<150 then ':blueball:' --':blue3:'
when Quantity>=150 and Quantity<700 then ':redpin:' --':red4:'
+City
else ':redstar:' +City
end
from
(
select count(subnets.SubnetAddress) as Quantity,
cast(cast(Longitude as numeric(7,3)) as varchar(8))+','+
cast(cast(Latitude as numeric(7,3)) as varchar(8)) as LonLat,
City,
Longitude
from tmpGeoLyzerResults
group by Longitude,Latitude,City
) as GeoLyzerResults
order by Quantity desc

Query 1: Dynamically selecting marker style

2.- The "URL List" is a list of URLs which will be used to describe the geographic area that you wish the points to be plotted onto, such as the
map center latitude and longitude and the height and width of the map.
The TIGER Mapping Service murl parameter is used to tell the Tiger service where your file containing the Marker List resides.

For further detail as to what parameters the TIGER Mapping Service takes, please see http://tiger.census.gov/instruct.html

A URL List may look like:

<a href="http://tiger.census.gov/cgi-bin/mapgen?map.x=211&map.y=180&lon=-73.769&lat=42.661&wid=1&ht=1&iht=718&iwd=844à
&murl=http://www.yourdomain.com/plot.txt">Albany</a>
<a href="http://tiger.census.gov/cgi-bin/mapgen?map.x=211&map.y=180&lon=-75.505&lat=40.593&wid=1&ht=1&iht=718&iwd=844à
&murl=http://www.yourdomain.com/plot.txt">Allentown</a>
<a href="http://tiger.census.gov/cgi-bin/mapgen?map.x=211&map.y=180&lon=-83.747&lat=42.276&wid=1&ht=1&iht=718&iwd=844à
&murl=http://www.yourdomain.com/plot.txt">Ann Arbor</a>
<a href="http://tiger.census.gov/cgi-bin/mapgen?map.x=211&map.y=180&lon=-84.399&lat=33.756&wid=1&ht=1&iht=718&iwd=844à
&murl=http://www.yourdomain.com/plot.txt">Atlanta</a>

List 2: A typical "URL List"

To produce the list of links re-run GeoLyzer again using Template 3 with the plot.txt file:

<a href="http://tiger.census.gov/cgi-bin/mapgen?map.x=211&map.y=180&lon=<geobytes longitude>&lat=<geobytes latitude>&widà
=1&ht=1&iht=718&iwd=844&murl=http://www.yourdomain.com/plot.txt"><geobytes city></a>

GeoLyzer Template 3: URL list template

Optionally, import the GeoLyzer "link" results into an SQL database table and perform a "group by" to remove duplicate links and sort the links.
A typical "group by" may look like Query 2.

select URLs from tmpGeoLyzerResults
group by URLs
order by URLs

Query 2: Grouping and sorting the URL List

Some of the uses for the URLs that have been successfully generated can be:

As a hyperlink to the generated map. For example - Albany. The code to generate a similar hyperlink looks like:

<a href="http://tiger.census.gov/cgi-bin/mapgen?map.x=211&map.y=180&lon=-73.769&lat=42.661&wid=1&ht=1&iht=718&iwdà
=844&murl=http://www.yourdomain.com/plot.txt">Albany</a>

List 3: Hyperlinked text to generated map

As an image on a page, for example - Boston, Massachusetts, United States:

Image 1: Image of Boston, Massachusetts, United States

The code to generate a similar image to that of Image 1 looks like:

To view all of your points on the 48 contiguous United States on the one map:

Image 2: The 48 contiguous United States with markers

The code to generate a similar image to that of Image 2 looks like:

For more examples of mapping IP Addresses onto maps using GeoSelect see:
Map Accuracy Evaluation Test Results
PAXTV City Targeted Campaign