Click here to Skip to main content
15,867,704 members
Articles / Web Development / ASP.NET

Mapping Zip Code From Your Database

Rate me:
Please Sign up or sign in to vote.
4.45/5 (19 votes)
25 Jul 20044 min read 137.6K   1.8K   65   19
Using longitude, latitude, and zip code to map the United States, and then mark the zip codes that are matched in your database

Sample Image - zipcodemapping.gif

Introduction

This application was designed to help me figure out the demographics of my address list. I was curious as to where the various people were located in the continental United States. After a while, I found a couple of interesting articles that pointed me in the right direction, but nothing that was going to save me from writing a solution myself... So, here it is... The Zip Code Mapping Solution (Free). Now, please be patient with this article as it is the first one I have posted...

How It Works

The Database

This application has some pretty simple source code in it... but the main guts and glory come from the database. In the demonstration database, you will find two tables and three queries.

  • Table 1: PUBLISHERS

    (A sample table of data put out by Microsoft, normally this would be your customer base... or whoever you would like mapped.)

  • Table 2: ZIPCODES

    (This table is something I put together through the US Census and through a free Zip Code database that I found online.

  • Query 1: ALLZIPS

    (This query simply filters out all zip codes from the ZIPCODES table that are not in the continental United States.)

  • Query 2: ZIPCODEADDRESS

    (This query also filters out zip codes that are not in the continental US as well as removes null zip codes, non-numeric zip codes [e.g. foreign], and spaces from the "PUBLISHERS" table.)

  • Query 3: ZIPLOCATIONS

    (This query counts the number of people at each zip code, gets the latitude and longitude, and lists the zip code by matching through ZIPCODEADDRESS and ZIPCODES.)

The Mathematics

I had to determine a certain amount of mathematical formulae in order to get this to work. Basically, what the application needs to do is scale down the zip codes into a grid, based on the latitude and longitude coordinates being converted into pixels, on an image. Here are the pieces of data that are needed (these are explained in the demo project).

  • +48.987386 is the highest latitude in the continental US
  • +18.005611 is a latitude lower than the southern most latitude (used for buffer)
  • -124.626080 is the west most longitude in the continental US
  • -62.361014 is a longitude more east than the eastern most longitude (used for buffer)

To find out the scale, I simply figured out the difference in latitudes, and divided it by the height of my image... and then figured out the difference in the longitudes, and divided it by the width of my image. For example:

I selected a 701w X 565h pixel image. To calculate the scale, I did the following math:

Latitude:  (48.987386-18.005611)/565h  = .054835 latitude  unit/pixel
Longitude: (124.656080-67.040767)/701w = .088866 longitude unit/pixel

To put this into code, I just created two functions:

VB
Function getLat(byVal y1)
    Return cInt((48.987386 - (cDbl(y1))) / 0.054835)
End Function
VB
Function getLong(byVal x1)
    Return cInt((124.62608 - (cDbls(x1) * -1)) / 0.088866)
End Function

*Note that the longitude was multiplied * -1... that was to make it a positive number for the subtraction routine.

Now with these functions, I can simply send the latitudes and longitudes from the database to those functions in order to get the correct pixel area that I need to map.

The Application

Now that we have determined the canvas size and the scale, we create a canvas:

VB
'declare variables
Dim objBitmap As Bitmap = New Bitmap(701, 565)
Dim objGraphics As Graphics = Graphics.FromImage(objBitmap)

'create the colors and the shapes
Dim objBrush As SolidBrush = _
  New SolidBrush(System.Drawing.Color.LightGray) 'for US Contour
Dim objWhite As SolidBrush = _
  New SolidBrush(System.Drawing.Color.White) 'for background
Dim objNewBrush As SolidBrush
Dim myRect As Rectangle

Here, I have created the Bitmap and Graphics objects to draw with. I also have created some brushes in order to draw some stuff.

The next step I took was to access everything from a database. I have tested this with both Oracle 9i and MS Access... as long as you change where you are getting your data... everything works the same. So, I am going to skip the database connection stuff as you should be able to figure that out.... if not, it is still in the demo program.

The first step I take (maybe there is an easier way) is I draw a white rectangle for the background of my bitmap.

VB
myRect = New Rectange(0,0,701,565)
objGraphics.FillRectangle(objWhite, myRect)

The next step is to select all records from the "ALLZIPS" table and draw a dot at each of the records. (This is actually quite fast on my computer, but results could vary.)

VB
rs.Open("SELECT * FROM ALLZIPS", DB, 3, 3)

'LOOP THROUGH
WHILE NOT RS.EOF
    myRect = New Rectangle(getLong(rs("LONGITUDE").value)+50, _
                            getLat(rs("LATITUDE").value)+50,2,2)
    objGraphics.FillEllipse(objBrush, myRect)
    rs.MoveNext()
Wend

As you can notice, it is creating an Ellipse (2x2), colored LightGray, at the coordinates specified by "getLong({DB-LONGITUDE})" and "getLat({DB-LATITUDE})", and I add 50 pixels for my surrounding border on the image.

Now, I do it again, but I do it with the records that I want to mark with a different color.

VB
rs.close 'close previous recordset
rs.open("SELECT * FROM ZIPLOCATIONS"), DB, 3, 3)
VB
'LOOP THROUGH
WHILE NOT RS.EOF
    myRect = New Rectangle(getLong(rs("LONGITUDE").value)+50, _
                            getLat(rs("LATITUDE").value)+50,4,4)
