一、背景
最近,碰到了一个业务,是将数据库中所有的地址信息请求百度接口获取经纬度保存起来。有38万多个地址,想到的方案就是查出所有的地址字段加上主键字段,然后导出csv文件,读取这个文件,遍历请求百度api接口,获取经纬度信息,生成一个新的文件,作为一张表导入数据库,使用sql给地址刷一遍经纬度。
二、前期准备
1、生成需要转换的地址数据
(1)示例:查询sql需要筛选出经纬度字段为空的地址数据,之后的刷经纬度需要主键字段,所有也需要获取,然后导出一个文件。
1select external_id,address from customer where longitude is null and latitude is null and address is not null
(2)导出这条sql查出的记录,像下面这样,一个csv文件。
三、百度接口介绍
1、百度地址转经纬度接口支持返回json格式和xml格式
(1)get方式请求下面地址将返回json格式,key为自己在百度上申请的开发者密钥。
1 http://api.map.baidu.com/geocoder?address={address}&output=json&key=SkSf
(2)成功的返回格式如下:
1{ 2 "status":"OK", 3 "result":{ 4 "location":{ 5 "lng":123.473237, 6 "lat":41.833995 7 }, 8 "precise":1, 9 "confidence":80,10 "level":"\u95e8\u5740"11 }12}
(3)get方式请求下面地址将返回xml格式
1http://api.map.baidu.com/geocoder?address={address}&output=json&key=SkSf
(4)成功的格式如下:
1<?xml version="1.0" encoding="utf-8" ?> 2<GeocoderSearchResponse> 3 <status>OKstatus> 4 <result> 5 <location> 6 <lat>40.148852lat> 7 <lng>117.125265lng> 8 location> 9 <precise>0precise>10 <confidence>75confidence>11 <level>购物level>12 result> 13GeocoderSearchResponse>
(5)请求上面两个url,都可能返回失败内容,失败内容都是像下面这样,返回html页面。
1html> 2 3<html> 4<head> 5 <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1"> 6 <meta http-equiv="content-type" content="text/html;charset=utf-8"> 7 <meta content="always" name="referrer"> 8 <script src="https://ss1.bdstatic.com/5eN1bjq8AAUYm2zgoY3K/r/www/nocache/imgdata/seErrorRec.js">script> 9 <title>页é¢ä¸åå¨_ç¾åº¦æç´¢title>10 <style data-for="result">11 body {color: #333; background: #fff; padding: 0; margin: 0; position: relative; min-width: 700px; font-family: arial; font-size: 12px }12 p, form, ol, ul, li, dl, dt, dd, h3 {margin: 0; padding: 0; list-style: none }13 input {padding-top: 0; padding-bottom: 0; -moz-box-sizing: border-box; -webkit-box-sizing: border-box; box-sizing: border-box } img {border: none; }14 .logo {width: 117px; height: 38px; cursor: pointer }15 ...16
注意:无论想返回json格式还是xml格式,当请求返回这种html类型数据,就获取不到经纬度,需要收集下来重新请求。
四、功能实现
1、先来实现百度接口返回为xml格式并解析获取经纬度,最后附完整代码
(1)为了记录读取的csv文件的原始地址数据和请求百度接口获取经纬度数据,原始文件中有主键(external_id)和地址(address),请求接口返回我们需要的经度(longitude)维度(latitude),这四个字段都需要最终保存到生成的结果文件中,所以我们声明ResultBean类如下,来记录数据(省略setget方法)。
1 static class ResultBean { 2 3 private String external_id; 4 //百度经纬度 5 private String longitude; 6 private String latitude; 7 8 //address 9 private String address;1011 public ResultBean(String external_id, String address, String longitude, String latitude) {12 this.external_id = external_id;13 this.longitude = longitude;14 this.latitude = latitude;15 this.address = address;16 }1718 }
(2)读取导出的原始csv地址文件方法如下:通过CSVReader的write方法读取文件中的每条记录,保存到ResultBean,执行请求后面的经纬度方法。
1 public static void readCSV(List datas, String sourcePath) { 2 List failData = new ArrayList<>(); 3 try (CSVReader csvReader = new CSVReaderBuilder(new BufferedReader(new InputStreamReader(new FileInputStream(new File(sourcePath)), "utf-8"))).build()) { 4 Iterator<String[]> iterator = csvReader.iterator(); 5 //导出文件有标题行,去掉标题行,没有就不需要 6 iterator.next(); 7 while (iterator.hasNext()) { 8 String[] next = iterator.next(); 9 String address = next[1].replaceAll("\\s*", "");10 ResultBean resultBean = new ResultBean(next[0], address, null, null);1112 //百度接口地址转换经纬度方法13 getLngLat(datas, failData, resultBean);14 }1516 //失败数据再次请求百度接口,最多循环一千次,防止失败数据出现程序永不停止17 int i = 1000;18 while (failData.size() > 0 && i > 0) {19 List tempFailData = new ArrayList<>(failData);20 failData.clear();21 for (ResultBean resultBean : tempFailData) {22 getLngLat(datas, failData, resultBean);23 }24 i--;25 }2627 } catch (Exception e) {28 e.printStackTrace();29 } finally {30 System.out.println("fail record:" + failData.size());31 }32 }
(3)我们使用restTemplate的getForObject方法请求百度接口,得到响应的结果,从上面可以看出返回的正常数据都是String类型的,肯定有"GeocoderSearchResponse",会基于这个字符串判断是否返回了xml数据,防止返回上面所说的html类型的数据,导致xml转换为bean对象获取经纬度报错。请求百度接口方法如下:
1 /** 2 * 封装的获取经纬度方法 3 * @param datas 4 * @param failData 5 * @param resultBean 6 */ 7 private static void getLngLat(List datas, List failData, ResultBean resultBean) { 8 Map<String, String> map = Maps.newHashMap(); 9 map.put("address", resultBean.getAddress());10 String response = restTemplate.getForObject(URL, String.class, map);11 if (response.contains("GeocoderSearchResponse")) {12 GeocoderSearchResponse g = JAXB.unmarshal(new StringReader(response), GeocoderSearchResponse.class);13 if (g.status.equals("OK")) {14 resultBean.setLatitude(g.result.location.lat);15 resultBean.setLongitude(g.result.location.lng);16 datas.add(resultBean);17 } else {18 failData.add(resultBean);19 }20 } else {21 failData.add(resultBean);22 }23 }
(4)对于偶尔返回html类型的错误数据,会收集相应的ResultBean到failData集合中,执行完csv文件中的所有数据后,遍历失败的集合再次请求百度接口,重复拿到失败数据集合请求百度,直到没有失败数据,或者已经重复了1000次,结束请求百度接口,将百度的所有转换成功的数据写入结果文件中。部分代码如下:
1 //失败数据再次请求百度接口,最多循环一千次,防止失败数据出现程序永不停止 2 int i = 1000; 3 while (failData.size() > 0 && i > 0) { 4 List tempFailData = new ArrayList<>(failData); 5 failData.clear(); 6 for (ResultBean resultBean : tempFailData) { 7 getLngLat(datas, failData, resultBean); 8 } 9 i--;10 }
(5)当请求百度api返回正确xml数据以后, 需要将xml转换为bean,然后获取经纬度,很多博客说使用dom4j进行转换,但是我发现公司pom里没有dom4j这个依赖,加这个依赖需要向上申请,所以就使用了JAXB(Java Architecture for XML Binding) ,他是一个业界的标准,是一项可以根据XML Schema产生Java类的技术。通过分析上面返回的xml,我们需要建立三个类,一个是GeocoderSearchResponse,Result,Location,他们都需要加上@XmlRootElement注解。类声明如下:
1 @XmlRootElement(name = "GeocoderSearchResponse") 2 static class GeocoderSearchResponse { 3 private String status; 4 5 private Result result; 6 7 8 public String getStatus() { 9 return status;10 }1112 public void setStatus(String status) {13 this.status = status;14 }1516 public Result getResult() {17 return result;18 }1920 public void setResult(Result result) {21 this.result = result;22 }23 }2425 @XmlRootElement26 static class Result {27 private Location location;282930 public Location getLocation() {31 return location;32 }3334 public void setLocation(Location location) {35 this.location = location;36 }37 }3839 @XmlRootElement40 static class Location {41 private String lat;42 private String lng;4344 public String getLat() {45 return lat;46 }4748 public void setLat(String lat) {49 this.lat = lat;50 }5152 public String getLng() {53 return lng;54 }5556 public void setLng(String lng) {57 this.lng = lng;58 }59 }
注意:
①类名的首字母会自动变为小写去对应xml中的字段,由于xml中GeocoderSearchResponse直接是大写的,所以需要在注解上加name属性,否则可能报错:
unexpected element (uri:"", local:"GeocoderSearchResponse"). Expected elements are
②每个类的变量只能有一个获取方式,需要声明变量私有,通过getset方法获取,否则会报错:
Class has two properties of the same name "result
"
(6)当获取所有已经转换成功的经纬度信息后,将数据写入结果csv文件中,通过CsvWriter的write方法如下:
1 public static void writeCSV(List datas, String goalPath) {2 CsvWriter csvWriter = new CsvWriter(new File(goalPath));3 for (ResultBean data : datas) {4 csvWriter.write(new String[]{data.getExternal_id(), data.getAddress(), data.getLongitude(), data.getLatitude()});5 }6 csvWriter.close();7 }
(7)所有代码如下:
1package com.forceclouds.crm.local; 2 3import cn.hutool.core.date.StopWatch; 4import cn.hutool.core.text.csv.CsvWriter; 5import com.google.common.collect.Maps; 6import com.opencsv.CSVReader; 7import com.opencsv.CSVReaderBuilder; 8import org.springframework.web.client.RestTemplate; 9 10import javax.xml.bind.JAXB; 11import javax.xml.bind.annotation.XmlRootElement; 12import java.io.*; 13import java.util.ArrayList; 14import java.util.Iterator; 15import java.util.List; 16import java.util.Map; 17 18/* 19 *@create by jiankang 20 *@date 2020/6/3 time 14:59 21 */ 22 23public class GPSTest { 24 private static String URL = "http://api.map.baidu.com/geocoder?address={address}&key=SkSf"; 25 private static RestTemplate restTemplate = new RestTemplate(); 26 27 public static void main(String[] args) { 28 List datas = new ArrayList<>(); 29 ResultBean title = new ResultBean("external_id", "address", "longitude", "latitude"); 30 datas.add(title); 31 String sourcePath = "C:\\Users\\ForceClouds\\Desktop\\aaa.csv"; 32 String goalPath = "C:\\Users\\ForceClouds\\Desktop\\jiangkang0905.csv"; 33 StopWatch stopWatch = new StopWatch(); 34 stopWatch.start("经纬度转换运行程序"); 35 readCSV(datas, sourcePath); 36 stopWatch.stop(); 37 System.out.println(stopWatch.prettyPrint()); 38 writeCSV(datas, goalPath); 39 } 40 41 public static void writeCSV(List datas, String goalPath) { 42 CsvWriter csvWriter = new CsvWriter(new File(goalPath)); 43 for (ResultBean data : datas) { 44 csvWriter.write(new String[]{data.getExternal_id(), data.getAddress(), data.getLongitude(), data.getLatitude()}); 45 } 46 csvWriter.close(); 47 } 48 49 public static void readCSV(List datas, String sourcePath) { 50 List failData = new ArrayList<>(); 51 try (CSVReader csvReader = new CSVReaderBuilder(new BufferedReader(new InputStreamReader(new FileInputStream(new File(sourcePath)), "utf-8"))).build()) { 52 Iterator iterator = csvReader.iterator(); 53 //导出文件有标题行,去掉标题行,没有就不需要 54 iterator.next(); 55 while (iterator.hasNext()) { 56 String[] next = iterator.next(); 57 String address = next[1].replaceAll("\\s*", ""); 58 ResultBean resultBean = new ResultBean(next[0], address, null, null); 59 60 //百度接口地址转换经纬度方法 61 getLngLat(datas, failData, resultBean); 62 } 63 64 //失败数据再次请求百度接口,最多循环一千次,防止失败数据出现程序永不停止 65 int i = 1000; 66 while (failData.size() > 0 && i > 0) { 67 List tempFailData = new ArrayList<>(failData); 68 failData.clear(); 69 for (ResultBean resultBean : tempFailData) { 70 getLngLat(datas, failData, resultBean); 71 } 72 i--; 73 } 74 75 } catch (Exception e) { 76 e.printStackTrace(); 77 } finally { 78 System.out.println("fail record:" + failData.size()); 79 } 80 } 81 82 83 private static void getLngLat(List datas, List failData, ResultBean resultBean) { 84 Map map = Maps.newHashMap(); 85 map.put("address", resultBean.getAddress()); 86 String response = restTemplate.getForObject(URL, String.class, map); 87 if (response.contains("GeocoderSearchResponse")) { 88 GeocoderSearchResponse g = JAXB.unmarshal(new StringReader(response), GeocoderSearchResponse.class); 89 if (g.status.equals("OK")) { 90 resultBean.setLatitude(g.result.location.lat); 91 resultBean.setLongitude(g.result.location.lng); 92 datas.add(resultBean); 93 } else { 94 failData.add(resultBean); 95 } 96 } else { 97 failData.add(resultBean); 98 } 99 }100101102 @XmlRootElement(name = "GeocoderSearchResponse")103 static class GeocoderSearchResponse {104 private String status;105106 private Result result;107108109 public String getStatus() {110 return status;111 }112113 public void setStatus(String status) {114 this.status = status;115 }116117 public Result getResult() {118 return result;119 }120121 public void setResult(Result result) {122 this.result = result;123 }124 }125126 @XmlRootElement127 static class Result {128 private Location location;129130131 public Location getLocation() {132 return location;133 }134135 public void setLocation(Location location) {136 this.location = location;137 }138 }139140 @XmlRootElement141 static class Location {142 private String lat;143 private String lng;144145 public String getLat() {146 return lat;147 }148149 public void setLat(String lat) {150 this.lat = lat;151 }152153 public String getLng() {154 return lng;155 }156157 public void setLng(String lng) {158 this.lng = lng;159 }160 }161162163 static class ResultBean {164165 private String external_id;166 //百度经纬度167 private String longitude;168 private String latitude;169170 //address171 private String address;172173 public ResultBean(String external_id, String address, String longitude, String latitude) {174 this.external_id = external_id;175 this.longitude = longitude;176 this.latitude = latitude;177 this.address = address;178 }179180 public String getExternal_id() {181 return external_id;182 }183184 public String getAddress() {185 return address;186 }187188189 public String getLongitude() {190 return longitude;191 }192193 public String getLatitude() {194 return latitude;195 }196197 public void setExternal_id(String external_id) {198 this.external_id = external_id;199 }200201 public void setLongitude(String longitude) {202 this.longitude = longitude;203 }204205 public void setLatitude(String latitude) {206 this.latitude = latitude;207 }208209 public void setAddress(String address) {210 this.address = address;211 }212 }213}
2、实现返回json格式并解析获取经纬度
Ⅰ、第一种比较简单,只需要把上面完整代码中url改一下,并且getLngLat方法换一下就可以了
(1)请求百度api加一个output=json的参数。
1 private static String URL = "http://api.map.baidu.com/geocoder?address={address}&output=json&key=SkSf";
(2)通过new JacksonJsonParser()对返回值进行解析,注意判断返回值是否为html形式的内容,是的话需要收集重新请求。代码如下。
1 private static void getLngLat(List datas, List failData, ResultBean resultBean) { 2 Map<String, String> map = Maps.newHashMap(); 3 map.put("address", resultBean.getAddress()); 4// restTemplate.getMessageConverters().add(new MyMappingJackson2HttpMessageConverter()); 5 String response = restTemplate.getForObject(URL, String.class, map); 6 if (response != null && response.contains("lng") && response.contains("lat")) { 7 final Map<String, Object> geocoderResult = new JacksonJsonParser().parseMap(response); 8 Map location = (Map) ((Map) geocoderResult.get("result")).get("location"); 9 Double lng = (Double) location.get("lng");10 System.out.println(lng);11 Double lat = (Double) location.get("lat");12 System.out.println(lat);13 ResultBean bean = new ResultBean(resultBean.getExternal_id(), resultBean.getAddress(), String.valueOf(lng), String.valueOf(lat));14 datas.add(bean);15 } else {16 failData.add(resultBean);17 }18 }
Ⅱ、另一种解析json方式是我们根据返回值,封装一个bean对象使用RestTemplate的getForObject直接转换为bean类型
(1)封装的bean如下:
1 static class GeocoderResult implements Serializable { 2 String status; 3 Map result; 4 5 public String getStatus() { 6 return status; 7 } 8 9 public void setStatus(String status) {10 this.status = status;11 }1213 public Map getResult() {14 return result;15 }1617 public void setResult(Map result) {18 this.result = result;19 }2021 @Override22 public String toString() {23 return super.toString();24 }25 }
(2)请求百度api接口如下:
1 private static void getLngLat2(List datas, List failData, ResultBean resultBean) { 2 Map<String, String> map = Maps.newHashMap(); 3 map.put("address", resultBean.getAddress()); 4 restTemplate.getMessageConverters().add(new MyMappingJackson2HttpMessageConverter()); 5 try { 6 GeocoderResult geocoderResult = restTemplate.getForObject(URL, GeocoderResult.class, map); 7 if (geocoderResult.status.equals("OK")) { 8 Map location = (Map) geocoderResult.getResult().get("location"); 9 Double lng = (Double) location.get("lng");10 Double lat = (Double) location.get("lat");11 ResultBean bean = new ResultBean(resultBean.getExternal_id(), resultBean.getAddress(), String.valueOf(lng), String.valueOf(lat));12 datas.add(bean);13 } else {14 failData.add(resultBean);15 }16 } catch (RestClientException e) {17 failData.add(resultBean);18 }19 }
(3)上面方法中我们对restTemplate加了一个自定义的MyMappingJackson2HttpMessageConverter实例,之所以自定义一个,是因为restTemplate不支持接口返回MediaType类型为text/javascript以及为text/html类型的返回值的转换,不加会报错信息:
UnknownContentTypeException: Could not extract response: no suitable HttpMessageConverter found for response type [class GPSTest2$GeocoderResult] and content type [text/javascript;charset=utf-8],
同时当返回html这种错误值时,会转换失败进入catch代码块中,我们需要收集起来,下次继续请求。MyMappingJackson2HttpMessageConverter类代码如下
1 static class MyMappingJackson2HttpMessageConverter extends MappingJackson2HttpMessageConverter {23 public MyMappingJackson2HttpMessageConverter() {4 List mediaTypes = new ArrayList<>();5 mediaTypes.add(new MediaType("text", "javascript"));6 mediaTypes.add(new MediaType("text", "html"));7 setSupportedMediaTypes(mediaTypes);8 }9 }
五、成果展示及总结
不论是请求百度接口返回xml类型获取经纬度还是返回json类型获取经纬度,都会得到同样的结果,程序正确执行完成。
1、控制台输出
2、同时生成一个csv结果文件,使用excel打开部分结果如下
将拿到的结果文件导入数据库的新表中,写一个sql语句通过主键条件更新源表的经纬度字段就顺利完成任务。以上就是对地址转换经纬度的一点总结和分享,觉得不错的话,欢迎关注java基础笔记。