使用PostGIS进行轨迹分析
来源：互联网 发布：手机怎么制作软件 编辑：程序博客网 时间：2024/05/13 23:36
从 sqlite 数据库中读取轨迹数据，存储到 postgis 数据库中，再利用 postgis 的特性进行轨迹分析。
track.db
Program.cs
class Program{ static void Main(string[] args) { List<Track> tracks = new List<Track>(); try { #region 从Sqlite中读取轨迹数据 //Sqlite数据库路径 string sqlitePath = Path.Combine(Environment.CurrentDirectory, "track.db"); //数据表名称 string sqliteTb = "T_1140204000334"; using (SqliteDB sqlite = new SqliteDB(sqlitePath)) { sqlite.Open(); DataTable dt = sqlite.ExecuteSQL("select * from " + sqliteTb + ";"); if (dt != null && dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { tracks.Add(new Track() { B = Convert.ToDouble(dt.Rows[i]["B"]) * 180 / Math.PI, L = Convert.ToDouble(dt.Rows[i]["L"]) * 180 / Math.PI, H = Convert.ToSingle(dt.Rows[i]["H"]), Azi = Convert.ToSingle(dt.Rows[i]["Azi"]), AntH = Convert.ToSingle(dt.Rows[i]["AntH"]), UTCTime = Convert.ToDateTime(dt.Rows[i]["UTCTime"]), Hdop = Convert.ToSingle(dt.Rows[i]["Hdop"]), SolType = Convert.ToInt32(Convert.ToByte(dt.Rows[i]["SolType"])) }); } } } Console.WriteLine("Sqlite中的轨迹记录数为：" + tracks.Count); Console.WriteLine("---------------------------------------------------------------------"); #endregion #region 向PostgreSQL中存入轨迹数据 using (PostgresqlDB postgresql = new PostgresqlDB("localhost", "5432", "postgres", "123456", "test")) { postgresql.Open(); string tableName = "t_1_1_20150519103523"; StringBuilder sql = new StringBuilder(); #region 写入数据 //删除数据表 if (postgresql.QueryTableIsExist(tableName)) { sql.Clear(); sql.AppendLine("drop table " + tableName + ";"); postgresql.ExecuteNonQuery(sql.ToString()); } postgresql.BeginTransaction(); try { //创建数据表 sql.Clear(); sql.AppendLine("create table " + tableName + "("); sql.AppendLine("id serial not null,"); sql.AppendLine("coord geometry(point, 4326) not null,"); sql.AppendLine("h real not null,"); sql.AppendLine("azi real not null,"); sql.AppendLine("ant_h real not null,"); sql.AppendLine("utc_time timestamp with time zone not null,"); sql.AppendLine("hdop real not null,"); sql.AppendLine("sol_type integer not null,"); sql.AppendLine("constraint " + tableName + "_pkey primary key (id)"); sql.AppendLine(");"); postgresql.ExecuteNonQuery(sql.ToString()); //插入记录 for (int i = 0; i < tracks.Count; i++) { sql.Clear(); sql.AppendLine("insert into " + tableName + "(coord,h,azi,ant_h,utc_time,hdop,sol_type) values("); sql.AppendLine(string.Format("ST_GeomFromText('point({0} {1})',4326),", tracks[i].B, tracks[i].L)); sql.AppendLine(tracks[i].H + ","); sql.AppendLine(tracks[i].Azi + ","); sql.AppendLine(tracks[i].AntH + ","); sql.AppendLine("'" + tracks[i].UTCTime.ToString("yyyy-MM-dd HH:mm:ss") + "',"); sql.AppendLine(tracks[i].Hdop + ","); sql.AppendLine(tracks[i].SolType.ToString()); sql.AppendLine(");"); postgresql.ExecuteNonQuery(sql.ToString()); } postgresql.Commit(); Console.WriteLine("写入postgresql成功！"); Console.WriteLine("---------------------------------------------------------------------"); } catch (Exception ex) { postgresql.Rollback(); Console.WriteLine(ex.Message); Console.WriteLine(ex.StackTrace); } postgresql.EndTransaction(); #endregion #region 读取数据 tracks.Clear(); DataTable dt = postgresql.ExecuteQuery("select st_x(coord) as b, st_y(coord) as l, h, azi, ant_h, utc_time, hdop, sol_type from " + tableName + ";"); if (dt != null && dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { tracks.Add(new Track() { B = Convert.ToDouble(dt.Rows[i]["b"]), L = Convert.ToDouble(dt.Rows[i]["l"]), H = Convert.ToSingle(dt.Rows[i]["h"]), Azi = Convert.ToSingle(dt.Rows[i]["azi"]), AntH = Convert.ToSingle(dt.Rows[i]["ant_h"]), UTCTime = Convert.ToDateTime(dt.Rows[i]["utc_time"]), Hdop = Convert.ToSingle(dt.Rows[i]["hdop"]), SolType = Convert.ToInt32(dt.Rows[i]["sol_type"]) }); } } Console.WriteLine("读取postgresql中的数据成功！记录数为：" + tracks.Count); Console.WriteLine("---------------------------------------------------------------------"); #endregion #region 轨迹分析 //轨迹总长度 double dist = 0; DataTable dtDistance = postgresql.ExecuteQuery("select st_length(st_makeline(st_transform(coord, 4504))) from " + tableName + ";"); if (dtDistance != null && dtDistance.Rows.Count > 0) { dist = Convert.ToDouble(dtDistance.Rows[0][0]); Console.WriteLine("轨迹总长度：" + dist + " 米"); Console.WriteLine("---------------------------------------------------------------------"); } //轨迹总时间 double seconds = 0; DataTable dtDateTime = postgresql.ExecuteQuery("select max(utc_time), min(utc_time) from " + tableName + ";"); if (dtDateTime != null && dtDateTime.Rows.Count > 0) { DateTime max = Convert.ToDateTime(dtDateTime.Rows[0][0]); DateTime min = Convert.ToDateTime(dtDateTime.Rows[0][1]); seconds = (max - min).TotalSeconds; Console.WriteLine("轨迹总时间：" + seconds + " 秒"); Console.WriteLine("---------------------------------------------------------------------"); } //轨迹平均速度 if (seconds != 0) { Console.WriteLine("轨迹平均速度：" + dist / seconds + " 米/秒"); Console.WriteLine("---------------------------------------------------------------------"); } //轨迹瞬时速度（计算相邻两条记录的距离及时间间隔） //extract(epoch from time1-time2):计算时间间隔的总秒数 DataTable dtDelta = postgresql.ExecuteQuery("select st_distance(st_transform(p1.coord,4504),st_transform(p2.coord,4504)) as delta_d, extract(epoch from p1.utc_time-p2.utc_time) as delta_t from " + tableName + " as p1, " + tableName + " as p2 where p1.id = p2.id+1;"); if (dtDelta != null && dtDelta.Rows.Count > 0) { double delta_d = Convert.ToDouble(dtDelta.Rows[dtDelta.Rows.Count - 1]["delta_d"]); double delta_t = Convert.ToDouble(dtDelta.Rows[dtDelta.Rows.Count - 1]["delta_t"]); if (delta_t != 0) { Console.WriteLine("最后一个点的分析结果为：\r\n 瞬时位移：" + delta_d + " 米" + "\r\n 时间间隔：" + delta_t + " 秒" + "\r\n 瞬时速度：" + delta_d / delta_t + " 米/秒"); Console.WriteLine("---------------------------------------------------------------------"); } } #endregion } #endregion } catch (Exception ex) { Console.WriteLine(ex.Message); Console.WriteLine(ex.StackTrace); } Console.Read(); }}运行结果为：
Sqlite中的轨迹记录数为：1521---------------------------------------------------------------------写入postgresql成功！---------------------------------------------------------------------读取postgresql中的数据成功！记录数为：1521---------------------------------------------------------------------轨迹总长度：16775.455657666 米---------------------------------------------------------------------轨迹总时间：353325 秒---------------------------------------------------------------------轨迹平均速度：0.0474788244751037 米/秒---------------------------------------------------------------------最后一个点的分析结果为： 瞬时位移：5.32844146492802 米 时间间隔：1 秒 瞬时速度：5.32844146492802 米/秒---------------------------------------------------------------------Track.cs
public class Track{ public double B; public double L; public float H; public float Azi; public float AntH; public DateTime UTCTime; public float Hdop; public int SolType;}SqliteDB.cs
/// <summary>/// Sqlite数据库操作类/// </summary>public class SqliteDB : IDisposable{ #region 字段 /// <summary> /// 数据库连接字符串构造器 /// </summary> private SQLiteConnectionStringBuilder _connStr = null; /// <summary> /// 数据库连接器 /// </summary> private SQLiteConnection _conn = null; /// <summary> /// 通过连接器创建NpgsqlCommand对象，用于执行SQL语句 /// </summary> private SQLiteCommand _comm = null; /// <summary> /// 事务管理对象 /// </summary> private SQLiteTransaction _trans = null; #endregion #region 构造函数 public SqliteDB(string filePath) { _connStr = new SQLiteConnectionStringBuilder(); _connStr.DataSource = filePath; _conn = new SQLiteConnection(_connStr.ToString()); _comm = _conn.CreateCommand(); } ~SqliteDB() { } #endregion #region 静态方法 /// <summary> /// 创建数据库 /// </summary> /// <param name="filePath">db文件路径+文件名</param> public static void CreateDB(string filePath) { try { SQLiteConnection.CreateFile(filePath); } catch { throw; } } #endregion #region 公共方法 #region 数据库操作：连接数据库、断开数据库、加密数据库 /// <summary> /// 打开数据库连接 /// </summary> /// <returns>是否连接成功</returns> public bool Open() { try { _conn.Open(); return true; } catch { Close(); return false; throw; } } /// <summary> /// 打开数据库连接 /// </summary> /// <param name="password">密码</param> /// <returns>是否连接成功</returns> public bool Open(string password) { try { _conn.SetPassword(password); _conn.Open(); return true; } catch { Close(); return false; throw; } } /// <summary> /// 关闭数据库连接 /// </summary> public void Close() { if (_conn != null) { _comm.Dispose(); _conn.Close(); _conn.Dispose(); } } /// <summary> /// 给sqlite数据库加密 /// </summary> /// <param name="password">密码</param> public void Encrypt(string password) { try { if (_conn != null) { _conn.ChangePassword(password); } } catch { throw; } } #endregion #region 查询 /// <summary> /// 执行sql语句 /// </summary> /// <param name="sql"></param> /// <returns></returns> public DataTable ExecuteSQL(string sql) { try { _comm.Transaction = _trans; _comm.CommandText = sql; DataTable dt = new DataTable(); using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(_comm)) { adapter.Fill(dt); } return dt; } catch { throw; } } #endregion #region 事务管理 /// <summary> /// 开始执行事务 /// </summary> public void BeginTransaction() { if (_conn != null) { _trans = _conn.BeginTransaction(); } } /// <summary> /// 事务结束，保存并提交数据库数据， /// </summary> public void Commit() { if (_trans != null) { _trans.Commit(); } } /// <summary> /// 回滚事务 /// </summary> public void Rollback() { if (_trans != null) { _trans.Rollback(); } } /// <summary> /// 结束事务，并释放资源 /// </summary> public void EndTransaction() { if (_trans != null) { _trans.Dispose(); _trans = null; } } #endregion #endregion #region IDisposable 成员 public void Dispose() { this.Close(); } #endregion}PostgresqlDB.cs
/// <summary>/// 数据库管理类/// 直接操作数据库/// </summary>public class PostgresqlDB : IDisposable{ #region 字段 #region 连接配置 /// <summary> /// 服务器地址 /// </summary> private string server; /// <summary> /// 端口 /// </summary> private string port; /// <summary> /// 用户名 /// </summary> private string username; /// <summary> /// 密码 /// </summary> private string password; /// <summary> /// 数据库名称 /// </summary> private string db; #endregion #region 数据库操作相关变量 /// <summary> /// 数据库连接器 /// </summary> private NpgsqlConnection _conn; /// <summary> /// 通过连接器创建NpgsqlCommand对象，用于执行SQL语句 /// </summary> private NpgsqlCommand _comm; /// <summary> /// 将数据表中获取的记录生成DataSet /// </summary> private NpgsqlDataAdapter _adapter; /// <summary> /// 创建一个事务对象 /// </summary> private NpgsqlTransaction _trans; #endregion #endregion #region 构造函数 private PostgresqlDB() { } /// <summary> /// 数据库管理类 /// </summary> /// <param name="dbConfig">数据库配置</param> public PostgresqlDB(string server, string port, string username, string password, string db) { this.server = server; this.port = port; this.username = username; this.password = password; this.db = db; } ~PostgresqlDB() { } #endregion #region 公共方法 #region 数据库操作：连接数据库、断开数据库、数据库连接状态 /// <summary> /// 打开数据库连接 /// </summary> /// <returns></returns> public bool Open() { try { //构造SQL语句 string sql = String.Format( "server={0};port={1};userid={2};password={3};database={4};pooling=true;maxpoolsize=40;timeout=5;", server, port, username, password, db); //初始化数据库连接参数 _conn = new NpgsqlConnection(sql); //打开数据库连接 _conn.Open(); //初始化 _comm = _conn.CreateCommand(); _adapter = new NpgsqlDataAdapter(_comm); _trans = null; return true; } catch (Exception ex) { if (_conn != null) _conn.Close(); _conn = null; return false; } } /// <summary> /// 关闭数据库连接 /// </summary> public void Close() { if (_conn != null) { _adapter.Dispose(); _adapter = null; _comm.Dispose(); _comm = null; _conn.Close(); _conn.Dispose(); _conn = null; } } /// <summary> /// 获取数据库的连接状态 /// </summary> /// <returns>true表示已连接，false表示未连接</returns> public bool GetConnStatus() { return (_conn != null && _conn.State == ConnectionState.Open); } #endregion #region 数据表操作：查询数据表或视图是否存在 /// <summary> /// 查询指定名称的数据表是否存在 /// </summary> /// <param name="tableName">数据表名</param> /// <returns>true:存在，false:不存在</returns> public bool QueryTableIsExist(string tableName) { //构建SQL语句 string sql = String.Format("select count(*) from pg_tables where tablename = '{0}';", tableName.ToLower()); //执行SQL语句 _comm.Transaction = _trans; _comm.CommandText = sql; NpgsqlDataReader readerTemp = _comm.ExecuteReader(); //获取查询结果 long value = -1; if (readerTemp.HasRows == true) { readerTemp.Read(); value = readerTemp.GetInt64(0); } //释放资源 readerTemp.Close(); //value=1表示数据表存在；value=0表示数据表不存在 if (value > 0) { return true; } return false; } /// <summary> /// 查询指定名称的视图是否存在 /// </summary> /// <param name="viewName">视图名</param> /// <returns>true:存在，false:不存在</returns> public bool QueryViewIsExist(string viewName) { //构建SQL语句 string sql = String.Format("select count(*) from pg_views where viewname = '{0}';", viewName.ToLower()); //执行SQL语句 _comm.Transaction = _trans; _comm.CommandText = sql; NpgsqlDataReader readerTemp = _comm.ExecuteReader(); //获取查询结果 long value = -1; if (readerTemp.HasRows == true) { readerTemp.Read(); value = readerTemp.GetInt64(0); } //释放资源 readerTemp.Close(); //value=1表示数据表存在；value=0表示数据表不存在 if (value == 0) { return false; } return true; } #endregion #region 执行sql语句 /// <summary> /// 执行sql语句 /// </summary> /// <param name="sql"></param> public void ExecuteNonQuery(string sql) { try { _comm.Transaction = _trans; _comm.CommandText = sql; _comm.ExecuteNonQuery(); } catch { throw; } } /// <summary> /// 执行sql语句 /// </summary> /// <param name="sql"></param> /// <returns></returns> public DataTable ExecuteQuery(string sql) { try { _comm.Transaction = _trans; _comm.CommandText = sql; DataTable dt = new DataTable(); using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(_comm)) { adapter.Fill(dt); } return dt; } catch (Exception ex) { throw ex; } } #endregion #region 事务管理 /// <summary> /// 开始执行事务 /// </summary> public void BeginTransaction() { if (_conn != null) { _trans = _conn.BeginTransaction(); } } /// <summary> /// 事务结束，保存并提交数据库数据， /// </summary> public void Commit() { if (_trans != null) { _trans.Commit(); } } /// <summary> /// 回滚事务 /// </summary> public void Rollback() { if (_trans != null) { _trans.Rollback(); } } /// <summary> /// 结束事务，并释放资源 /// </summary> public void EndTransaction() { if (_trans != null) { _trans.Dispose(); _trans = null; } } #endregion #endregion public void Dispose() { this.Close(); }} 0 0
- 使用PostGIS进行轨迹分析
- 作业：使用Douglas-Peucker算法进行轨迹压缩
- PostgreSQL+PostGIS的使用
- PostgreSQL+PostGIS的使用
- postGIS简单使用
- PostgreSQL+PostGIS的使用
- greenplum使用postgis例子
- PostGres+postGis 初级使用
- 航空轨迹数据处理，进行轨迹聚类
- postgis
- PostGis
- postgis
- Postgis
- PostGIS
- postGIS
- js实现回放拖拽轨迹从过程上进行分析
- js实现回放拖拽轨迹从过程上进行分析
- js实现回放拖拽轨迹从过程上进行分析
- Android 布局属性大全
- 数据库调优的方法有那些
- 黑马程序员——java基础（5）
- eclipse中如何对libs中项目添加依赖
- Pixel gap / lines that appear at the edges of sprites[NGUI]
- 使用PostGIS进行轨迹分析
- Openstack Oslo.config 学习(一)
- WebBrowser
- 木瓜妮子多媒体开发教程---第一天---Android环境搭建和Helloworld项目测试
- android初学------系统设置之恢复出厂设置
- SQL Server 2005 性能监视器计数器要求 (错误)
- Openstack Oslo.config 学习(二)
- sigaction 用法实例
- 关于TCS中的图灵机模型和神谕机
