Mapping Zip Code From Your Database.

• Download demo project - 1144 Kb

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:

Function getLat(byVal y1)
    Return cInt((48.987386 - (cDbl(y1))) / 0.054835)
End Function

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:

    '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) but I draw a white rectangle for the background of my bitmap.

    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).

    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.

    rs.close 'close previous recordset
    rs.open("SELECT * FROM ZIPLOCATIONS"), DB, 3, 3)

    'LOOP THROUGH
    WHILE NOT RS.EOF
        myRect = New Rectangle(getLong(rs("LONGITUDE").value)+50, _
                                getLat(rs("LATITUDE").value)+50,4,4)

        'now determine the color based on number of matches
        Select Case (rs("ZIPCOUNT").Value)

        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

        'fill the elipse with the selected color
        objGraphics.FillEllipse(objNewBrush, myRect)

        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.

    '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 TheCodeProject). 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 drop me a line: aamittee{at}mtu.edu.

Regards,

Adam A. Mitteer