WebServer实现SQL数据库百度坐标转换转化的算法

数据格式：

gis://23.140343;113.327019;0;2013-06-05 09:44:32;64;null     

格式说明：

<!--gis://纬度;经度;星数;年-月-日 时:分:秒;定位方式（0 GPS 1 公司基站 2 免费驴博士 4 Google 8 收费驴博士 16 mock 32 iphone 64 百度）;地址-->

表名：TBasStore

字段：Col3Col4Col5分别是经度纬度，定位类型

表名：TPlaActualVisit   

字段：PositioningType 采集字段

连接调用方式：

http://localhost:11209/Services/TransformSqlLatLng.asmx

http://localhost:11209/Services/TransformSqlLatLng.asmx/ConvertSqlLatLng?datasourceid=d5bfbe03-867e-4e32-b2dc-d87eb384f5cf&entnumber=1001051

http://localhost:11209/Services/TransformSqlLatLng.asmx/ConvertSqlLatLng?datasourceid=940c8bb2-3453-457e-831d-65ce167fe26c&entnumber=1002420

原始数据：

select StoreID,Col3,Col4,Col5 from TBasStore where col5=0

75849    113.970263    22.558992    0

75850    113.969306    22.562244    0

75851    113.970596    22.561417    0

75852    113.970604    22.561618    0

75867    113.971877    22.540232    0

75869    113.968206    22.540837    0

75870    113.968148    22.540745    0

75871    113.970669    22.5587    0

75872    113.970261    22.558932    0

75873    113.97043    22.558823    0

75879    113.97261    22.557824    0

75880    113.97263    22.557924    0

75882    113.940676    22.554949    0

转换后的数据：

75849    113.98166958385    22.56213701767    64

75850    113.98071725529    22.565370537455    64

75851    113.98200483986    22.56456919428    64

75852    113.98201288564    22.564770379133    64

75867    113.98326646456    22.54340440827    64

75869    113.97960033913    22.543936063106    64

75870    113.97954218276    22.543843065589    64

75871    113.98207480847    22.561852886284    64

75872    113.98166763098    22.562076751591    64

75873    113.98183621594    22.56197113887    64

75879    113.98401271458    22.561014719663    64

75880    113.98403282185    22.56111516862    64

75882    113.95211178699    22.557607236411    64

原始点：（75849    113.970263    22.558992    0）

修正后：（75849    113.98166958385    22.56213701767    64）

修正误差对比：

整个算法流程：

/*
 *   Created by SharpDevelop.
 *  User: Administrator
 *  Date:  2013/6/3
 * Time:   17:01
 * 
 *  To change this template use Tools | Options |  Coding | Edit Standard   Headers.
 */
using System;
using System.Web.Services;
using System.Data;
using XuanWu.Software.EasyInfo.BLL;
using XuanWu.Software.EasyInfo.ECModel;
using System.Collections;
using System.Collections.Generic;
using XuanWu.Software.EasyInfo.EnterpriseClient.Common;
using System.Threading.Tasks;
using System.Threading;
using System.Text;
namespace XuanWu.Software.EasyInfo.EnterpriseClient.Services
{
    /// <summary>
    /// 数据坐标转换服务(只对TPlaActualVisit表和TBasStore两个表进行)
    /// </summary>
    [WebService
      (    Name  = "TransformSqlLatLng",
          Description  = "TransformSqlLatLng",
          Namespace  = "http://www.TransformSqlLatLng.example"
      )
    ]
    public class TransformSqlLatLng :   WebService
    {
        [WebMethod]
        public void ConvertSqlLatLng(string datasourceid,int entnumber)
        {  
            try {
                    Transform  _TBasStore=new Transform_TBasStore(new Guid(datasourceid),entnumber);
                    Transform  _TPlaActualVisit=new Transform_TPlaActualVisit(new Guid(datasourceid),entnumber);
                    _TBasStore.ConvertLatLng();
                    _TPlaActualVisit.ConvertLatLng();
            } catch (Exception   ex) {
                
                Common.LogBll.Error(ex.Message.ToString());
            }
        }
    }
    
