First a little background on this project. Our company has developed a web page

Question

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.

Explanation / Answer

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.

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.