使用PostGIS进行轨迹分析

最新推荐文章于 2023-12-21 09:18:52 发布

doris_d

最新推荐文章于 2023-12-21 09:18:52 发布

阅读量6.2k

点赞数 2

CC 4.0 BY-SA版权

分类专栏： Database 文章标签： postgis

本文链接：https://blog.youkuaiyun.com/doris_d/article/details/45846365

从 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].Sol