StevensNet.com

...Because someone's gotta tell the story...

Blogs

To return to the main Blog List, click Full Blog Listing.

Tags

SMB
SQL
Technical
VB.NET

How the GeoLocator works for gas prices

Tuesday, March 11, 2008 in Technical Articles (Views: 2081)
Here is the technical 411 on how the geolocator works. First, allow me to say in not so technical words some of the limitations.

How the tool basically works is it takes the IP Address of the computer you are logging in from, converts it to a geocode, then matches it to a database. Unfortunately, sometimes that IP Address could match up to a router that could be a mile from you, sometimes 50.

In one test case, I logged in from a computer in Northern VA, and received a geocode from Maryland. So, we will see how this works out in further testing, and I would appreciate your feedback as to how it is working for you.

Here is the technical information as to how it exactly works. The code snippets are in ASP.NET, written in VB.NET.

First, we have to get the incoming IP address from the client itself. This is a relatively easy process, which is simply to load this value into a variable:

My.Request.UserHostAddress.ToString

But, I am not looking for the IP address by itself, when I can convert the geocode and grab the IP address in a single line by calling a function:

dim strGeode as String = CStr(ConvertGeoLocation(My.Request.UserHostAddress.ToString))

The function ConvertGeoLocation takes this IP address, splits it by octets, and then reforms it into a geocode, searchable by the database.

Function ConvertGeoLocation(ByVal strIP As String)

Dim arrDec() As String

Dim i As Integer

Dim intResult As Long

If strIP = "" Then

ConvertGeoLocation = 0

Else

arrDec = strIP.Split(".")

For i = arrDec.Length - 1 To 0 Step -1

intResult = intResult + ((Int(arrDec(i)) Mod 256) * Math.Pow(256, 3 - i))

Next

ConvertGeoLocation = intResult

End If

End Function


In my case, I actually take that geocode and pass it into a stored procedure. I have a single stored procedure that processes the input from the gas price page and returns information based on parameters the user provides. So, in the case of clicking "Near Me", here is the relevant SQL code...

The @geocode parameter is defined further up as a required stored procedure parameter.

For the sake of security, field and table names have been changed.

declare @tmpZIP varchar(10)

declare @city varchar(40)

declare @state varchar(2)

SELECT @tmpZIP=zipCode

FROM GeoCodeTable

WHERE @geocode BETWEEN GeoCodeFrom AND GeoCodeTo

and len(@geocode)=len(GeoCodeTo)

order by ZipCode desc

select @city=city, @state=state from ZipCodeTable where zipcode=@tmpZIP


I then take this information, knowing the city, state, and zip code, and process gas prices relative to this information. As of now, it is simply matching only the city as the geolocator. I will probably convert that to county later on as the geolocator, however, in some places like Los Angeles County, that will really produce a lot of results. Not that I am trying to get all gas prices within a 3 block radius of where you're sitting (although that would be cool), it is meant to compare prices generally in the area you are in...

Again, your feedback is appreciated on the tool...

 

Related Blogs You May Be Interested In:


To leave a comment, please log in and/or register.