423 lines
15 KiB
C#
423 lines
15 KiB
C#
using Newtonsoft.Json;
|
||
using Newtonsoft.Json.Linq;
|
||
using OpenAuth.App.Request;
|
||
using OpenAuth.App.Response;
|
||
using SqlSugar;
|
||
using System;
|
||
using System.Collections.Generic;
|
||
using System.Data;
|
||
using System.Linq;
|
||
using System.Text;
|
||
using System.Threading.Tasks;
|
||
|
||
namespace OpenAuth.App.BasicQueryService
|
||
{
|
||
public class CommonDataManager
|
||
{
|
||
ISqlSugarClient client;
|
||
|
||
public CommonDataManager(ISqlSugarClient client)
|
||
{
|
||
this.client = client;
|
||
}
|
||
|
||
/// <summary>
|
||
/// wkt转geom数据
|
||
/// </summary>
|
||
/// <param name="wkt"></param>
|
||
/// <param name="wkid"></param>
|
||
/// <returns></returns>
|
||
public string WktToGeometry(string wkt, int wkid)
|
||
{
|
||
StringBuilder geomsql = new StringBuilder();
|
||
geomsql.AppendFormat(" SELECT CAST(st_geomfromtext('{0}',{1}) AS text) geom", wkt, wkid);
|
||
var model_geom = client.SqlQueryable<dynamic>(geomsql.ToString()).First();
|
||
return model_geom.geom;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Pgsql获取GeoJson数据
|
||
/// </summary>
|
||
/// <param name="req"></param>
|
||
/// <param name="_configuration"></param>
|
||
/// <returns></returns>
|
||
public JToken PgsqlGeoJsonCommon(QueryGeoJsonCommonReq req)
|
||
{
|
||
//如果有的参数为空，设置参数的默认值
|
||
req.init();
|
||
|
||
//查询列的数据
|
||
StringBuilder sqlColumn = new StringBuilder();
|
||
sqlColumn.AppendFormat(" select {1} from {0} {3} order by \"{2}\"", req.tablename, req.column, req.order,
|
||
req.where);
|
||
|
||
StringBuilder sqlGeo = new StringBuilder();
|
||
sqlGeo.AppendFormat(" select st_asgeojson(\"{0}\") geom from \"{1}\" {3} order by \"{2}\"", req.geom,
|
||
req.tablename, req.order, req.where);
|
||
|
||
return PgsqlGeoJsonCommon(sqlColumn, sqlGeo, req.pageIndex, req.limit);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 查询GeoJson公共方法
|
||
/// </summary>
|
||
/// <param name="sqlColumn"></param>
|
||
/// <param name="sqlGeom"></param>
|
||
/// <param name="pageIndex"></param>
|
||
/// <param name="pageSize"></param>
|
||
/// <param name="_configuration"></param>
|
||
/// <returns></returns>
|
||
public JToken PgsqlGeoJsonCommon(StringBuilder sqlColumn, StringBuilder sqlGeom, int pageIndex, int pageSize)
|
||
{
|
||
//获取到数据库操作实例
|
||
int total = 0;
|
||
int totalPage = 0;
|
||
//查询列
|
||
var dt_properties = client.SqlQueryable<dynamic>(sqlColumn.ToString())
|
||
.ToPageList(pageIndex, pageSize, ref total, ref totalPage);
|
||
//列数据转换为字符串
|
||
string str_properties = JsonConvert.SerializeObject(dt_properties);
|
||
//列数据转换为json对象
|
||
JToken jtoken_properties = (JToken)JsonConvert.DeserializeObject(str_properties);
|
||
|
||
//查询geojson数据
|
||
var dt_geom = client.SqlQueryable<dynamic>(sqlGeom.ToString()).ToDataTablePage(pageIndex, pageSize);
|
||
|
||
//组装最终数据
|
||
JObject obj = new JObject();
|
||
JArray array = new JArray();
|
||
obj.Add("type", "FeatureCollection");
|
||
obj.Add("totalNumber", total);
|
||
obj.Add("totalPage", totalPage);
|
||
//遍历geojson数据
|
||
for (var i = 0; i < dt_geom.Rows.Count; i++)
|
||
{
|
||
//行数据
|
||
DataRow item = dt_geom.Rows[i];
|
||
//单条geojson
|
||
string _geom = item["geom"] == DBNull.Value ? "" : item["geom"].ToString();
|
||
//给数据赋值
|
||
JObject featureObj = new JObject();
|
||
featureObj.Add("type", "Feature");
|
||
featureObj.Add("geometry", (JToken)JsonConvert.DeserializeObject(_geom));
|
||
featureObj.Add("properties", jtoken_properties[i]);
|
||
//添加到数组
|
||
array.Add(featureObj);
|
||
}
|
||
|
||
obj.Add("features", array);
|
||
return obj;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 获取列名
|
||
/// </summary>
|
||
/// <param name="tablename"></param>
|
||
/// <returns></returns>
|
||
public List<dynamic> SearchColumnsList(string tablename)
|
||
{
|
||
//查询列名和注释描述
|
||
StringBuilder columnSql = new StringBuilder();
|
||
columnSql.AppendFormat(
|
||
@$" SELECT a.attname column_name,col_description(a.attrelid,a.attnum) as description,format_type(a.atttypid,a.atttypmod) as column_type
|
||
FROM pg_class as c,pg_attribute as a
|
||
where c.relname = '{tablename}'
|
||
and a.attrelid = c.oid
|
||
and a.attnum>0
|
||
and attstattarget = -1");
|
||
//查询结果
|
||
|
||
var column_list = client.SqlQueryable<dynamic>(columnSql.ToString()).ToList();
|
||
return column_list;
|
||
}
|
||
|
||
///// <summary>
|
||
///// 矢量切片公共方法
|
||
///// </summary>
|
||
///// <param name="req"></param>
|
||
///// <param name="_configuration"></param>
|
||
///// <returns></returns>
|
||
//public byte[] VectorTile(VectorTileSearchModel req)
|
||
//{
|
||
// req.Init();
|
||
|
||
// List<double> lons = getLon(req.x, req.z);
|
||
// List<double> lats = getLat(req.y, req.z);
|
||
// double lonmin = lons[0];
|
||
// double lonmax = lons[1];
|
||
// double latmin = lats[1];
|
||
// double latmax = lats[0];
|
||
|
||
// StringBuilder sql = new StringBuilder();
|
||
// sql.AppendFormat(
|
||
// $" SELECT ST_AsMVT(tile, '{req.source_layer}', 4096, 'geom') tile FROM (SELECT {req.field}ST_AsMVTGeom(geom, ST_Transform(ST_MakeEnvelope({lonmin},{latmin},{lonmax},{latmax}, 4326),4326),4096, 256, true) AS geom FROM public.{req.table} where {req.filter} ) AS tile");
|
||
|
||
|
||
// var dataTable = client.Ado.GetDataTable(sql.ToString());
|
||
// byte[] result = (Byte[])dataTable.Rows[0]["tile"];
|
||
// client.Ado.Close();
|
||
// client.Dispose();
|
||
|
||
|
||
// return result;
|
||
//}
|
||
public async Task<byte[]> VectorTileAsync(VectorTileSearchModel req)
|
||
{
|
||
req.Init();
|
||
|
||
List<double> lons = getLon(req.x, req.z);
|
||
List<double> lats = getLat(req.y, req.z);
|
||
double lonmin = lons[0];
|
||
double lonmax = lons[1];
|
||
double latmin = lats[1];
|
||
double latmax = lats[0];
|
||
|
||
StringBuilder sql = new StringBuilder();
|
||
sql.AppendFormat(
|
||
$" SELECT ST_AsMVT(tile, '{req.source_layer}', 4096, 'geom') tile FROM (SELECT {req.field}ST_AsMVTGeom(geom, ST_Transform(ST_MakeEnvelope({lonmin},{latmin},{lonmax},{latmax}, 4326),4326),4096, 256, true) AS geom FROM public.{req.table} where {req.filter} ) AS tile");
|
||
|
||
|
||
var dataTable = await client.Ado.GetDataTableAsync(sql.ToString());
|
||
byte[] result = (Byte[])dataTable.Rows[0]["tile"];
|
||
client.Ado.Close();
|
||
client.Dispose();
|
||
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 查询最新的主键
|
||
/// </summary>
|
||
/// <param name="_columm"></param>
|
||
/// <param name="tablename"></param>
|
||
/// <param name="_gid_const"></param>
|
||
/// <returns></returns>
|
||
public string GetMaxKeyVal(string _column, string tablename, int _gid_const)
|
||
{
|
||
string _val = "";
|
||
//查询最大的gid
|
||
var column_list = SearchColumnsList(tablename);
|
||
string _type = column_list[0].column_type;
|
||
if (_type != "integer")
|
||
{
|
||
_val = Guid.NewGuid().ToString();
|
||
return _val;
|
||
}
|
||
|
||
var _maxgid = client
|
||
.SqlQueryable<dynamic>(
|
||
$"SELECT max(\"{_column}\") \"maxGid\" FROM \"{tablename}\" where \"{_column}\" < 6000000").First()
|
||
.maxGid;
|
||
//gid为空时，说明一条数据都没有
|
||
if (_maxgid == null)
|
||
_val = _gid_const.ToString();
|
||
else
|
||
{
|
||
//如果大于设定值，在此基础上加一，否则设置为默认值
|
||
if (_maxgid >= _gid_const)
|
||
_val = (_maxgid + 1).ToString();
|
||
else
|
||
_val = _gid_const.ToString();
|
||
}
|
||
|
||
return _val;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 格式化wkt数据，兼容多个地块
|
||
/// </summary>
|
||
/// <param name="_wkt"></param>
|
||
/// <param name="_newType"></param>
|
||
/// <param name="_len"></param>
|
||
/// <returns></returns>
|
||
public string WktDataConvert(string _wkt, string _newType, int _len)
|
||
{
|
||
//使用逗号分割
|
||
var list = _wkt.Split(",");
|
||
|
||
//返回值
|
||
StringBuilder res = new StringBuilder();
|
||
|
||
//扩展的维度，用 0补齐
|
||
string extStr = "";
|
||
for (int i = 0; i < list.Length; i++)
|
||
{
|
||
//当前项
|
||
var item = list[i];
|
||
extStr = GetWktDemension(item, _len);
|
||
if (i == 0)
|
||
{
|
||
//第一项数据先去除前缀
|
||
item = item.Substring(item.IndexOf("(")).TrimStart('(');
|
||
if (_newType == "MULTIPOLYGON ZM")
|
||
item = "(((" + item;
|
||
else if (_newType == "MULTILINESTRING")
|
||
item = "((" + item;
|
||
|
||
//扩展后的值
|
||
item = _newType + item + extStr;
|
||
}
|
||
else if (i == list.Length - 1)
|
||
{
|
||
//最后一项
|
||
item = item.TrimEnd(')');
|
||
if (_newType == "MULTIPOLYGON ZM")
|
||
item = item + extStr + ")))";
|
||
else if (_newType == "MULTILINESTRING")
|
||
item = item + extStr + "))";
|
||
}
|
||
else
|
||
{
|
||
//判断是否存在右括号
|
||
if (item.IndexOf(")") > -1)
|
||
{
|
||
//在第一个右括号前插入维度数据
|
||
item = item.Insert(item.IndexOf(")"), extStr);
|
||
}
|
||
else
|
||
{
|
||
//在后面追加维度数据
|
||
item = item + extStr;
|
||
}
|
||
}
|
||
|
||
//追加数据
|
||
res.Append($"{item},");
|
||
}
|
||
|
||
string strRes = res.ToString();
|
||
|
||
//把最后一个逗号截取掉
|
||
return strRes.Substring(0, strRes.Length - 1);
|
||
}
|
||
|
||
private string GetWktDemension(string item, int _len)
|
||
{
|
||
if (item.IndexOf("(") > -1)
|
||
{
|
||
item = item.Substring(item.IndexOf("("));
|
||
item = item.TrimStart('(');
|
||
}
|
||
|
||
string extStr = "";
|
||
//数据的维度
|
||
var _count = _len - item.Split(" ", StringSplitOptions.RemoveEmptyEntries).Length;
|
||
|
||
//扩展维度
|
||
if (_count == 1)
|
||
extStr = " 0";
|
||
else if (_count == 2)
|
||
extStr = " 0 0";
|
||
return extStr;
|
||
}
|
||
|
||
#region Util
|
||
|
||
/// <summary>
|
||
/// 瓦片转经度
|
||
/// </summary>
|
||
/// <param name="x">列号</param>
|
||
/// <param name="z">缩放级别</param>
|
||
/// <returns></returns>
|
||
public static double tile2lon(int x, int z)
|
||
{
|
||
return x / Math.Pow(2.0, z) * 360.0 - 180;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 瓦片转纬度
|
||
/// </summary>
|
||
/// <param name="y">行号</param>
|
||
/// <param name="z">缩放级别</param>
|
||
/// <returns></returns>
|
||
public static double tile2lat(int y, int z)
|
||
{
|
||
double n = Math.PI - (2.0 * Math.PI * y) / Math.Pow(2.0, z);
|
||
return Math.Atan(Math.Sinh(n)) * 180 / Math.PI;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 获取经度最大值和经度最小值
|
||
/// </summary>
|
||
/// <param name="x">列号</param>
|
||
/// <param name="z">缩放级别</param>
|
||
/// <returns></returns>
|
||
public static List<double> getLon(int x, int z)
|
||
{
|
||
List<double> lonExtent = new List<double>();
|
||
lonExtent.Add(tile2lon(x, z));
|
||
lonExtent.Add(tile2lon(x + 1, z));
|
||
return lonExtent;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 获取纬度最大值和经度最小值
|
||
/// </summary>
|
||
/// <param name="y">列号</param>
|
||
/// <param name="z">缩放级别</param>
|
||
/// <returns></returns>
|
||
public static List<double> getLat(int y, int z)
|
||
{
|
||
List<double> latExtent = new List<double>();
|
||
latExtent.Add(tile2lat(y, z));
|
||
latExtent.Add(tile2lat(y + 1, z));
|
||
return latExtent;
|
||
}
|
||
|
||
#endregion
|
||
|
||
public byte[] VectorTile1(VectorTileSearchModel req)
|
||
{
|
||
req.Init();
|
||
|
||
List<double> lons = getLon(req.x, req.z);
|
||
List<double> lats = getLat(req.y, req.z);
|
||
double lonmin = lons[0];
|
||
double lonmax = lons[1];
|
||
double latmin = lats[1];
|
||
double latmax = lats[0];
|
||
|
||
StringBuilder sql = new StringBuilder();
|
||
sql.AppendFormat(
|
||
$" SELECT ST_AsMVT(tile, '{req.source_layer}', 4096, 'geom') tile FROM (SELECT {req.field}ST_AsMVTGeom(geometry, ST_Transform(ST_MakeEnvelope({lonmin},{latmin},{lonmax},{latmax}, 4326),4326),4096, 256, true) AS geom FROM public.{req.table} where {req.filter} ) AS tile");
|
||
|
||
|
||
var dataTable = client.Ado.GetDataTable(sql.ToString());
|
||
byte[] result = (Byte[])dataTable.Rows[0]["tile"];
|
||
client.Ado.Close();
|
||
client.Dispose();
|
||
|
||
return result;
|
||
}
|
||
|
||
|
||
/// <summary>
|
||
/// 矢量切片公共方法
|
||
/// </summary>
|
||
/// <param name="req"></param>
|
||
/// <param name="_configuration"></param>
|
||
/// <returns></returns>
|
||
public byte[] VectorTile(VectorTileSearchModel req)
|
||
{
|
||
req.Init();
|
||
|
||
List<double> lons = getLon(req.x, req.z);
|
||
List<double> lats = getLat(req.y, req.z);
|
||
double lonmin = lons[0];
|
||
double lonmax = lons[1];
|
||
double latmin = lats[1];
|
||
double latmax = lats[0];
|
||
|
||
StringBuilder sql = new StringBuilder();
|
||
sql.AppendFormat(
|
||
$" SELECT ST_AsMVT(tile, '{req.source_layer}', 4096, 'geom') tile FROM (SELECT {req.field}ST_AsMVTGeom(geom, ST_Transform(ST_MakeEnvelope({lonmin},{latmin},{lonmax},{latmax}, 4326),4326),4096, 256, true) AS geom FROM public.{req.table} where {req.filter} ) AS tile");
|
||
|
||
|
||
var dataTable = client.Ado.GetDataTable(sql.ToString());
|
||
byte[] result = (Byte[])dataTable.Rows[0]["tile"];
|
||
|
||
return result;
|
||
}
|
||
}
|
||
} |