   /// <summary>
   /// 坐标转换基类
   /// </summary>
   public abstract class Transform
    {   
           /// <summary>
           /// 数据源ID
           /// </summary>
           protected Guid   datasourceid;
           
           /// <summary>
           /// 企业E号
           /// </summary>
           protected int EnterpriseNumber;
       
        /// <summary>
        /// 枚举原始对象
        /// </summary>
        /// <param name="enumer"></param>
        /// <returns></returns>
        protected abstract InitObj GetInitObj(Object   enumer);
        
        /// <summary>
        /// 获取数据源
        /// </summary>
        /// <param name="datasourceid"></param>
        /// <returns></returns>
        protected abstract List<DataSourceInfo> GetDatasource();
        
        /// <summary>
        /// 从数据库读取数据
        /// </summary>
        /// <returns></returns>
        protected abstract DataTable GetDBDataToDataTable(DataSourceInfo   _datasource);
        
        /// <summary>
        /// 并接更新语句
        /// </summary>
        /// <param name="InitObjects"></param>
        /// <returns></returns>
        protected abstract StringBuilder BuildUpdateSql(List<InitObj> InitObjects);
        
        /// <summary>
        /// 数据源BLL
        /// </summary>
        protected BLL.DataSource  mDataSource=new BLL.DataSource(ECConfiguration.DalProvider, ECConfiguration.ConnectionString);
        
        /// <summary>
        /// 转换逻辑
        /// </summary>
        /// <param name="datasourceid"></param>
        public void ConvertLatLng(){
           try {
                
                List<DataSourceInfo> _datasources=GetDatasource();
                _datasources.ForEach(_datasource=>{
                    //1.查询采集表数据进行坐标转换
                    DataTable  _upinfo=GetDBDataToDataTable(_datasource);
                    if(_upinfo!=null&&_upinfo.Rows.Count>0){
                      //定义分组规则，限定每次转换多少个坐标点
                       var _count=_upinfo.Rows.Count;
                       var _loopcount=_count/20;//定义查询次数
                       var _modcount=_count%20;//余数
                       
                       //装载源坐标数据
                       var _emumer=_upinfo.Rows.GetEnumerator();
                       List<InitObj> _current=new List<InitObj>();
                       while (_emumer.MoveNext()) {
                            var _currow=_emumer.Current;
                            var _initObj=GetInitObj(_currow);
                             _current.Add(_initObj);
                            
                        }
                        //分组请求，每组20个坐标点，请求结束后返回坐标点，然后并接语句更新采集表数据库（每组20条update语句）。
                        for (int i  = 0; i<_loopcount+1; i++) {
                            List<InitObj> _sPoints=new List<InitObj>();
                            if(i==_loopcount&&_modcount>0)
                                _sPoints=  _current.GetRange(i*20,_modcount);//源坐标对象,最后一次取
                            else if(i<_loopcount)
                                _sPoints=  _current.GetRange(i*20,(i+1)*20);//源坐标对象，整次取
                            else
                                return;
                             var _tPoints=new StringBuilder();//实际转换坐标
                             var _tfrom="";
                             List<string> _tlats=new List<string>();
                             List<string> _tlngs=new List<string>();
                             _sPoints.ForEach(c=>{
                                                  _tlats.Add(c._lat);
                                                  _tlngs.Add(c._lng);
                                                _tfrom=c._from;
                                             });
                             //执行算法
                             List<ResulteObj> _results=this.TransformLatLngs<ResulteObj>(_tfrom.ToString(),string.Join(",",_tlats),string.Join(",",_tlngs));
                             
                             //数据转换后进行对接
                             var _lastIndex=_sPoints.Count-1;
                             _results.ForEach(r=>{
                                                      var _index=_results.IndexOf(r);
                                                      if(_index<=_lastIndex&&r.error==0){
                                                          InitObj   _obj=_sPoints[_index];
                                                          _obj.lat=r.x;
                                                          _obj.lng=r.y;
                                                      }
                                              });
                             //并接SQL语句精心更新
                             StringBuilder  _updateSql=BuildUpdateSql(_sPoints);
                             //执行更新
                             _datasource.description=_updateSql.ToString();
                             Common.DataSourceInvoke.DataSourceInvoke.RequestDataToDataTable(_datasource,null,EnterpriseNumber);
                        }
                     }
              });
            } catch (Exception   ex) {
                Common.LogBll.Error(ex.Message.ToString());
            }
        }
        
