Zip Code Database

I’ve updated my ZipCodes database to work on Panorama X and updated the data itself to nearly 42,000 zip codes.

The database demonstrates three ways of working with zip codes and miles.

Distance Between Zips uses Find to locate a first zip code and get its coordinates. Then it Finds the second and calculates the distance bewteen them in miles.

Select a Radius uses Lookup equations to get the coordinates for a zip code, then selects all zip codes within a specified radius of the first.

Calculate Mileage also uses Lookup equations to acquire the coordinates of a zip code, then fills the Mileage column with the distance of every zip code from the first.

There are many more potential applications and methods possible. These are simply references for helping to get ideas to build from.

And thanks to David Thompson for the formula that is key to all of it working.

Download ZipCodes

2 Likes

Thanks Jim (and Dave) :slight_smile:

However, I think this represents a marketing failure on my part – good thing I am not actively marketing Panorama X yet. It turns out, Panorama X already includes this functionality built in.

ZipDistance(

ZipLatitude(

ZipLongitude(

Also, if a field is named Zip, Panorama automatically will include zip code distance selections in the context menu when you right click on a cell. In fact, these work right out of the box in the database that Jim Cook has provided.

This feature is demonstrated in the Introduction to Panorama X video on the ProVUE web site.

http://www.provue.com/panoramax

Sorry I didn’t promote this better, I guess I could have saved you some work.

From whatever source, thank you very much for this. It fits perfectly into a project I want to do, but there is some additional voodoo that I need to accomplish to make my project work, so since we’re on the subject I’ll lay it out briefly here to see if anyone has ideas. I can open a new thread for this if it takes off.

We often have people call to ask if there is anyone in their area who uses the equipment we sell. We’ve been using a pretty cumbersome procedure to answer this, basing “nearness” on having the first 3 digits of the ZIP be the same. In real life, this is a bad approximation, and these ZIP functions give much better info. When I heard that PanX could open web pages I had a better idea. We’ve all seen Google maps with pins all over it denoting restaurants or some other kind of business the user is interested in, with more details about any of the points available with a click. I would like to be able to populate a map with the info from a search based on ZIP proximity of our customers to the caller. I have no idea how to inspire Google maps to do this, but it must be well documented somewhere since the method is so prevalent. Can anyone point me in the right direction?

I think you might want to start here:

This should give you a foothold into the basic technology that you would have to use to create specialized Google maps. Good luck!

Google Maps Platform  |  Google Developers https://developers.google.com/maps/

Google Maps Platform - Location and Mapping Solutions https://enterprise.google.com/intl/en_uk/maps/?utm_source=cpc&utm_medium=google&utm_campaign=2016-geo-emea-endor-gmedia-search-gb-homepage&utm_content=gb|en|hybr|1001878||bk|brand||homepage&ds_lpt_start=&ds_lpt_end=&gclid=CjwKEAiAz4XFBRCW87vj6-28uFMSJAAHeGZb57GXRtYC_gfvc1jie8s7kE5slBI7yR8GjOx4ib-35RoCKhrw_wcB

Robert Ameeti

Scott, if you figure out how to do this, I hope you’ll share it with us :slight_smile:

Darn! I was hoping you were going to chime in with the definitive approach! Thank you Gary and Robert for the references. I will read them and see how feasible this is. When they mention APIs, it makes me realize this is going to be quite a bit more complex than constructing a detailed URL. Being at least able to select customers within a certain reliable range is the going to be very useful even without a map display.

Onward! I’ll report back.

Scott, here is the format of a url that you can see as an example…

https://www.google.com/maps/@-37.8039764,144.930675,13z

There are an infinite number of options that you can use but it can be as easy as that.

The 13z at the end is merely the zoom level. So it can be as easy or a complex as you might want. If you want custom markers at particular points, you get to decide the shape, color, cluster size etc. or perhaps custom markers.

Robert Ameeti

Robert, I’m not seeing a URL, only an actual map. It looks like when you put the URL in there, it got interpreted instead of just displaying as text. I’d love to see how you got that.

A couple of more questions. I assume ZipDistance is measured “as the crow flies” rather than road miles? Some ZIP codes can encompass a large area; what is used as the reference point as the “location” of the ZIP code - geographic center? location of the post office itself?

Go to Google Maps http://www.google.com/maps

Note the url. That is a very simplified format that you can use. It will be the ‘@’ along with a lat and long, and a zoom level.

If you right click on an already noted point of interest, you’ll see a ‘What’s here’ in the context menu. Select that. Then your url will change to that spot. The previous urls that Gary and I share are the documentation for the format of the url that you might want. The docs will show you how to add custom markers at points that you desired, selecting the shape, size, and color of the markers, as well as the clustering of markers based on their proximity to each other. Lots and lots of options. Clicking on your markers can bring up custom window overlays with whatever kinds of detail you want to share about your marker. Sales, volume at that location, contact name, hours of operation, etc. All pulled from your database.

Robert Ameeti

Whew! There is a lot there to absorb. I see that limited use of the API is free (I would stay well below the usage level for that). So far, merely being able to compute distance between ZIP codes is a huge benefit, but a map would add icing to that cake. Thank you, I will have more questions…

Geographic center. You can find this kind of stuff out by entering a lat long for a zip that you know. From there you can see how they are placing the pin.

Find the lat long for a zip code that you are interested in (from the Pano db).

Go here: Google Maps

paste the lat long in the Search box.

You’ll get something like this as a result:

https://www.google.com/maps/@34.2134412,-118.6206445,15z

If you are wise about that zip code area, you can see how they are placing that map’s center point, especially if use a zip code of a sparsely populated area. Do recognize that not all zip codes have post offices.

Robert Ameeti

Robert, look at your latest reply on the forum page. I keep thinking it is not showing everything you are typing. Your 5th paragraph reads “You’ll get something like this as a result:” and there is nothing there, we just go to the next paragraph. Or I don’t get it.

With just ZIP codes to go on, ZipDistance will tell me that everyone in that ZIP code is 0 miles from each other. What’s going to help me in a map solution is knowing the Lat and Long of specific addresses, so they can be accurately represented on the map, whether they’re in the same ZIP code or not. I have the addresses in my database, but I don’t know how to get global coordinates.

I need to dig into the documentation before I get hung up asking ignorant questions.

Yes, the forum is reformatting some stuff.

The below code is expecting that you would have fields named…

Address1
City_District
County
ST_Region
Country
Postal_Code
Lat
Lng

Let’s see if this works… Create a db with the above fields. Enter an address in Address1, and a zip code in Postal_Code. Have the below code trigger based on entry of your Postal_Code. Everything else should be auto calculated.

City_District=City(Postal_Code)
County=County(Postal_Code)
ST_Region =State(Postal_Code)

Local LResult, LAddress

LAddress = Address1 + " " + City_District + " " + ST_Region + " " + Postal_Code

LoadURL LResult,“http://maps.googleapis.com/maps/api/geocode/xml?address=“+replace(LAddress,” “,”+”)+“&sensor=false”
Lat = tagdata(tagdata(LResult,“”,“”,1),“”,“”,1)
Lng = tagdata(tagdata(LResult,“”,“”,1),“”,“”,1)

Robert Ameeti

Best bet for putting code in a forum page is to indent it 4 spaces. Then it will automatically format as code, like this:

City_District=City(Postal_Code)
County=County(Postal_Code)
ST_Region =State(Postal_Code)

Here’s what my previous post looked like in the forum editor.

An easy way to indent Panorama code is to select it, press Command-] to shift it right 4 spaces, then copy it and paste into the forum editor. Then go back to the procedure editor and either Undo or press Command-[ to shift it back left again. You can also do this in BBEdit, Xcode and probably other editors.

The problem with all of the mapping solutions for this that I’ve seen is that you need to convert the physical address into latitude and longitude. The USPS used to offer limited conversions, on a one-time basis. Obviously, there are commercial outfits that will do this for you, but at a price each time. If your locations are at all dynamic, this can run into some real dough. You might be able to get away with just using the ZIP code if you never have more than one location in a ZIP code, though. Otherwise, each location in that ZIP code will have its icon stacked on all the other locations in that ZIP code. As Jim says, if you get this figured out, please share.

Bingo, that’s what I’ve been thinking is the real stickler here. I imagine that Google map’s ability to precisely localize addresses on a map is based on knowing those coordinates, but how are they getting them?

Robert, your code looks pretty straightforward (however it’s formatted) but it requires that you already know the lat and long of the location. I would only have addresses. On a large scale map just localizing by ZIP codes could be useful, but if we’re talking about a 20 mile radius of interest (pretty common in what I want to do) it wouldn’t be so much. I’m realizing that a text output would be only slightly less elegant but more useful. The queries are basically “Which customers [in the database] meet these criteria [based on database info] within X miles of [inquirer’s ZIP code]?” To sprinkle these out on a map would be cool, but may require more resources than I have and may not offer enough more info to be worth it. I’m going to keep fiddling with this. I appreciate all the interest in this issue.

I’ve not ever seen the Post Office do anything but correction of addresses and offering of a correction of zip code (which I do do before I seek out the lat long. First I get my address format and possible zip code corrected by the USPO. They I give my address and zip to Google Maps which gives me back the lat long.

I’ve done this for over 27,000 addresses and no one has ever asked me for money. :slight_smile: