2

First a little background on this project.

Our company has developed a web page for our Inside Sales team that lets them enter a Zip Code and it returns the District Manager that is responsible in that area.

Currently we use Microsoft's MapPoint software to map the Sales Regions and Districts. Once we have the area mapped, we export the data to an excel spreadsheet for upload to an SQL database for the Web Site to utilize.

However, over the past few years of doing this I learned that not all of the postal codes are exported. I have had extensive conversations with Microsoft and they explained that this was a limitation of the software. All available Zip Codes are displayed in the software but only the ones listed as "Primary" in the coding are available for export. This leaves about 15,000 zip codes out.

So I developed a process that involves a lot of tedious look-up and manual correction for the missing zip codes.

What I really want to know is, does anyone know of any software that would allow me to tie into an SQL Database, that would allow a user to make corrections to a Geographical Gui (like MapPoint), that would in turn update the Database providing a complete list of Postal Codes that we could use to update the Site for our Inside Sales team.

We also use this data for Marketing Campaigns and other geographical systems throughout the company.

It would also need to be able to map Canada and Mexico.

I have done several searches and found several programs, but very few tie to SQL.

  • From my experience every site/solution that provides postal codes are incomplete. That is there will always be missing or inconsistent data. It is not really their fault though because it is hard to get hold of all zipcodes since there is no reliably central repository for practically every country. So your best bet would be try & do this manually. – Ram G Athreya May 1 '14 at 14:32
  • I have created a Zip Code DB for us and Canada. But I would really like ot tie it to a program that would allow super users to make the changes they need and have those changes reflect back to the DB to be consumed by other processes. – Brian D. Brubaker May 1 '14 at 16:14
1

There will always be some issues connecting a database with alpha numeric data to maps based on a matching field like postal code. For the simple fact that they have different update timings.

Roughly 2 options: Enhance your postal code database by geocoding the lacking postal codes heads up by hand. Your second option is to write a XY coordinate fallback procedure. We did this many years ago (in the early internet days so not available online and lost in a long depreciated GIS software tool :-) ):

  • When a postal code and / or XY coordinate lacks in the postal code base -> use another geocoding attribute (a database field with XY reference available) to digitise the XY of this record. E.g. community centre point. Streetname centre point, etc. If no reference is available, pick some centre point conveniently located in the middle of your view rect of the country. Eg. pick the centre a big lake in Canada.
  • When more than 1 record falls back to a certain point -> make a small numeric spread around this XY point and your data will not be project on top of each other. But when you zoom in it will be projected next to each other. This is important for accessibility via the map interface and signals through colours on the map. Give your fallback data a different icon (!) in the map, so you recognise the fact that is not a real postal code XY point but a second or third best derivative.
  • After the calculation above, write the numerical XY value's straight into the SQL database.

You could use openstreetmap or google maps and/or derivates to digitise the lacking points heads up and export them into SQL-format (via CSV if no direct export option is available)

Does this help? I tried to find a present-day alternative to our 1996 XY Fallback tool, but sorry, couldn't find it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.