        /// <summary>
        /// 转换算法
        /// </summary>
        /// <param name="from"></param>
        /// <param name="lats"></param>
        /// <param name="lngs"></param>
        /// <returns></returns>
        protected dynamic TransformLatLngs<T>(string from,string lats,string lngs)
        {   
            if(string.IsNullOrEmpty(from))
                return null;
            System.Net.Http.HttpClient  _client=new System.Net.Http.HttpClient();
            _client.Timeout=new TimeSpan(0,0,0,0,500);
            var _from=Convert.ToInt32(from);
            var _lat=lats;
            var _lng=lngs;
            var _url=string.Format("http://api.map.baidu.com/ag/coord/convert?from={0}&to=4&mode=1&x={1}&y={2}",_from,_lat,_lng);
            var _task=_client.GetStringAsync(_url);
            var str=string.Empty;
            var _resulte=string.Empty;
            List<ResulteObj> _arrys=new List<ResulteObj>();
            try {
                 _task.Wait();
              } catch (AggregateException   ex) {
                    var _error="坐标转换异常日志: ";
                    foreach (var _ex in ex.InnerExceptions) {
                        Common.LogBll.Error(string.Format("{0}{1}",_error,_ex.Message.ToString()));
                    }
                }
            finally{
                switch (_task.Status) {
                    case TaskStatus.Created:
                        break;
                    case TaskStatus.WaitingForActivation:
                        break;
                    case TaskStatus.WaitingToRun:
                        break;
                    case TaskStatus.Running:
                           break;
                    case TaskStatus.WaitingForChildrenToComplete:
                        break;
                    case TaskStatus.RanToCompletion:
                         str="请求成功！";
                         _arrys=   Newtonsoft.Json.JsonConvert.DeserializeObject<List<ResulteObj>>(_task.Result);
                         _arrys.ForEach(r=>{
                            if(r.error==0){
                               r.x=  Encoding.Default.GetString(Convert.FromBase64String(r.x));
                               r.y=  Encoding.Default.GetString(Convert.FromBase64String(r.y));
                            }
                         });
                         _resulte  = _arrys != null && _arrys.Count > 0 ? Ext.Net.JSON.Serialize(_arrys) : string.Empty;
                        break;
                    case TaskStatus.Canceled:
                         str="由于超时等原因，任务被取消！";
                        break;
                    case TaskStatus.Faulted:
                        str="由于给定的连接无响应或者不存在，任务执行失败！";
                        break;
                    default:
                        str="Invalid value for   TaskStatus！";
                        break;
                }
            }
            Type  type = typeof(T);
            switch (type.FullName)
            {
                case "System.String":
                    return _resulte;
                default:
                    return _arrys;
            }
        }
    }
   
   /// <summary>
   /// 转换基础信息表
   /// </summary>
   public class Transform_TBasStore:Transform
   {
           public Transform_TBasStore(Guid  datasourceid,int entnumber)
           {
              this.datasourceid=datasourceid;
              this.EnterpriseNumber=entnumber;
           }
           
