Use Mysql to calculate address longitude, latitude, distance and real-time location

Preface

When I was working on a project recently, I encountered such a demand,

Click to use the cards and coupons in the card package. You need to display the stores nearby where the cards and coupons are used.

Ideas

Database address table design

• General Area street address table tz_sys_area

##parent_idbigintThe ID of the parent arealevelintLevel

Field name	Type	Remarks
area_id	bigint	Area ID
area_name	varchar(32)	area Name
parent_id	bigint	belongs to the parent area ID
level	int	Level
type	char	Region type 0 Country 1 Province Municipality 2 Prefecture City 3 District County
area_name	varchar(32)	Area name

The background can also be modified

The source of the fourth-level regional address data is the json file I found online and then poured it into the database according to the format.

• Store address table tz_address

##Requirement realization

You need to use longitude and latitude to calculate the distance here
You need to use the Amap API interface geography/reverse geocoding to get the address longitude and latitude and save it

 /**
     * 地理/逆地理编码
     * https://lbs.amap.com/api/webservice/guide/api/georegeo
     *
     * @return
     */
    public String addressToLongitude(String address) {
        String longitude = "";
        String urlString = "?key={key}&address={address}&output=JSON";
        String response = restTemplate.getForObject(ApiAction.API_GEOREGO_TEST + urlString, String.class, apiKey, address);
        if (StrUtil.isEmpty(response)) {
            return null;
        }
        JSONObject jsonObject = JSON.parseObject(response);
        String code = jsonObject.getString("infocode");
        if (code.equals("10000")) {
            JSONArray jsonArray = jsonObject.getJSONArray("geocodes");
            JSONObject jsonObject1 = (JSONObject) jsonArray.get(0);
            longitude = jsonObject1.get("location").toString();
        } else {
            return null;
        }
        return longitude;
    }

Use

 private Address setlngAndLat(Address address) {
        String addr = address.getProvince() + address.getCity() + address.getArea() + address.getAddr();
        String longitude = gaoDeService.addressToLongitude(addr);
        if (StrUtil.isBlank(longitude)) {
            throw new BusinessException("地址经纬度识别识别");
        }
        String lat = longitude.split(",")[1];
        String lng = longitude.split(",")[0];

        address.setLat(lat);
        address.setLng(lng);
        return address;
    }

MySQL calculates the distance between the address and the current location based on the latitude and longitude

•  SELECT
                 (
                         6371 * acos(
                                     cos(radians(#{lat}))
                                     * cos(radians(lat))
                                     * cos(radians(lng) - radians(#{lng}))
                                 + sin(radians(#{lat}))
                                         * sin(radians(lat))
                         )
                     ) AS distance
          FROM tz_user_addr where addr_id=#{storeAddrId}

The above is the detailed content of Use Mysql to calculate address longitude, latitude, distance and real-time location. For more information, please follow other related articles on the PHP Chinese website!