Select records based zipcode, it's radius, lat

问题:

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?

回答1:

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

回答2:

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.

回答3:

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).