坐标转换服务笔录

场景：

移动终端设备读取数据库坐标数据时，由于终端设备某些情况下无法正确识别坐标类型（例如只读取经纬度，无定位来源），这样使得定位会存在偏差。

目的：

为了使移动终端或者后台直接获取到经过修正后的坐标（目前只用百度地图），需要有一个自动坐标转换修正的过程。同时需要保存原始坐标或修正后的坐标。

设计方案一：

新建一个webservice，用于把GPS和其他坐标转换为百度地图坐标，通过数据调度去定时执行这个job.

url约束

lat:经度字段

lng:纬度字段

tp:类型

fn:函数或者存贮过程

dsid:数据源id

设计方案二：

直接在SQL或者PostgreSQL存贮过程中或者函数内（或者触发器）调用RESTFUL API:

参考：

http://stackoverflow.com/questions/3325292/how-can-i-send-some-http-request-from-postgresql-function-or-trigger；

http://p2p.wrox.com/sql-server-2000/35035-how-send-http-post-request-via-stored-procedure.html；

http://www.google.ru/search?q=sql+clr+wcf；

http://www.google.ru/search?q=sql%20clr%20webrequest；

http://stackoverflow.com/questions/5983599/can-sql-server-send-a-web-request；

====================================================================

测试数据：

gis://23.059628;113.211929;7;2013-05-06 10:12:56;0;
NULL
gis://23.215532;113.220751;6;2013-05-06 11:07:49;0;null
NULL
NULL
gis://23.179174;113.261121;0;2013-05-06 12:38:19;64;null
gis://23.198915;113.21605;6;2013-05-06 12:57:56;0;null
gis://23.077585;113.205882;8;2013-05-06 12:25:12;0;广东省广州市荔湾区龙溪中路129号
gis://23.076882;113.19145;7;2013-05-06 13:38:02;0;广东省广州市荔湾区西埔路2号

gis://23.208871;113.237282;7;2013-05-15 12:09:17;0;广东省广州市白云区石槎路1577号

gis://23.06533;113.249085;8;2013-05-15 12:42:17;0;广东省广州市荔湾区环翠北路5号

gis://23.160435;113.228923;8;2013-05-15 13:38:08;0;null

gis://23.094145;113.22796;7;2013-05-15 15:23:06;0;广东省广州市荔湾区花地大道北174号

gis://23.140418;113.235926;0;2013-05-15 17:23:25;64;null

gis://23.15645;113.256197;0;2013-05-15 17:21:44;64;null

gis://23.178485;113.261878;0;2013-05-15 18:09:25;64;null

gis://23.143816;113.237154;7;2013-05-15 18:13:39;0;广东省广州市荔湾区西湾路060号

gis://23.231603;113.283009;0;2013-05-16 12:24:02;64;未知

gis://23.228754;113.291519;0;2013-05-16 13:17:34;64;null

gis://23.209869;113.255476;0;2013-05-16 14:35:45;64;null

gis://23.205369;113.265368;0;2013-05-16 16:33:12;64;null

gis://23.077049;113.289786;0;2013-05-16 17:53:35;64;未知

gis://23.234099;113.224781;0;2013-05-17 14:00:58;64;null

gis://23.105285;113.27643;6;2013-05-17 12:30:54;0;广东省广州市海珠区万寿路63号-1

gis://23.128599;113.301339;0;2013-05-17 14:44:53;64;未知

gis://23.153274;113.239624;0;2013-05-17 15:34:28;64;未知

gis://23.044736;113.367783;8;2013-05-17 16:52:00;0;null

gis://23.156407;113.256188;0;2013-05-17 17:00:14;64;未知

解析URL:

http://api.map.baidu.com/ag/coord/convert?from=0&to=4&x=113.211929&y=23.215532

结果：

{"error":0,"x":"MTEzLjIyMzg2MzM2NDQy","y":"MjMuMjE4NTgxODkwOTMx"}

多个坐标测试：

http://api.map.baidu.com/ag/coord/convert?from=2&to=4&mode=1&x=116.3786889372559,116.38632786853031,116.39534009082035,116.40624058825688,116.41413701159672&y=39.90762965106183,39.90795884517671,39.907432133833574,39.90789300648029,39.90795884517671

 

