C#利用Postgresql+Postgis进行空间地理信息分析（道路偏移，进出电子围栏等）
·
前言
GIS空间分析指的是在GIS(地理信息系统）里实现分析空间数据，即从空间数据中获取有关地理对象的空间位置、分布、形态、形成和演变等信息并进行分析。
将GIS空间分析利用到系统中，我们可以利用GIS空间分析处理车辆进出电子围栏，车辆预计到达目的地，车辆是否进入某些区域，车辆是否进入道路，车辆是否偏移了规定道路等等相关应用型分析。
准备
针对复杂的GIS空间分析，我们可以利用Postgresql+Postgis来简单实现，我们需要先安装对于的控件分析需要的组件。
1：安装PostgreSQL，我这里使用的是Windows 服务器
2：给PostgreSQL 安装PostGIS组件
安装参考：https://wenku.baidu.com/view/ac2b40ab284ac850ad0242f3.html
实现
首先我们可以与操作MS-SQL，MySQL等数据库一样定义一个DBHelper类，给这个类取名PostgreHelper，封装PostgresSQL数据库操作方法。
public class PostgreHelper : IDBHelper
{
/// <summary>
/// 读取数据返回DataSet
/// </summary>
/// <param name="sqrstr"></param>
/// <returns></returns>
public DataSet ExecuteQuery(string sqrstr)
{
try
{
using (NpgsqlConnection conn = new NpgsqlConnection(Config.PostgreConnectionString))
{
using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(sqrstr, conn))
{
DataSet ds = new DataSet();
da.Fill(ds, "ds");
return ds;
}
}
}
catch (Exception ex)
{
Log.Instance.Error("ExecuteQuery:"+ex.Message);
return null;
}
}
/// <summary>
/// 判断增删改执行状态
/// </summary>
/// <param name="sqrstr"></param>
/// <returns></returns>
public int ExecuteNonQuery(string sqrstr)
{
try
{
using (NpgsqlConnection conn = new NpgsqlConnection(Config.PostgreConnectionString))
{
using (NpgsqlCommand SqlCommand = new NpgsqlCommand(sqrstr, conn))
{
int r = SqlCommand.ExecuteNonQuery(); //执行查询并返回受影响的行数
conn.Close();
return r; //r如果是>0操作成功！
}
}
}
catch (Exception ex)
{
Log.Instance.Error("ExecuteNonQuery:"+ex.Message);
return -1;
}
}
/// <summary>
/// 读取数据返回数据流
/// </summary>
/// <param name="cmdText"></param>
/// <returns></returns>
public DbDataReader GetReader(string cmdText)
{
try
{
using (NpgsqlConnection conn = new NpgsqlConnection(Config.PostgreConnectionString))
{
using (NpgsqlCommand cmd = new NpgsqlCommand(cmdText, conn))
{
NpgsqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return sdr;
}
}
}
catch (Exception ex)
{
Log.Instance.Error("GetReader:" + ex.Message);
return null;
}
}
}这里我们需要引入一个dll，Npgsql；Npgsql封装了一些.NET与PostgreSQL操作方法。
然后我们将一些常用的分析方法进行封装，封装好后以API的方式提供给应用程序使用。
public class MainGISFunction
{
PostgreHelper dbHelper = new PostgreHelper();
/// <summary>
/// 判断两个经纬度点之间的距离
/// 参数格式：经度,纬度
/// </summary>
/// <param name="lonlat1">经纬度1</param>
/// <param name="lonlat2">经纬度2</param>
/// <returns>直线距离（单位m）/null</returns>
public DataTable GetPointDistance(string lonlat1, string lonlat2)
{
try
{
string strsql = string.Format(@"SELECT ST_Distance(ST_Transform(ST_SetSRID(ST_MakePoint({0}),4326), 26986)
,ST_Transform(ST_SetSRID(ST_MakePoint({1}),4326), 26986)) AS Distance", lonlat1, lonlat2);
return dbHelper.ExecuteQuery(strsql).Tables[0];
}
catch (Exception ex)
{
Log.Instance.Error("GetPointDistance:" + ex.Message);
return null;
}
}
/// <summary>
/// 查询点与点之间相距小于某个值的集合
/// </summary>
/// <param name="lonlat"></param>
/// <param name="points"></param>
/// <param name="distance"></param>
/// <returns></returns>
public DataTable GetPointsDwithin(string lonlat, List<string> points, int distance)
{
try
{
StringBuilder sbsql = new StringBuilder();
for (int i = 0; i < points.Count; i++)
{
if (i == 0)
{
sbsql.AppendFormat(@"SELECT {0} AS Index, st_dwithin(ST_Transform(ST_SetSRID(ST_MakePoint({0}),4326), 26986)
,ST_Transform(ST_SetSRID(ST_MakePoint({1}),4326), 26986),{2}) AS Status", lonlat, points[i], distance);
}
else
{
sbsql.AppendFormat(@" union all SELECT {0} AS Index, st_dwithin(ST_Transform(ST_SetSRID(ST_MakePoint({0}),4326), 26986)
,ST_Transform(ST_SetSRID(ST_MakePoint({1}),4326), 26986),{2}) AS Status", lonlat, points[i], distance);
}
}
DataSet ds = dbHelper.ExecuteQuery(sbsql.ToString());
DataRow[] rows = ds.Tables[0].Select("Status='true'");
if (rows.Length > 0)
{
DataTable dtNew = ds.Tables[0].Clone();
for (int i = 0; i < rows.Length; i++)
{
dtNew.Rows.Add(rows[i].ItemArray);
}
return dtNew;
}
else
{
return null;
}
}
catch (Exception ex)
{
Log.Instance.Error("GetPointOnLines:" + ex.Message);
return null;
}
}
/// <summary>
/// 判断某个经纬度点是否在某条路线上
/// </summary>
/// <param name="lonlat">经纬度点</param>
/// <param name="line">路线经纬度集合</param>
/// <param name="buffer">缓冲区域，及在这个缓冲区域的点都可以判断为在这条路上，可以称作道路边缘到中心距离的宽度</param>
/// <returns>ture/false/null</returns>
public DataTable GetPointOnTheLine(string lonlat, List<string> line, int buffer)
{
try
{
StringBuilder sbline = new StringBuilder();
for (int i = 0; i < line.Count; i++)
{
sbline.Append(line[i].Replace(',', ' ') + ",");
}
string strsql = string.Format(@"select ST_Contains(St_Astext(ST_Buffer(geography(ST_GeomFromText('LINESTRING({0})')),{1}))
,st_astext(geography(ST_GeomFromText('POINT({2})')))) AS Status", sbline.ToString().Trim(','), buffer, lonlat.Replace(',', ' '));
return dbHelper.ExecuteQuery(strsql).Tables[0];
}
catch (Exception ex)
{
Log.Instance.Error("GetPointOnTheLine:" + ex.Message);
return null;
}
}
/// <summary>
/// 判断某个经纬度点是否在这些路线上的其中一条
/// </summary>
/// <param name="lonlat">经纬度点</param>
/// <param name="lines">多条路线经纬度集合</param>
/// <param name="buffer">缓冲区域，及在这个缓冲区域的点都可以判断为在这条路上，可以称作道路边缘到中心距离的宽度</param>
/// <returns>ture/false/null</returns>
public DataTable GetPointOnOneLine(string lonlat, List<List<string>> lines, int buffer)
{
try
{
StringBuilder sblines = new StringBuilder();
for (int i = 0; i < lines.Count; i++)
{
StringBuilder sbline = new StringBuilder();
for (int j = 0; j < lines[i].Count; j++)
{
sbline.Append(lines[i][j].Replace(',', ' ') + ",");
}
sblines.Append("(" + sbline.ToString().Trim(',') + "),");
}
string strsql = string.Format(@"select ST_Contains(St_Astext(ST_Buffer(geography(ST_GeomFromText('MULTILINESTRING({0})')),{1}))
,st_astext(geography(ST_GeomFromText('POINT({2})')))) AS Status", sblines.ToString().Trim(','), buffer, lonlat.Replace(',', ' '));
return dbHelper.ExecuteQuery(strsql).Tables[0];
}
catch (Exception ex)
{
Log.Instance.Error("GetPointOnOneLine:" + ex.Message);
return null;
}
}
/// <summary>
/// 判断某个经纬度点是否在这些路线上的其中一条
/// </summary>
/// <param name="lonlat">经纬度点</param>
/// <param name="lines">多条路线经纬度集合</param>
/// <param name="buffer">缓冲区域，及在这个缓冲区域的点都可以判断为在这条路上，可以称作道路边缘到中心距离的宽度</param>
/// <returns>ture/false/null</returns>
public DataTable myGetPointOnOneLine(string lonlat, List<string> lines, int buffer)
{
try
{
StringBuilder sbline = new StringBuilder();
for (int i = 0; i < lines.Count; i++)
{
sbline.Append(lines[i].Replace(',', ' ') + ",");
}
string strsql = string.Format(@"select ST_Contains(St_Astext(ST_Buffer(geography(ST_GeomFromText('LINESTRING({0})')),{1}))
,st_astext(geography(ST_GeomFromText('POINT({2})')))) AS Status", sbline.ToString().Trim(','), buffer, lonlat.Replace(',', ' '));
return dbHelper.ExecuteQuery(strsql).Tables[0];
}
catch (Exception ex)
{
Log.Instance.Error("myGetPointOnOneLine:" + ex.Message);
return null;
}
}
/// <summary>
/// 判断某个经纬度点所在的路线
/// </summary>
/// <param name="lonlat"></param>
/// <param name="lines"></param>
/// <param name="buffer"></param>
/// <returns>路线索引集合/null</returns>
public DataTable GetPointOnLines(string lonlat, List<List<string>> lines, int buffer)
{
try
{
StringBuilder sbsql = new StringBuilder();
for (int i = 0; i < lines.Count; i++)
{
StringBuilder sbline = new StringBuilder();
for (int j = 0; j < lines[i].Count; j++)
{
sbline.Append(lines[i][j].Replace(',', ' ') + ",");
}
if (i == 0)
{
sbsql.AppendFormat(@"select {0} AS Index, ST_Contains(St_Astext(ST_Buffer(geography(ST_GeomFromText('LINESTRING({1})')),{2}))
,st_astext(geography(ST_GeomFromText('POINT({3})')))) AS Status", i, sbline.ToString().Trim(','), buffer, lonlat.Replace(',', ' '));
}
else
{
sbsql.AppendFormat(@" union all select {0} AS Index, ST_Contains(St_Astext(ST_Buffer(geography(ST_GeomFromText('LINESTRING({1})')),{2}))
,st_astext(geography(ST_GeomFromText('POINT({3})')))) AS Status", i, sbline.ToString().Trim(','), buffer, lonlat.Replace(',', ' '));
}
}
DataSet ds = dbHelper.ExecuteQuery(sbsql.ToString());
DataRow[] rows = ds.Tables[0].Select("Status='true'");
if (rows.Length > 0)
{
DataTable dtNew = ds.Tables[0].Clone();
for (int i = 0; i < rows.Length; i++)
{
dtNew.Rows.Add(rows[i].ItemArray);
}
return dtNew;
}
else
{
return null;
}
}
catch (Exception ex)
{
Log.Instance.Error("GetPointOnLines:" + ex.Message);
return null;
}
}
/// <summary>
/// 判断某个经纬度点所在的路线
/// </summary>
/// <param name="lonlat"></param>
/// <param name="lines"></param>
/// <param name="buffer"></param>
/// <returns>路线索引集合/null</returns>
public DataTable GetPointOnLinesByWidth(string lonlat, List<List<string>> lines, List<int> buffer)
{
try
{
StringBuilder sbsql = new StringBuilder();
for (int i = 0; i < lines.Count; i++)
{
StringBuilder sbline = new StringBuilder();
for (int j = 0; j < lines[i].Count; j++)
{
sbline.Append(lines[i][j].Replace(',', ' ') + ",");
}
if (i == 0)
{
sbsql.AppendFormat(@"select {0} AS Index, ST_Contains(St_Astext(ST_Buffer(geography(ST_GeomFromText('LINESTRING({1})')),{2}))
,st_astext(geography(ST_GeomFromText('POINT({3})')))) AS Status", i, sbline.ToString().Trim(','), buffer[i], lonlat.Replace(',', ' '));
}
else
{
sbsql.AppendFormat(@" union all select {0} AS Index, ST_Contains(St_Astext(ST_Buffer(geography(ST_GeomFromText('LINESTRING({1})')),{2}))
,st_astext(geography(ST_GeomFromText('POINT({3})')))) AS Status", i, sbline.ToString().Trim(','), buffer[i], lonlat.Replace(',', ' '));
}
}
DataSet ds = dbHelper.ExecuteQuery(sbsql.ToString());
DataRow[] rows = ds.Tables[0].Select("Status='true'");
if (rows.Length > 0)
{
DataTable dtNew = ds.Tables[0].Clone();
for (int i = 0; i < rows.Length; i++)
{
dtNew.Rows.Add(rows[i].ItemArray);
}
return dtNew;
}
else
{
return null;
}
}
catch (Exception ex)
{
Log.Instance.Error("GetPointOnLines:" + ex.Message);
return null;
}
}
/// <summary>
/// 判断某个经纬度点是否在这个区域内
/// </summary>
/// <param name="lonlat">经纬度点</param>
/// <param name="area">多个经纬度点组成的区域集合，区域点必须是闭合的，即以哪个经纬度点开始必须以那个经纬度点结束</param>
/// <returns>ture/false/null</returns>
public DataTable GetPointOnTheArea(string lonlat, List<string> area)
{
try
{
StringBuilder sbarea = new StringBuilder();
for (int i = 0; i < area.Count; i++)
{
sbarea.Append(area[i].Replace(',', ' ') + ",");
}
string strsql = string.Format(@"SELECT ST_Contains( ST_MakePolygon(ST_GeomFromText('LINESTRING({0}) '))
,st_point({1})) AS Status", sbarea.ToString().Trim(','), lonlat);
return dbHelper.ExecuteQuery(strsql).Tables[0];
}
catch (Exception ex)
{
Log.Instance.Error("GetPointOnTheArea:" + ex.Message);
return null;
}
}
/// <summary>
/// 判断某个经纬度点所在区域
/// </summary>
/// <param name="lonlat">经纬度点</param>
/// <param name="areas">多个区域的经纬度集合</param>
/// <returns>区域索引集合/null</returns>
public DataTable GetPointOnAreas(string lonlat, List<List<string>> areas)
{
try
{
StringBuilder sbsql = new StringBuilder();
for (int i = 0; i < areas.Count; i++)
{
StringBuilder sbarea = new StringBuilder();
for (int j = 0; j < areas[i].Count; j++)
{
sbarea.Append(areas[i][j].Replace(',', ' ') + ",");
}
if (i == 0)
{
sbsql.AppendFormat(@"SELECT {0} AS Index, ST_Contains( ST_MakePolygon(ST_GeomFromText('LINESTRING({1}) '))
,st_point({2})) AS Status", i, sbarea.ToString().Trim(','), lonlat);
}
else
{
sbsql.AppendFormat(@" union all SELECT {0} AS Index, ST_Contains( ST_MakePolygon(ST_GeomFromText('LINESTRING({1}) '))
,st_point({2})) AS Status", i, sbarea.ToString().Trim(','), lonlat);
}
}
DataSet ds = dbHelper.ExecuteQuery(sbsql.ToString());
DataRow[] rows = ds.Tables[0].Select("Status='true'");
if (rows.Length > 0)
{
DataTable dtNew = ds.Tables[0].Clone();
for (int i = 0; i < rows.Length; i++)
{
dtNew.Rows.Add(rows[i].ItemArray);
}
return dtNew;
}
else
{
return null;
}
}
catch (Exception ex)
{
Log.Instance.Error("GetPointOnOneArea:" + ex.Message);
return null;
}
}
/// <summary>
/// 获取路线长度
/// </summary>
/// <param name="line">路线经纬度点集合</param>
/// <returns>路线长度（单位m）/null</returns>
public DataTable GetLineLength(List<string> line)
{
try
{
StringBuilder sbline = new StringBuilder();
for (int i = 0; i < line.Count; i++)
{
sbline.Append(line[i].Replace(',', ' ') + ",");
}
string strsql = string.Format(@"select ST_Length(Geography(ST_GeomFromText('LINESTRING({0})'))) AS Length", sbline.ToString().Trim(','));
return dbHelper.ExecuteQuery(strsql).Tables[0];
}
catch (Exception ex)
{
Log.Instance.Error("GetLineLength:" + ex.Message);
return null;
}
}
}这样，应用程序就可以根据自己的需求选择不同的方法进行特定应用场景的分析了。
目前我用此方法写的一个分析服务已经上线运行了一年多了，服务器配置为4核8G可承载上万台车辆实时空间分析，也不会出现内存与CPU占用过高的现象发生。
并且如果针对海量车辆接入，我们通过Redis集群部署也是可以的。
更多推荐
4
0- 0
已为社区贡献7条内容
所有评论(0)