Home > Database > Mysql Tutorial > Use Mysql to calculate address longitude, latitude, distance and real-time location

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

王林
Release: 2023-06-03 16:19:04
forward
1604 people have browsed it

    Preface

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

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

    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

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

    The background can also be modified

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

    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

    Use Mysql to calculate address longitude, latitude, distance and real-time location##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;
        }
    Copy after login

    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;
        }
    Copy after login

      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}
      Copy after login

      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!

    Related labels:
    source:yisu.com
    Statement of this Website
    The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
    Popular Tutorials
    More>
    Latest Downloads
    More>
    Web Effects
    Website Source Code
    Website Materials
    Front End Template