           /// <summary>
           /// 从数据库查询数据
           /// </summary>
           /// <param name="_datasource"></param>
           /// <returns></returns>
           protected override DataTable GetDBDataToDataTable(DataSourceInfo   _datasource)
           {
               DataTable  _data=Common.DataSourceInvoke.DataSourceInvoke.RequestDataToDataTable(_datasource,null,EnterpriseNumber);
               return _data;
           }
           
           /// <summary>
           /// 枚举原始对象
           /// </summary>
           /// <param name="enumer"></param>
           /// <returns></returns>
        protected override InitObj GetInitObj(Object   enumer)
       {    
            var _currow=enumer as DataRow;
            InitObj  _initObj=new InitObj();
            _initObj._majorkey=_currow["StoreID"].ToString();
            if(_currow["Col3"]!=null&&_currow["Col4"]!=null&&_currow["Col5"]!=null)
            {
                _initObj._lat=_currow["Col3"].ToString();
                _initObj._lng=_currow["Col4"].ToString();
                _initObj._from=_currow["Col5"].ToString();
            }
               return _initObj;
           }
       
        /// <summary>
        /// 获取数据源
        /// </summary>
        /// <param name="datasourceid"></param>
        /// <returns></returns>
        protected override List<DataSourceInfo> GetDatasource()
        {   
            var _datasource=mDataSource.GetDataSourceByID(datasourceid);
            List<DataSourceInfo> lst=new List<DataSourceInfo>();
            _datasource.description="select StoreID,Col3,Col4,Col5 from TBasStore   where  col5=0";//查询GPS坐标SQL
            _datasource.datasourcetype=1;//默认全部是SQL
            lst.Add(_datasource);
            //_datasource.description="";//查询GOOGLE坐标SQL
            //lst.Add(_datasource);    
            return lst;
        }
        
        /// <summary>
        /// 并接更新语句
        /// </summary>
        /// <param name="InitObjects"></param>
        /// <returns></returns>
        protected override StringBuilder BuildUpdateSql(List<InitObj> InitObjects)
        {    
            StringBuilder  _updateSql=new StringBuilder();
             InitObjects.ForEach(s=>{
                      if(!string.IsNullOrEmpty(s.lat)&&!string.IsNullOrEmpty(s.lng)&&!string.IsNullOrEmpty(s._majorkey))
                     _updateSql.AppendFormat(@"UPDATE TBasStore  SET   Col3='{0}',Col4='{1}',Col5='64' WHERE   StoreID='{2}';",s.lat,s.lng,s._majorkey);
                  });
            return _updateSql;
        }
   }
   
   /// <summary>
   /// 转换信息采集表
   /// </summary>
   public class Transform_TPlaActualVisit:Transform
   {    
           public Transform_TPlaActualVisit(Guid  datasourceid,int entnumber)
           {
              this.datasourceid=datasourceid;
              this.EnterpriseNumber=entnumber;
           }
           
           /// <summary>
           /// 从数据库查询数据
           /// </summary>
           /// <param name="_datasource"></param>
           /// <returns></returns>
           protected override DataTable GetDBDataToDataTable(DataSourceInfo   _datasource)
           {
               DataTable  _data=Common.DataSourceInvoke.DataSourceInvoke.RequestDataToDataTable(_datasource,null,EnterpriseNumber);
               //筛选出真正需要转换的数据行
               DataTable  _resulte=_data.Clone();
               foreach (DataRow   _row in _data.Rows) {
                   if(_row["PositioningType"]!=null&&_row["LatLng"]!=null){
                       string[] _slatlng=_row["PositioningType"].ToString().Split(';');
                       string[] _pionts=_row["LatLng"].ToString().Split('|');
                       if(_slatlng.Length==6&&_pionts.Length==2&&_pionts[0]!="0"&&_pionts[1]!="0"&&_slatlng[4]=="0"){
                          _resulte.ImportRow(_row);
                       }
                   }
               }
               return _resulte;
           }
           
