Java converts address to latitude and longitude coordinates

Task: there are more than 1000 store information records (in excel, including address, store name, phone number, etc.). The boss asked me to use the address to find the corresponding coordinates, then, the coordinates are added and updated to the company's database.

: 1. Using the keyboard wizard, the keyboard wizard is a software that imitates keyboard and mouse operations. It is used to write action scripts. Due to time constraints, I have not studied it much, because the whole set of actions is too complicated, the key-pressing Wizard will give up after trying it.

2. The form filling tool (that is, the exel table is submitted to the webpage in batches), and wuko paint (especially the uuko paint, but also the money and the goods) have all tried, the results are not satisfactory. Because I want to submit the content in excel to the webpage and obtain the corresponding content from the webpage, these software for batch registration will not be used.

: Finally, I started to solve the problem ..

The procedure is as follows:

1. Modify the attribute name in the excel table (which can be easily followed by query and read) and then import it to 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; // landline private String description; private String lat; // longitude private String lng; // latitude public ShopInfo (){}//.... get and set methods

:

/** Database Connection management class */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 the local mysql parameter? The following parameters are 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 = "****"; // connection server mysql parameter 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 ("load MySQL driver... "); con = DriverManager. getConnection (CONTENT, UN, UP); sta = con. createStatement (); System. out. println ("the local database is connected successfully !! "); Class. forName (MYSQLDRIVER1); System. out. println ("load MySQL driver... "); cons = DriverManager. getConnection (CONTENT1, UN1, UP1); stas = cons. createStatement (); System. out. println ("connection to the 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" using tablename=system.out.println( SQL ?rs=sta.exe cuteQuery (SQL); while (rs. next () {si = new ShopInfo (); si. setAddress (rs. getString ("address"); si. setDescription (rs. getString ("names") + "welcome"); si. setMobil E (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 obtains the coordinates of ShopInfo shop {try {String sCurrentLine; String sTotalString according to the address; 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 s Tr = l_reader.readLine (); // returns the webpage code 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 () ;}// save it to the public void inputAll (ArrayList <ShopInfo> allShops) {System. out. println ("starting to write to server"); Stri Ng 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 records waiting for writing:" + 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 ); // Image path: pss. setString (7, allShops. get (I ). getMobile (); pss. setString (8, allShops. get (I ). getLat (); pss. setString (9, allshops.get(i0000.getlng(+00000000pss.exe cuteUpdate ();} pss. close (); cons. close (); System. out. println ("---> OK");} catch (SQLException e) {// TODO Auto-generated catch blockSystem. out. println ("an exception occurred when updating data to mysql! "); E. printStackTrace ();}}

If you have any questions, please leave a message.