百度地图Geocoding API获取特定地区或地址的经纬度信息，然后在MySql数据库多表循环遍历更新
1、数据库多表循环更新的思想在这个链接上，标题是“MySql多表循环遍历更新”，
链接地址http://www.cnblogs.com/litao4047/archive/2013/05/31/3108753.html先看看这个思想，如果你需要这样的思路... ...
2、“利用百度地图Geocoding API获取特定地区或地址的经纬度信息”，这个链接讲的是从百度地图API获取地址解析数据，为数据库表更新提供数据基础。http://www.cnblogs.com/litao4047/archive/2013/05/30/3107565.html
以上两个链接就是为下面的思路做铺垫的，方便我的讲述。。。
首先，要借用别人的服务，通过别人的服务接口获取自己想要的数据。Geocoding API 是百度提供的服务接口，主要是用于提供从地址解析到经纬度坐标或者从经纬度坐标解析到地址的转换服务。我们今天要说的就是地址解析，通过现有的地址数据解析服务，获取经纬度数据。代码示例如下：
public bool getLatitudeAndLongitude(string city, string address) { bool flag = true; WebClient client = new WebClient();//webclient客户端对象 string url = "http://api.map.baidu.com/geocoder/v2/?ak=2ae1130ce176b453fb29e59a69b18407&callback=renderOption&output=xml&address=" + address + "&city=" + city; client.Encoding = Encoding.UTF8;//编码格式 string responseTest = client.DownloadString(url);//下载xml响应数据 try { XmlDocument doc = new XmlDocument();//创建XML文档对象 if (!string.IsNullOrEmpty(responseTest)) { doc.LoadXml(responseTest);//加载xml字符串 doc.Save(@"F:\更新数据库\UpdataDataBase\UpdataDataBase\Location.xml"); flag = true; } } catch (Exception e) { flag = false; } return flag; }
上面的代码我就不做具体是介绍了，基本上是从API上获取数据，然后呢，以xml文件格式存储在本地，方便更新的时候从本地读取数据。。
第二，就是读取上面存储的xml文件格式的数据，写个方法（传的参数是xml文件路径），将经纬度这两个数据存储在数组里面，返回数组。
//获取默认主页的数据 internal static string[] getDBInfo(string path) { try { string[] _data = new string[2]; XmlDocument xmldoc = new XmlDocument(); xmldoc.Load(path); XmlNodeList xnRoot = xmldoc.SelectNodes("/GeocoderSearchResponse/result/location"); foreach (XmlNode node in xnRoot) { _data[0] = node.SelectSingleNode("lat").InnerXml.ToString(); _data[1] = node.SelectSingleNode("lng").InnerXml.ToString(); } return _data; } catch (Exception settingEx) { return null; } }
第三，获取到数据之后，建一个Model类
using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace UpdataDataBase { //数据模型，存放数据 public class DataModel { private string id; public string Id { get { return id; } set { id = value; } } private string city; private string address; private decimal latitude; private decimal longitude; public string City { get { return city; } set { city = value; } } public string Address { get { return address; } set { address = value; } } public decimal Latitude { get { return latitude; } set { latitude = value; } } public decimal Longitude { get { return longitude; } set { longitude = value; } } } }
第四，连接数据库，以city，adress来查询数据（传参表名）
private static List<DataModel> GetDataConn(string dataTable) { List<DataModel> dataSource = new List<DataModel>(); using (MySqlConnection conn = new MySqlConnection(MySqlString)) { string MySqlSelect = "select id,city,address,latitude,longitude from " + dataTable; MySqlCommand Command = new MySqlCommand(MySqlSelect, conn); conn.Open(); using (MySqlDataReader dataReader = Command.ExecuteReader()) { while (dataReader.Read()) { DataModel dataModel = new DataModel(); dataModel.Id = dataReader["id"].ToString(); dataModel.City = (string)dataReader["city"]; dataModel.Address = (string)dataReader["address"]; if (dataReader["latitude"] != DBNull.Value) { dataModel.Latitude = (decimal)dataReader["latitude"]; } else dataModel.Latitude = 0.0M; if (dataReader["longitude"] != DBNull.Value) { dataModel.Latitude = (decimal)dataReader["longitude"]; } else dataModel.Latitude = 0.0M; dataSource.Add(dataModel); } dataReader.Close(); dataReader.Dispose(); } conn.Close(); conn.Dispose(); } return dataSource; }
第五，更新方法（传四个参数） ，如下：
private static void UpdataDataBase(string id, string dataTable, decimal lat, decimal lng) { using (MySqlConnection conn = new MySqlConnection(MySqlString)) { string MySqlUpdata = "update " + dataTable + " set latitude=" + lat + ",longitude=" + lng + " where id=" + id; MySqlCommand Command = new MySqlCommand(MySqlUpdata, conn); conn.Open(); Command.ExecuteNonQuery(); conn.Close(); conn.Dispose(); } }
最后，就是在Main()方法中调用，这个非常关键，提醒一点就是必须要做异常处理，否则将出现很多问题....
static void Main(string[] args) { string[] tableName = new string[19]; tableName[0] = "data_abroadstudy_agency_info"; tableName[1] = "data_bank_atm"; tableName[2] = "data_car_repair"; tableName[3] = "data_childrenschool_info"; tableName[4] = "data_college_info"; tableName[5] = "data_daijia"; tableName[6] = "data_gasstation"; tableName[7] = "data_government_service_point"; tableName[8] = "data_highschool_info"; tableName[9] = "data_highway_service_station"; tableName[10] = "data_housecare_service_info"; tableName[11] = "data_juniorschool_info"; tableName[12] = "data_lawyer_service_info"; tableName[13] = "data_parkyard"; tableName[14] = "data_pet_hostpital"; tableName[15] = "data_pharmacy_info"; tableName[16] = "data_secondhand_car"; tableName[17] = "data_shopingmall_info"; tableName[18] = "data_train_plane_tickets_daishou"; GetLatitudeAndLongitude getLatLng = new GetLatitudeAndLongitude(); int i = 0; for (i = 0; i < tableName.Length; i++) { List<DataModel> DataSource = new List<DataModel>(); int h = 0; DataSource = GetDataConn(tableName[i]); foreach (DataModel dm in DataSource) { bool flag = false; string[] info = new string[2]; flag = getLatLng.getLatitudeAndLongitude(dm.City, dm.Address); try { if (flag) { info = XMLSettings.getDBInfo(@"F:\更新数据库\UpdataDataBase\UpdataDataBase\Location.xml"); if (info[0].Length > 10) dm.Latitude = Convert.ToDecimal(info[0].Substring(0, 10)); else if (info[0].Length == 0) continue; else dm.Latitude = decimal.Parse(info[0]); if (info[1].Length > 10) dm.Longitude = Convert.ToDecimal(info[1].Substring(0, 10)); else dm.Longitude = Convert.ToDecimal(info[1]); UpdataDataBase(dm.Id, tableName[i], dm.Latitude, dm.Longitude); Console.WriteLine("第" + h + "条记录以更新"); h++; } } catch (Exception ex) { continue; } } Console.WriteLine(tableName[i] + "此表已更新完毕！！！"); } }
写到这，也算是结束了。毕竟也有很多不足之处，如果大家能够用到，就好好改善改善...
