Article

# Mapping Zip Code From Your Database.

, 25 Jul 2004
 Rate this:
Using longitude, latitude, and zip code to map the United States, and then mark the zip codes that are matched in your database.

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

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

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,

A list of licenses authors might use can be found here

## You may also be interested in...

United States
No Biography provided

 View All Threads First Prev Next
 Error Anonymous 28-Jul-04 13:40
 Re: Error Adam A. Mitteer 29-Jul-04 3:56
 Re: Error kcl 4-Aug-04 0:03
 Last Visit: 31-Dec-99 18:00     Last Update: 28-Aug-14 15:58 Refresh 1