测试逻辑代码：

 public class GetBaiduLngLat
   {

       public static string ConvertGPSToBaidu(int type, double lng, double lat)
       {
           string url = "http://api.map.baidu.com/ag/coord/convert?from={0}&to=4&x={1}&y={2}";
           System.Net.WebClient wCient = new System.Net.WebClient();
           wCient.Headers.Add("Accept-Language", "zh-cn");
           byte[] responseData = wCient.DownloadData(string.Format(url, type, lng, lat));
           string returnStr = System.Text.Encoding.UTF8.GetString(responseData);//返回接受的数据

           JavaScriptSerializer Serializer = new JavaScriptSerializer();
           ResulteObj objs = Serializer.Deserialize<ResulteObj>(returnStr);
           string xlng = Encoding.Default.GetString(Convert.FromBase64String(objs.x));
           string ylat = Encoding.Default.GetString(Convert.FromBase64String(objs.y));

           if (!string.IsNullOrEmpty(xlng) && !string.IsNullOrEmpty(ylat))
               return string.Format("{0},{1}", xlng, ylat);
           else
               return string.Empty;
       }
   }

protected void Page_Load(object sender, EventArgs e)
       {
           //string URL = "http://api.map.baidu.com/ag/coord/convert?from=0&to=4&x=113.31499&y=23.13761";
           //////经度
           //byte[] outputb = Convert.FromBase64String("MTEzLjMyNjkxNTc4NzIx");
           //string orgStr = Encoding.Default.GetString(outputb);

           //////纬度
           //byte[] outputb2 = Convert.FromBase64String("MjMuMTQwODI5ODExNjg1");
           //string orgStr2 = Encoding.Default.GetString(outputb2);

           string lnglat = GetBaiduLngLat.ConvertGPSToBaidu(0, 113.31499, 23.13761);
       }
public class ResulteObj
   {
       public string error { get; set; }
       public string x { get; set; }
       public string y { get; set; }
   }
解决方案二处理：

PostGreSQL触发器中请求url解析坐标：

http://stackoverflow.com/questions/3325292/how-can-i-send-some-http-request-from-postgresql-function-or-trigger;

http://stackoverflow.com/questions/12002662/psql-trigger-send-email/12003516#12003516;

http://stackoverflow.com/questions/12666148/does-plv8-support-making-http-calls-to-other-servers/;

https://github.com/pramsey/pgsql-http;

 

MSSQL触发器中请求url解析坐标：

http://msdn.microsoft.com/en-us/library/ms166043%28v=sql.90%29.aspx;

sql clr webrequest

 

==================================================================

最终解决方案：
PG数据库：
http://blog.opengeo.org/2012/04/20/http-for-postgresql/
这个demo是数据库里面根据坐标请求谷歌REST API进行的地理信息请求（返回xml）。
在postgresql数据库上面组要安装这个插件：
https://github.com/pramsey/pgsql-http

关联的curl源码：

https://github.com/bagder/curl

https://github.com/bagder/curl/blob/master/include/curl/curl.h

http://curl.haxx.se/libcurl/c/symbols-in-versions.html

用法：

1.CREATE EXTENSION http（首次使用需要使用postgres帐号来执行这个命令）

2.例子：

百度：

select * from http_get('http://api.map.baidu.com/ag/coord/convert?from=0&to=4&x=113.211929&y=23.215532');

返回：

{"error":0,"x":"MTEzLjIyMzg2MzM2NDQy","y":"MjMuMjE4NTgxODkwOTMx"}

//谷歌地图API暂时不测试

--select * from http_get('http://maps.googleapis.com/maps/api/geocode/xml?sensor=false&address=1234+main+st+chicago');

3.正则解析json

select regexp_matches('{"error":0,"x":"MTEzLjIyMzg2MzM2NDQy","y":"MjMuMjE4NTgxODkwOTMx"}','"y":"(.+)"')

union

select regexp_matches('{"error":0,"x":"MTEzLjIyMzg2MzM2NDQy","y":"MjMuMjE4NTgxODkwOTMx"}','"x":"(.+)",')

或者连同第二步骤：

select regexp_matches((select content from http_get('http://api.map.baidu.com/ag/coord/convert?from=0&to=4&x=113.211929&y=23.215532')),'"y":"(.+)"'),'')

union

select regexp_matches((select content from http_get('http://api.map.baidu.com/ag/coord/convert?from=0&to=4&x=113.211929&y=23.215532')),'"x":"(.+)",')

4.

PG数据库反编码base64语句：

--select decode('{"error":0,"x":"MTEzLjIyMzg2MzM2NDQy","y":"MjMuMjE4NTgxODkwOTMx"}', 'base64')

select decode('MTEzLjIyMzg2MzM2NDQy', 'base64')

select decode('MjMuMjE4NTgxODkwOTMx', 'base64')

或者连同2、3步骤：

--经度坐标数据库转换

select decode(array_to_string(regexp_matches((select content from http_get('http://api.map.baidu.com/ag/coord/convert?from=0&to=4&x=113.211929&y=23.215532')),'"x":"(.+)",'),''),'base64')

union

--纬度坐标数据库转换

select decode(array_to_string(regexp_matches((select content from http_get('http://api.map.baidu.com/ag/coord/convert?from=0&to=4&x=113.211929&y=23.215532')),'"y":"(.+)"'),''), 'base64')

 