VB
'now determine the color based on number of matches
Select Case (rs("ZIPCOUNT").Value)
VB
Case 1
    objNewBrush = New SolidBrush(System.Drawing.Color.RoyalBlue)
Case 2
    objNewBrush = New SolidBrush(System.Drawing.Color.MediumSeaGreen)
Case 3
    objNewBrush = New SolidBrush(System.Drawing.Color.LawnGreen)
Case 4
    objNewBrush = New SolidBrush(System.Drawing.Color.OliveDrab)
Case 5
    objNewBrush = New SolidBrush(System.Drawing.Color.DarkKhaki)
Case 6
    objNewBrush = New SolidBrush(System.Drawing.Color.Goldenrod)
Case 7
    objNewBrush = New SolidBrush(System.Drawing.Color.DarkOrange)
Case 8
    objNewBrush = New SolidBrush(System.Drawing.Color.Coral)
Case 9
    objNewBrush = New SolidBrush(System.Drawing.Color.Red)
Case 10
    objNewBrush = New SolidBrush(System.Drawing.Color.Firebrick)
Case Else
    objNewBrush = New SolidBrush(System.Drawing.Color.DarkRed)
End Select
VB
'fill the ellipse with the selected color
objGraphics.FillEllipse(objNewBrush, myRect)
VB
    rs.MoveNext()
Wend

At this point, the image is drawn and stored in the variable... so we just need to finish up the export of the image and set it to the Image object.

VB
'finish up
objBitmap.Save(Server.MapPath(Request.ApplicationPath) _
        & "\newGlobe.gif", Imaging.ImageFormat.Gif)
objGraphics.Dispose()
objBitmap.Dispose()
rs.Close()

'set source
imageButton.ImageUrl = "newGlobe.gif"

And that is all she wrote... that will create everything that you need.

Additional Comments

There is another procedure in the demo program that helps you "Zoom in", that is based off an article by Harish Palaniappan (also found on CodeProject). I hope that this article has helped you out and I really would like to hear about anybody who uses this application for their web sites. Just leave a comment below.

License

This article has no explicit license attached to it, but may contain usage terms in the article text or the download files themselves. If in doubt, please contact the author via the discussion board below.

A list of licenses authors might use can be found here.


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralIndividual states Pin
Fresh Mexican Food Fan3-Mar-05 7:25
Fresh Mexican Food Fan3-Mar-05 7:25 
GeneralLegal Notices Pin
Anonymous5-Aug-04 11:34
Anonymous5-Aug-04 11:34 
GeneralA tip about storing numeric values with leading zeros. Pin
Ashaman30-Jul-04 2:37
Ashaman30-Jul-04 2:37 
GeneralRe: A tip about storing numeric values with leading zeros. Pin
Chris Keeble4-Aug-04 0:45
Chris Keeble4-Aug-04 0:45 
GeneralRe: A tip about storing numeric values with leading zeros. Pin
Ashaman4-Aug-04 1:53
Ashaman4-Aug-04 1:53 
GeneralError Pin
Anonymous28-Jul-04 13:40
Anonymous28-Jul-04 13:40 
GeneralRe: Error Pin
Adam A. Mitteer29-Jul-04 3:56
Adam A. Mitteer29-Jul-04 3:56 
GeneralRe: Error Pin
Chris Keeble4-Aug-04 0:03
Chris Keeble4-Aug-04 0:03 
Generalneed help with error Pin
mkane727-Jul-04 18:08
mkane727-Jul-04 18:08 
GeneralRe: need help with error Pin
Adam A. Mitteer28-Jul-04 4:18
Adam A. Mitteer28-Jul-04 4:18 
GeneralRe: need help with error Pin
mkane728-Jul-04 17:14
mkane728-Jul-04 17:14 
GeneralRe: need help with error Pin
mkane728-Jul-04 18:33
mkane728-Jul-04 18:33 
GeneralRe: need help with error Pin
Adam A. Mitteer29-Jul-04 3:44
Adam A. Mitteer29-Jul-04 3:44 
GeneralRe: need help with error Pin
Chris Keeble3-Aug-04 23:30
Chris Keeble3-Aug-04 23:30 
GeneralRe: need help with error Pin
Anonymous9-Aug-04 9:32
Anonymous9-Aug-04 9:32 
GeneralRe: need help with error Pin
Adam A. Mitteer9-Aug-04 10:27
Adam A. Mitteer9-Aug-04 10:27 
GeneralRe: need help with error Pin
Cat Doan1-Apr-09 18:07
Cat Doan1-Apr-09 18:07 
GeneralRe: need help with error Pin
Chris Keeble10-Aug-04 2:50
Chris Keeble10-Aug-04 2:50 
GeneralRe: need help with error Pin
Anonymous10-Aug-04 3:50
Anonymous10-Aug-04 3:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.