Select records based zipcode, it's radius, lat and lon in MySQL

Question

Below is my company table with it's postalcode, lat, lon and the radius in kilometers where each company is able to provide his services.

id  company_name   city                 postalcode  radiu   latitude       longitude
 1  A              Drogteropslagen      7705 PA     10      52.61666700    6.50000000
 2  B              Coevorden            7740 AA     15      52.66666700    6.75000000
 3  C              Emmen                7812 TN     5       52.78333300    6.9000000
 4  D              Emmer-Compascuum     7881 PZ     25      52.81666700    7.05000000
 5  E              Nieuw-Dordrecht      7885 AA     60      52.75000000    6.96666700

I would like to select the companies which a particular postalcode e.g. 7813 AB lives within their postalcode + the radius, even this postalcode 7813 AB is not exact the same as that of a company. how to write a sql query to select those companies?

@sagi He already asked this question [here](http://stackoverflow.com/questions/37404372/select-records-by-zipcode-and-its-radius-in-mysql/37404516?noredirect=1#comment62325499_37404516), but never even gave me the new link. — Tim Biegeleisen, May 24 '16 at 09:35
@sagi I expect to get at least record id = 3 and 5 because the postalcode 7813 AB is nearby 7812 TN and for the record id = 5, because it's radius is 60 km., quite long, thus the postalcode 7813 AB should lives within this area. It is just an idea. Finally it is based on the calculation of the lat and lon plus the radius of those companies. — O Connor, May 24 '16 at 09:39

Tim Biegeleisen · Answer 1 · 2016-05-24T13:41:44.450

2

SELECT t1.company_name, t2.company_name,
    (6371 * acos(cos(radians(t1.lat1)) * cos(radians(t2.lat2)) 
    * cos(radians(t2.lng2) - radians(t1.lng1)) + sin(radians(t1.lat1)) * sin(radians(t2.lat2)))) AS distance,
    t1.radius
FROM
(
    SELECT company_name, latitude AS lat1, longitude AS lng1,
        radius
    FROM company
    WHERE postalcode = '7813 AB'
) t1
CROSS JOIN
(
    SELECT company_name, latitude AS lat2, longitude AS lng2
    FROM company
) t2
HAVING distance < t1.radius AND t1.company_name != t2.company_name

edited May 24 '16 at 13:41

answered May 24 '16 at 09:35

Tim Biegeleisen

502,043
27
286
360

thanks for trying to help me. I am trying to figure out where to put the particular postcalcode as the condition e.g. where postalcode = "7813 AB"; because this postalcode will come from the end user input. – O Connor May 24 '16 at 09:45
Replace `WHERE c.company_name = 'A'` with `WHERE postalcode = '7813 AB'` in the first subquery. – Tim Biegeleisen May 24 '16 at 09:46
I have the following sql error: #1054 - Unknown column 'distance' in 'where clause'. It seems like the alias distance is not working. – O Connor May 24 '16 at 10:04
Try using `HAVING distance < ...` instead – Tim Biegeleisen May 24 '16 at 10:07
#1054 - Unknown column 't1.searchRadius' in 'having clause' – O Connor May 24 '16 at 10:08
still get: #1054 - Unknown column 't1.searchRadius' in 'having clause'. Maybe it found nothing in t1? – O Connor May 24 '16 at 10:30
Sorry man, but still get an error: #1054 - Unknown column 't1.radius' in 'having clause' I really don't understand why it said radius column is unknown. It is right there. – O Connor May 24 '16 at 13:03
My last attempt: I added `t1.radius` to the `SELECT` list, e.g. `SELECT t1.radius` – Tim Biegeleisen May 24 '16 at 13:42
There is no error. But I've got no records back. Maybe because the given zipcode does not exist in the table? – O Connor May 24 '16 at 14:36
Please try using a postal code which you know for certain exists. – Tim Biegeleisen May 24 '16 at 14:50
That is not the idea that a given postal code must exist in the company table. The idea is even the given postal code does not exist in the company table but it lives in the radius of other postal codes of the existing companies. Thus those companies will be selected. Maybe I should join the company table with the postal table. Because the postal table contains ALL the postal codes, thus the given postal code as well. – O Connor May 25 '16 at 07:41
That won't work. There needs to be a _relation_ between a postal code and its latitude and longitude. Otherwise how can the query know where that postal code is located? – Tim Biegeleisen May 25 '16 at 07:59
Then we first should select the latitude and the longitude of the given postal code from the postalcode table and then use that lat and lon to query in the company table? – O Connor May 25 '16 at 08:10
I don't think you understand the query, because this is precisely what it is already doing. If you are getting back no results, it means you chose a postal code which does not exist in the table. And you no longer have 2 tables in this question, you only have one. – Tim Biegeleisen May 25 '16 at 08:11
The chosen postal code does not exist in the company table, because the company table contains companies, it cannot contain ALL the postal code of a country. but the chosen postal code exists in the postal_code table, there we can query the lat and lon of the chosen postal code. – O Connor May 25 '16 at 08:15
You changed the problem again. Return to the first one and ask there. – Tim Biegeleisen May 25 '16 at 08:17
Sorry that I changed the problem. This is because when I look at your query above I realise that I need the lat and lon of the chosen postal code when user inputs his postal code and that postal code might not exist in the company table. – O Connor May 25 '16 at 08:20
Go back to your original question. – Tim Biegeleisen May 25 '16 at 08:22

JSchirrmacher · Answer 2 · 2016-05-24T14:46:18.560

0

You can use spatial datatypes for longitude and latitude (or convert them) and then calculate the difference with st_difference, see https://dev.mysql.com/doc/refman/5.7/en/spatial-operator-functions.html#function_st-difference

Example:

SELECT st_distance(POINT(50.0, 8.0), POINT(latitude, longitude)) FROM company;

The distance is, however, a approximation and works best for small distances. With longer distances the calculation error increases due to the fact, that MySQL only calculates planar coordinates (Euclidean geometry).

edited May 24 '16 at 14:46

answered May 24 '16 at 09:56

JSchirrmacher

3,243
2
19
27

Can you show me some query sample based on my database table above? – O Connor May 24 '16 at 13:07

score 0 · Answer 3 · answered May 24 '16 at 11:17

You will need to convert the input post code to long/lat coordinates.

There are two ways to do this; the most performant way is to import a table with post code and long/lat coordinates. You can find a dataset here for the Netherlands.

The alternative is to use a geocoding API; there are several available; Google is your friend. This can be a performance problem (if you have thousands of customers all submitting post codes at the same time), and may require licensing from the API provider.

Once you have the long/lat for your input post code, you can use geo-spatial logic in MySQL to calculate what's in the radius.

how to use geo-spatial logic in MySQL? – O Connor May 24 '16 at 13:05 — O Connor, May 24 '16 at 13:05

Select records based zipcode, it's radius, lat and lon in MySQL

3 Answers3

Linked