====测试语句（一个坐标）=========================================

--测试环境(PostgreSQL):120.132.147.36

--CREATE EXTENSION http（每个数据库首次使用需要使用postgres帐号来执行这个命令）

--测试过程

--原始坐标：

--x=113.211929&y=23.215532

--原始定义来源：GPS

--经度坐标数据库转换(正则表达原则：从源字符串中抽取匹配表达式的子串)

select decode(array_to_string(regexp_matches((select content from http_get('http://api.map.baidu.com/ag/coord/convert?from=0&to=4&x=113.211929&y=23.215532')),'"x":"(.+)",'),''),'base64')

union

--纬度坐标数据库转换

select decode(array_to_string(regexp_matches((select content from http_get('http://api.map.baidu.com/ag/coord/convert?from=0&to=4&x=113.211929&y=23.215532')),'"y":"(.+)"'),''),'base64')

--转换后坐标

--x=113.22386336442&y=23.218581890931

====测试语句（多个坐标）=========================================

--多个坐标测试过程（注意API参数,批量请求时需要加&mode=1这个参数）

--原始坐标：

--//注意：百度和谷歌的经纬度坐标顺序是相反的。

--var points = [new BMap.Point(116.3786889372559,39.90762965106183),

--              new BMap.Point(116.38632786853032,39.90795884517671),

--              new BMap.Point(116.39534009082035,39.907432133833574),

--              new BMap.Point(116.40624058825688,39.90789300648029),

--              new BMap.Point(116.41413701159672,39.90795884517671)

--原始定义来源：Google

--经度坐标数据库转换(正则表达原则：从源字符串中替换匹配表达式的子串)

select decode(

array_to_string(

  regexp_matches((

    select content from

        http_get(

        'http://api.map.baidu.com/ag/coord/convert?from=2&to=4&mode=1&x=116.3786889372559,116.38632786853031,116.39534009082035,116.40624058825688,116.41413701159672&y=39.90762965106183,39.90795884517671,39.907432133833574,39.90789300648029,39.90795884517671'

           )),'"x":"(.+)",'),''),'base64')

--测试结果

select  regexp_matches(        

(select content from

        http_get(

        'http://api.map.baidu.com/ag/coord/convert?from=2&to=4&mode=1&x=116.3786889372559,116.38632786853031,116.39534009082035,116.40624058825688,116.41413701159672&y=39.90762965106183,39.90795884517671,39.907432133833574,39.90789300648029,39.90795884517671'

           )),'{.+}')

--结果

{"{\"error\":0,\"x\":\"MTE2LjM4NTEyODk3MDM2\",\"y\":\"MzkuOTEzNzgwNTQ0Njk2\"},

  {\"error\":0,\"x\":\"MTE2LjM5MjcyOTg3NjM=\",\"y\":\"MzkuOTE0MjE5NjY3MzMy\"},

  {\"error\":0,\"x\":\"MTE2LjQwMTcxNjczNjI4\",\"y\":\"MzkuOTEzNzY4NTE2MjE0\"},

  {\"error\":0,\"x\":\"MTE2LjQxMjYxOTYwMDc1\",\"y\":\"MzkuOTE0MjIxMTQyNDM3\"},

  {\"error\":0,\"x\":\"MTE2LjQyMDU0MDk1ODI1\",\"y\":\"MzkuOTE0MjE0MDc0NDc2\"}"

 

参考：读取数组值

http://www.cnblogs.com/stephen-liu74/archive/2012/05/07/2295273.html

问题：若数据库无法连接外网，需要把这接口包装或者通过反向代理来请求！同时需要注意的是请求可能存在很开的情况，应该需要一种机制来确保性能问题。

http://api.map.baidu.com/ag/coord/convert?from=2&to=4&mode=1&x=116.3786889372559&y=39.90762965106183

封装成接口调用（相当于代理，做了超时处理，保证连接使用不影响性能）：

http://localhost:8080/Services/Transform.asmx/TransformLatLngsToJson?from=2&lats=116.3786889372559,116.38632786853032,116.39534009082035&lngs=39.90762965106183,39.90795884517671,39.907432133833574

 

http://localhost:8080/Services/Transform.asmx/TransformLatLngsToXml?from=2&lats=116.3786889372559,116.38632786853032,116.39534009082035&lngs=39.90762965106183,39.90795884517671,39.907432133833574

===========================

 

===========================

MSSQL数据库：

参考：

http://www.cnblogs.com/coderlee/archive/2008/01/31/1059523.html

http://msdn.microsoft.com/zh-cn/library/ms166043(v=sql.90).aspx

来自为知笔记(Wiz)Time=2013-05-23 13:45:18