        /// <summary>
           /// 枚举原始对象
           /// </summary>
           /// <param name="enumer"></param>
           /// <returns></returns>
           protected override InitObj GetInitObj(Object   enumer)
           {
               var _currow=enumer as DataRow;
            InitObj  _initObj=new InitObj();
            _initObj._majorkey=_currow["ActualVisitID"].ToString();
            if(_currow["LatLng"]!=null&&_currow["PositioningType"]!=null)
            {
                string[] _slatlng=_currow["PositioningType"].ToString().Split(';');
                var _from=_slatlng[4];
                var _latlng=_currow["LatLng"].ToString().Split('|');
                _initObj._lat=_latlng[0];
                _initObj._lng=_latlng[1];
                _initObj._from=_from;
                _initObj._source="gis://{0};"+string.Format("{0};{1};64;{2}",_slatlng[2],_slatlng[3],_slatlng[5]);
            }
               return _initObj;
           }
    
        /// <summary>
        /// 获取数据源
        /// </summary>
        /// <param name="datasourceid"></param>
        /// <returns></returns>
        protected override List<DataSourceInfo> GetDatasource()
        {   
            var _datasource=mDataSource.GetDataSourceByID(datasourceid);
            List<DataSourceInfo> lst=new List<DataSourceInfo>();
            _datasource.description="select ActualVisitID,LatLng,PositioningType from  TPlaActualVisit";//查询GPS坐标SQL
            _datasource.datasourcetype=1;//默认全部是SQL
            lst.Add(_datasource);
            //_datasource.description="";//查询GOOGLE坐标SQL
            //lst.Add(_datasource);    
            return lst;
        }
        
        /// <summary>
        /// 并接更新语句
        /// </summary>
        /// <param name="InitObjects"></param>
        /// <returns></returns>
        protected override StringBuilder BuildUpdateSql(List<InitObj> InitObjects)
        {
             StringBuilder  _updateSql=new StringBuilder();
             InitObjects.ForEach(s=>{
                 if(!string.IsNullOrEmpty(s.lat)&&!string.IsNullOrEmpty(s.lng)&&
                                        s._from=="0"&&
                    !string.IsNullOrEmpty(s._majorkey)&&!string.IsNullOrEmpty(s._source))
                 {
                      var _latlng=string.Format("{0}|{1}",s.lat,s.lng);
                      var _slatlng=string.Format(s._source,string.Format("{0};{1}",s.lng,s.lat));
                      _updateSql.AppendFormat(@"UPDATE TPlaActualVisit  SET   LatLng='{0}',PositioningType='{1}' WHERE   ActualVisitID='{2}';",_latlng,_slatlng,s._majorkey);
                 }
             });
            return _updateSql;
        }
   }
   
   /// <summary>
   /// 转换结果类
   /// </summary>
   public class ResulteObj
   {   
          /// <summary>
          /// 结果编码
          /// </summary>
       public int error { get; set; }
       
       /// <summary>
       /// 转换后的纬度
       /// </summary>
       public string x { get; set; }
       
       /// <summary>
       /// 转换后的经度
       /// </summary>
       public string y { get; set; }
   } 
   
   /// <summary>
   /// 原始对象
   /// </summary>
   public class InitObj{
       /// <summary>
          /// 数据库主键
          /// </summary>
       public string _majorkey { get; set; }
       
       /// <summary>
       /// 采集原始数据
       /// </summary>
       public string _source{ get; set; }
       
       /// <summary>
          /// 数据库坐标定位来源
          /// </summary>
       public string _from { get; set; }
       
       /// <summary>
       /// 数据库源纬度
       /// </summary>
       public string _lng { get; set; }
       
       /// <summary>
       /// 数据库源经度
       /// </summary>
       public string _lat { get; set; }
       
       /// <summary>
       /// 转换后的纬度
       /// </summary>
       public string lng { get; set; }
       
       /// <summary>
       /// 转换后的经度
       /// </summary>
       public string lat { get; set; }
       
   }
   
}