记一次MySQL的插入数据 结合百度地图的坐标转换
using MySql.Data.MySqlClient;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
namespace ConsoleApplication1
{
class Program
{
static double x_PI = 3.14159265358979324 * 3000.0 / 180.0;
static double PI = 3.1415926535897932384626;
static double a = 6378245.0;
static double ee = 0.00669342162296594323;
static void Main(string[] args)
{
double lat= 13529822.18, lon= 3644519.8;
string connstr = "Server=10.14.3.137;Uid=root;Pwd=20190416;Database=test";
using (MySqlConnection conn = new MySqlConnection(connstr)) {
try{
conn.Open();
Console.Write("数据库连接成功" + "\n");
MySqlDataAdapter adapter = new MySqlDataAdapter("select id,gcj02_lat,gcj02_lon,wgs84_lat,wgs84_lon from steps", conn);
DataSet ds = new DataSet();
adapter.Fill(ds, "steps_table");
Console.Write("数据获取成功" + "\n");
DataTable mytable = ds.Tables["steps_table"];
Point point = null;
foreach (DataRow myRow in mytable.Rows)
{
lat = System.Convert.ToDouble(myRow["gcj02_lat"].ToString());
lon = System.Convert.ToDouble(myRow["gcj02_lon"].ToString());
point = new Point(lat,lon);
//获取数据接口
//string Url = "http://api.map.baidu.com/geoconv/v1/?coords=" + lat + "," + lon + "&from=6&to=5&ak=8dho8FFgHmpa0pP0FgMTk17TrGVl5jaf";
//string result = HttpGet(Url);
//JObject jo = (JObject)JsonConvert.DeserializeObject(result);
//string bd_lat = jo["result"][0]["x"].ToString();
//string bd_lon = jo["result"][0]["y"].ToString();
point = gcj02_To_Gps84(point);
lat = point.lat;
lon= point.lng;
myRow["wgs84_lat"] = Convert.ToString(lat);
myRow["wgs84_lon"] = Convert.ToString(lon);
}
MySqlCommandBuilder mySqlCommandBuilder = new MySqlCommandBuilder(adapter);
//更新数据
adapter.Update(ds, "steps_table");
Console.Write(mytable.TableName + "\n");
Console.Write("数据更新成功" + "\n");
}
catch (MySqlException ex)
{
Console.Write(ex.ToString());
}
finally
{
conn.Close();
}
}
Console.ReadKey();
}
//获取坐标转换数据
public static string HttpGet(string Url)
{
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(Url);
request.Method = "GET";
request.ContentType = "text/html;charset=UTF-8";
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
Stream myResponseStream = response.GetResponseStream();
StreamReader myStreamReader = new StreamReader(myResponseStream, Encoding.GetEncoding("utf-8"));
string retString = myStreamReader.ReadToEnd();
myStreamReader.Close();
myResponseStream.Close();
return retString;
}
/**
* 百度坐标系 (BD-09) 与 火星坐标系 (GCJ-02)的转换
* 即 百度 转 谷歌、高德
* @param bd_lon
* @param bd_lat
* @returns {*[]}
*/
public static Point bd09togcj02( double bd_lat,double bd_lon)
{
double x = bd_lon - 0.0065;
double y = bd_lat - 0.006;
double z = Math.Sqrt(x * x + y * y) - 0.00002 * Math.Sin(y * x_PI);
double theta = Math.Atan2(y, x) - 0.000003 * Math.Cos(x * x_PI);
double gg_lng = z * Math.Cos(theta);
double gg_lat = z * Math.Sin(theta);
Point point = new Point(gg_lat, gg_lng);
return point;
}
static double transformLat(double x, double y)
{
double ret = -100.0 + 2.0 * x + 3.0 * y + 0.2 * y * y + 0.1 * x * y
+ 0.2 * Math.Sqrt(Math.Abs(x));
ret += (20.0 * Math.Sin(6.0 * x * PI) + 20.0 * Math.Sin(2.0 * x * PI)) * 2.0 / 3.0;
ret += (20.0 * Math.Sin(y * PI) + 40.0 * Math.Sin(y / 3.0 * PI)) * 2.0 / 3.0;
ret += (160.0 * Math.Sin(y / 12.0 * PI) + 320 * Math.Sin(y * PI / 30.0)) * 2.0 / 3.0;
return ret;
}
static double transformLon(double x, double y)
{
double ret = 300.0 + x + 2.0 * y + 0.1 * x * x + 0.1 * x * y + 0.1
* Math.Sqrt(Math.Abs(x));
ret += (20.0 * Math.Sin(6.0 * x * PI) + 20.0 * Math.Sin(2.0 * x * PI)) * 2.0 / 3.0;
ret += (20.0 * Math.Sin(x * PI) + 40.0 * Math.Sin(x / 3.0 * PI)) * 2.0 / 3.0;
ret += (150.0 * Math.Sin(x / 12.0 * PI) + 300.0 * Math.Sin(x / 30.0
* PI)) * 2.0 / 3.0;
return ret;
}
static Point transform(Point Gpoint)
{
double dLat = transformLat(Gpoint.lng - 105.0, Gpoint.lat - 35.0);
double dLon = transformLon(Gpoint.lng - 105.0, Gpoint.lat - 35.0);
double radLat = Gpoint.lat / 180.0 * PI;
double magic = Math.Sin(radLat);
magic = 1 - ee * magic * magic;
double sqrtMagic = Math.Sqrt(magic);
dLat = (dLat * 180.0) / ((a * (1 - ee)) / (magic * sqrtMagic) * PI);
dLon = (dLon * 180.0) / (a / sqrtMagic * Math.Cos(radLat) * PI);
double mgLat = Gpoint.lat + dLat;
double mgLon = Gpoint.lng + dLon;
return new Point(mgLat, mgLon);
}
/**
** 火星坐标系 (GCJ-02) to 84 * * @param lon * @param lat * @return
**/
public static Point gcj02_To_Gps84(Point Gpoint)
{
Point gps = transform(Gpoint);
double lontitude = Gpoint.lng * 2 - gps.lng;
double latitude = Gpoint.lat * 2 - gps.lat;
return new Point(latitude, lontitude);
}
public class Point
{
public double lat;
public double lng;
public Point(double lat,double lng) {
this.lat = lat;
this.lng = lng;
}
}
}
}