JAVA code for converting addresses to latitude and longitude coordinates
- 2020-04-01 02:20:14
- OfStack
Task: there are more than 1000 store information (put in excel, including address, store name, phone number, etc., but no latitude and longitude coordinates), the boss asked me to use the address through baidu map pick up coordinate system to find the corresponding coordinates, and then add the coordinates to update to the company's database.
1, the use of the key wizard, the key wizard is a simulation of the keyboard and mouse operation of the software, used to write action script, due to time constraints, not much research, because the whole set of action is too complex to try the key wizard gave up.
2, form filling tool (is to submit the exel form batch to the web page), what wind more, black coat paint (especially this black coat paint, money, pit goods) have tried, the results are not satisfactory. Because I had to submit excel content to the web page and get it from the web page, the bulk registration software didn't work.
Solution: I finally did my job - writing code - and solved the problem. The idea is: pass in the address as a parameter splicing url call baidu map, and then parse the returned page, extract latitude and longitude coordinates.
Here are the steps
1. Modify the property name in the excel sheet (easy to be read later by query) and then pour it into the database.
2. Code implementation
Entity class
public class ShopInfo {
private String name;
private String scope;
private String address;
private String mobile;//Mobile phone
private String phone;//Machine
private String description;
private String lat;//Longitude
private String lng;//Latitude
public ShopInfo() {
}
//. Get and set methods & NBSP;
Key code Simulate HTTP and parse the returned data:
public class DbManager{
private Connection con = null ;
private Statement sta = null ;
private ResultSet rs = null ;
private PreparedStatement ps = null ;
private Connection cons = null ;
private Statement stas = null ;
private ResultSet rss = null ;
private PreparedStatement pss = null ;
//Connect to local mysql parameters? The last parameter is the & NBSP; that solves the Chinese garbled code;
private String MYSQLDRIVER="com.mysql.jdbc.Driver" ;
private String CONTENT="jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8";
private String UN="***";
private String UP="****";
//Connect to server mysql parameter & NBSP;
private String MYSQLDRIVER1="com.mysql.jdbc.Driver" ;
private String CONTENT1="jdbc:mysql://***********:3306/test?useUnicode=true&characterEncoding=utf8";
private String UN1="*******";
private String UP1="****";
public DbManager()
{
try {
Class.forName(MYSQLDRIVER);
System.out.println(" loading MySQL drive ...");
con = DriverManager.getConnection(CONTENT,UN,UP);
sta = con.createStatement();
System.out.println(" Connection to local database successful!! ");
Class.forName(MYSQLDRIVER1);
System.out.println(" loading MySQL drive ...");
cons = DriverManager.getConnection(CONTENT1,UN1,UP1);
stas = cons.createStatement();
System.out.println(" Connection to server successful!! ");
} catch (Exception e) {
e.printStackTrace();
}
}
public ArrayList<ShopInfo> getAll(String tablename) throws SQLException{
ArrayList<ShopInfo> allShops=new ArrayList();
ShopInfo si;
String sql="select * from "+tablename;
System.out.println(sql);
rs=sta.executeQuery(sql);
while(rs.next()){
si=new ShopInfo();
si.setAddress(rs.getString("address"));
si.setDescription(rs.getString("names")+" You are welcome ");
si.setMobile(rs.getString("keeperphone"));
si.setScope(tablename);
si.setPhone(rs.getString("shoptel"));
getPoint(si);
allShops.add(si);
System.out.println(" longitude :"+si.getLat()+" latitude :"+si.getLng());
}
return allShops;
}
//-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- "key code according to address to obtain the coordinate" -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
public void getPoint(ShopInfo shop){
try {
String sCurrentLine;
String sTotalString;
sCurrentLine = "";
sTotalString = "";
java.io.InputStream l_urlStream;
java.net.URL l_url = new java.net.URL("http://api.map.baidu.com/geocoder/v2/?address="+shop.getAddress().replaceAll(" ", "")+"&output=json&ak=702632E1add3d4953d0f105f27c294b9&callback=showLocation");
java.net.HttpURLConnection l_connection = (java.net.HttpURLConnection) l_url.openConnection();
l_connection.connect();
l_urlStream = l_connection.getInputStream();
java.io.BufferedReader l_reader = new java.io.BufferedReader(new java.io.InputStreamReader(l_urlStream));
String str=l_reader.readLine();
//Divide the returned web code with longitude & PI;
String s=","+"""+"lat"+"""+":";
String strs[]=str.split(s, 2);
String s1="""+"lng"+"""+":";
String a[]=strs[0].split(s1, 2);
shop.setLng(a[1]);
s1="}"+","+""";
String a1[]=strs[1].split(s1, 2);
shop.setLat(a1[0]);
} catch (Exception e) {
e.printStackTrace();
}
}
//Put it in a database & NBSP;
public void inputAll(ArrayList<ShopInfo> allShops){
System.out.println(" Start writing to the server ");
String sql2="insert into test.dc_shop (name,scope,address,phone,description,image,createtime,lat,lng) values (?,?,?,?,?,?,?,?,?)";
try {
pss=cons.prepareStatement(sql2);
System.out.println("------------------------- Number of data bars waiting to be written : "+allShops.size());
for(int i=0;i<allShops.size();i++){
pss.setString(1,allShops.get(i).getName());
pss.setString(2, allShops.get(i).getScope());
pss.setString(3, allShops.get(i).getAddress());
pss.setString(4, allShops.get(i).getPhone());
pss.setString(5, allShops.get(i).getDescription());
pss.setString(6, null);//Picture path & NBSP;
pss.setString(7, allShops.get(i).getMobile());
pss.setString(8, allShops.get(i).getLat());
pss.setString(9, allShops.get(i).getLng());
pss.executeUpdate();
}
pss.close();
cons.close();
System.out.println("--->OK");
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println(" to mysql An exception occurred while updating the data! ");
e.printStackTrace();
}
}
I'm just going to call main.