Click on the banner to learn about and purchase my database training on Azure

SQL Server - How to identify the store closest to the customer (or the distance between 2 locations) from the zip code (without API)

Views: 2.070 views
Reading Time: 22 minutes

Speak guys!

In this very interesting article, I'm going to share with you a very creative solution to identify the store closest to the customer from that customer's zip code, without using an API. All the “magic” is in the fact that the zip code seems to be calculated according to the distance from that location and because of that, finding the nearest store can be absurdly simple and effective. And who shared with me that this worked was the Lucas Galon, about 4 years ago.

What will we learn to do in this post:

Why does this work?

According to Official Post Office documentation on the CEP, each CEP number corresponds to a Region, Sub-region, Sector, Sub-sector and Sub-Sector Divisor, as shown in the image below:

So there is logic behind these numbers yes, and it also reflects how closely one zip code is physically close to each other based on the zip number.

Important: The zip code is assigned as if it were a circle, so if you have many stores close to the zip code you are looking for, in the same area, you may return a store very close, but this store may not be as close as possible, as I will demonstrate in the examples below.

To return the exact information, then you would have to use the latitude and longitude or some API to calculate the distance, but besides taking a lot longer to process this for several stores, the code would still be much more complex.

If you need to calculate the exact distance, without margin for error, you can try some of these approaches:

Test base creation

To create a database for demonstration and be as close as possible to a real one to help you think about how to apply it in your scenario and in your daily life. For this, I used the real base of the Carrefour stores, which I extracted from the website, I unchecked all the filter options and clicked the “Show more stores” button until all stores are showing on the page.

After that, I ran the jQuery script below:

Then I copied the result and pasted it into a CSV file and then imported this file into SQL Server.

Base import script:

How to identify the store closest to the customer using the zip code

And now, let's put this calculation into practice, which is basically, transform the 2 zip codes (the stores and the one that will be searched) to the integer type, calculate the numerical difference between the stores zip codes and the zip code that will be searched and demand the store that has the smallest difference. That simple.

Example using a CEP from Espírito Santo:

And the closest Carrefour store is:

Pretty cool huh? Let's run another test. If I am at Universidade Anhembi Morumbi – Vila Olímpia Campus (CEP 04546-001), the nearest Carrefour store is:

And is it true?

It worked! That Cool! But it's not all flowers. Unfortunately, this calculation is pretty approximate but not exact! There are some situations that don't bring the store as close as possible (although it always returns a relatively close store)

Example: Farol Santander (CEP 01014-900)

For this zip code, the query returned to the Express Senador Queirós store, which is not far from Farol Santander, but is not the closest store possible, according to Google Maps:

To make this calculation easier to use, we can create a function for this:

And now let's use this function for CEP 20921-060 (Estadio São Januário):

Checking the result:

And success too 🙂

Well folks, I hope you liked this tip, which allows you to offer a quick, simple and performance solution to calculate the customer's closest store from the zip code, without using an API and returning very approximate data.

A big hug and see you in the next post.