Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to use Mysql to calculate the real-time location of longitude and latitude distance". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Preface

Recently, when I was working on a project, I encountered a requirement like this.

Click to use the card coupon in the card package, and you need to show the card coupon to use the nearby store.

Design of address Table of thought Database

General area street address table tz_sys_area

Field name type remarks area_idbigint region IDarea_namevarchar (32) region name parent_idbigint parent region IDlevelint level typechar region type 0 country 1 province municipality 2 prefecture 3 district county area_namevarchar (32) area name parent_idbigint parent region IDlevelint level

Backstage can also make changes.

Level 4 regional address data source I found the json file on the Internet and then poured it into the database according to the format.

Store address table tz_address

Demand realization

Latitude and longitude are needed to calculate the distance here.

Need to use Amap api interface geocoding / inverse geocoding to obtain the longitude and latitude of the address and save it

/ * * Geographic / inverse geocoding * 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 ("address Latitude and Longitude Identification");} String lat = longitude.split (",") [1] String lng = longitude.split (",") [0]; address.setLat (lat); address.setLng (lng); return address;}

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

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} "this is how to calculate the real-time location of longitude and latitude distance by using Mysql". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.