1028 lines
37 KiB
C#
1028 lines
37 KiB
C#
using System.Data;
|
||
using System.IO.Compression;
|
||
using System.Text;
|
||
using Infrastructure;
|
||
using Infrastructure.Extensions;
|
||
using Microsoft.Extensions.Options;
|
||
using NetTopologySuite;
|
||
using NetTopologySuite.Geometries;
|
||
using NetTopologySuite.IO;
|
||
using NetTopologySuite.IO.Esri;
|
||
using NPOI.HSSF.UserModel;
|
||
using NPOI.SS.UserModel;
|
||
using NPOI.XSSF.UserModel;
|
||
using OpenAuth.App.BaseApp.Base;
|
||
using OpenAuth.App.BasicQueryService;
|
||
using OpenAuth.App.FormScheme;
|
||
using OpenAuth.App.FormScheme.FormHelpers;
|
||
using OpenAuth.App.Interface;
|
||
using OpenAuth.App.Request;
|
||
using OpenAuth.App.Response;
|
||
using OpenAuth.App.ServiceApp.ShpGeo.Request;
|
||
using OpenAuth.App.ServiceApp.ShpGeo.Utils;
|
||
using OpenAuth.Repository;
|
||
using OpenAuth.Repository.Domain;
|
||
using SqlSugar;
|
||
using Shapefile = NetTopologySuite.IO.Esri.Shapefile;
|
||
|
||
namespace OpenAuth.App.ServiceApp.ShpGeo;
|
||
|
||
public class ShpGeoLayerApp : SqlSugarBaseApp<ShpGeoLayer, SugarDbContext>
|
||
{
|
||
private readonly string _filePath;
|
||
private readonly ISqlSugarClient _client;
|
||
CommonDataManager _commonDataManager;
|
||
public ShpGeoLayerApp(ISugarUnitOfWork<SugarDbContext> unitWork,
|
||
ISimpleClient<ShpGeoLayer> repository, IAuth auth, FormSchemeApp formSchemeApp,
|
||
IOptions<AppSetting> setOptions, ISqlSugarClient client, CommonDataManager commonDataManager) : base(unitWork, repository, auth)
|
||
{
|
||
_client = client;
|
||
_filePath = setOptions.Value.UploadPath;
|
||
if (string.IsNullOrEmpty(_filePath))
|
||
{
|
||
_filePath = AppContext.BaseDirectory;
|
||
}
|
||
|
||
_commonDataManager = commonDataManager;
|
||
}
|
||
|
||
|
||
/// <summary>
|
||
/// 添加geo图层
|
||
/// </summary>
|
||
/// <param name="obj"></param>
|
||
/// <returns></returns>
|
||
/// <exception cref="Exception"></exception>
|
||
public async Task<Response<bool>> Add(ShpGeoLayerShpReq obj)
|
||
{
|
||
var record = obj.MapTo<ShpGeoLayer>();
|
||
record.CreateTime = DateTime.Now;
|
||
record.UpdateTime = DateTime.Now;
|
||
record.Id = Guid.NewGuid().ToString();
|
||
using var db = Repository.AsSugarClient();
|
||
if (db.DbMaintenance.IsAnyTable(obj.DataTable, false))
|
||
{
|
||
throw new Exception(obj.DataTable + "数据表已存在");
|
||
}
|
||
|
||
var layer = await Repository.AsQueryable().Where(a => a.DataTable.Equals(obj.DataTable)).FirstAsync();
|
||
if (layer != null)
|
||
{
|
||
throw new Exception($"图层记录数据表名{obj.DataTable}已存在");
|
||
}
|
||
|
||
// 发布图层
|
||
if (record.DataSourceType.Equals("shp"))
|
||
{
|
||
/*
|
||
// 相对路径
|
||
var sldFile = record.AppendPath;
|
||
// 取得全路径
|
||
var sldPath = Path.Combine(_filePath, sldFile);
|
||
// 处理SLD
|
||
var isSuccess = await GeoUtil.PublishSldStyle(sldPath, obj.DataTable);
|
||
if (!isSuccess)
|
||
{
|
||
throw new Exception("SLD样式发布失败");
|
||
}*/
|
||
|
||
var response = await CreateDataTable(obj);
|
||
if (!response.Result)
|
||
{
|
||
throw new Exception("创建shp数据表失败");
|
||
}
|
||
|
||
// 配置存储仓库及发布图层
|
||
//var sldName = Path.GetFileName(sldFile);
|
||
//var sldName = Path.GetFileNameWithoutExtension(sldFile);
|
||
var result =
|
||
await GeoUtil.CreateStoreAndLayer("", obj.SpatialRef, obj.DataTable, obj.StyleName);
|
||
if (!result)
|
||
{
|
||
db.DbMaintenance.DropTable(obj.DataTable);
|
||
throw new Exception("发布图层失败");
|
||
}
|
||
}
|
||
else // excel
|
||
{
|
||
var excelPath = Path.Combine(_filePath, obj.ShpPath);
|
||
var excelFileName = Path.GetFileNameWithoutExtension(excelPath);
|
||
// xls xlsx
|
||
// 使用NPOI读取Excel文件
|
||
using (var fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
|
||
{
|
||
IWorkbook workbook;
|
||
if (Path.GetExtension(excelPath).Equals(".xls"))
|
||
{
|
||
workbook = new HSSFWorkbook(fileStream);
|
||
}
|
||
else
|
||
{
|
||
workbook = new XSSFWorkbook(fileStream);
|
||
}
|
||
|
||
var sheet = workbook.GetSheetAt(0);
|
||
// 获取总行数
|
||
var rowCount = sheet.LastRowNum;
|
||
if (rowCount < 3)
|
||
{
|
||
throw new Exception("只有两行表头");
|
||
}
|
||
|
||
// 获取总列数
|
||
var columnCount = sheet.GetRow(0).LastCellNum;
|
||
var headers = new List<DataTableAttr>();
|
||
// 遍历表头
|
||
var chineseRow = sheet.GetRow(0);
|
||
var englishRow = sheet.GetRow(1);
|
||
|
||
for (var j = 0; j < columnCount; j++)
|
||
{
|
||
var header = new DataTableAttr
|
||
{
|
||
InitName = chineseRow.GetCell(j).ToString(),
|
||
RefName = englishRow.GetCell(j).ToString()?.ToLower(),
|
||
// 字段类型
|
||
Type = "string",
|
||
// 原始字段名称
|
||
Name = englishRow.GetCell(j).ToString(),
|
||
// 字段长度
|
||
Length = 64
|
||
};
|
||
headers.Add(header);
|
||
}
|
||
|
||
var keys = headers.Select(a => a.RefName).ToList();
|
||
if (!keys.Contains("lng") || !keys.Contains("lat"))
|
||
{
|
||
throw new Exception("缺少经纬度字段");
|
||
}
|
||
|
||
|
||
var typeBuilder = db.DynamicBuilder().CreateClass(obj.DataTable,
|
||
new SugarTable() { TableName = obj.DataTable, TableDescription = obj.ServerName + "图斑" });
|
||
//添加主键
|
||
typeBuilder.CreateProperty("id", typeof(string), new SugarColumn()
|
||
{
|
||
IsPrimaryKey = true,
|
||
IsIdentity = false,
|
||
ColumnDataType = "varchar",
|
||
Length = 36,
|
||
ColumnDescription = "主键",
|
||
});
|
||
//添加主键
|
||
typeBuilder.CreateProperty("geometry", typeof(string), new SugarColumn()
|
||
{
|
||
IsPrimaryKey = false,
|
||
IsIdentity = false,
|
||
// ColumnDataType = "geometry(GEOMETRY)",
|
||
ColumnDataType = string.Concat("geometry(GEOMETRY,",
|
||
obj.SpatialRef.AsSpan(obj.SpatialRef.LastIndexOf(":", StringComparison.Ordinal) + 1),
|
||
")"),
|
||
ColumnDescription = "图斑",
|
||
});
|
||
|
||
headers.ForEach(u =>
|
||
{
|
||
if (!u.RefName.Equals("lng") && !u.RefName.Equals("lat"))
|
||
{
|
||
typeBuilder.CreateProperty(u.RefName, typeof(string), new SugarColumn()
|
||
{
|
||
IsPrimaryKey = false,
|
||
IsIdentity = false,
|
||
IsNullable = true,
|
||
Length = u.Length,
|
||
ColumnDescription = u.InitName,
|
||
});
|
||
}
|
||
});
|
||
// 开启事务
|
||
await db.Ado.BeginTranAsync(IsolationLevel.ReadCommitted);
|
||
// 创建表
|
||
db.CodeFirst.InitTables(typeBuilder.BuilderType());
|
||
|
||
var cols = new string[headers.Count];
|
||
for (var i = 0; i < headers.Count; i++)
|
||
{
|
||
if (headers[i].RefName.Equals("lng"))
|
||
{
|
||
cols[i] = "geometry";
|
||
}
|
||
else if (headers[i].RefName.Equals("lat"))
|
||
{
|
||
cols[i] = "id";
|
||
}
|
||
else
|
||
{
|
||
cols[i] = headers[i].RefName;
|
||
}
|
||
}
|
||
|
||
// 遍历数据行
|
||
for (var i = 2; i < rowCount; i++)
|
||
{
|
||
var row = sheet.GetRow(i);
|
||
if (row == null)
|
||
{
|
||
continue;
|
||
}
|
||
|
||
var data = new Dictionary<string, object>();
|
||
// 遍历列
|
||
string lat = null, lng = null;
|
||
for (var j = 0; j < columnCount; j++)
|
||
{
|
||
var cell = row.GetCell(j);
|
||
if (cell == null)
|
||
{
|
||
continue;
|
||
}
|
||
|
||
var cellValue = cell.ToString();
|
||
switch (headers[j].RefName)
|
||
{
|
||
case "lat":
|
||
lat = cellValue;
|
||
continue;
|
||
case "lng":
|
||
lng = cellValue;
|
||
continue;
|
||
default:
|
||
data[headers[j].RefName] = cellValue;
|
||
break;
|
||
}
|
||
}
|
||
|
||
if (lat != null && lng != null)
|
||
{
|
||
var geography =
|
||
NtsGeometryServices.Instance.CreateGeometryFactory(
|
||
int.Parse(obj.SpatialRef.Substring(
|
||
obj.SpatialRef.IndexOf(":", StringComparison.Ordinal) + 1)));
|
||
var point = geography.CreatePoint(new Coordinate(double.Parse(lng), double.Parse(lat)));
|
||
data["geometry"] = point.AsText();
|
||
}
|
||
|
||
data["id"] = Guid.NewGuid().ToString();
|
||
await db.Insertable(data)
|
||
.AS(obj.DataTable) // 指定目标表名
|
||
.InsertColumns(cols) // 指定要插入的列
|
||
.ExecuteCommandAsync();
|
||
}
|
||
|
||
await db.Ado.CommitTranAsync();
|
||
// 关于图层发布失败后，如何处理？
|
||
// excel 矢量点发布图层
|
||
var response = await GeoUtil.CreateStoreAndLayer("", obj.SpatialRef, obj.DataTable, "");
|
||
if (!response)
|
||
{
|
||
// 如果图层发布失败 删除数据表
|
||
db.DbMaintenance.DropTable(obj.DataTable);
|
||
}
|
||
|
||
workbook.Close();
|
||
}
|
||
}
|
||
|
||
var geoserver = GeoUtil.GeoserverUrl;
|
||
var workspace = GeoUtil.Workspace;
|
||
var storeName = GeoUtil.DbStoreName;
|
||
var getBboxResponse = await GeoUtil.GetDBLayerBBox(workspace, storeName, obj.DataTable);
|
||
if (getBboxResponse.Code == 200)
|
||
{
|
||
var bbox = getBboxResponse.Result;
|
||
var serverUrl = $"{geoserver}/{workspace}/wms" +
|
||
$"?service=WMS" +
|
||
$"&version=1.1.0" +
|
||
$"&request=GetMap" +
|
||
$"&layers={workspace}:{obj.DataTable}" +
|
||
$"&bbox=" + bbox +
|
||
$"&width=768&height=619" +
|
||
$"&srs=" + obj.SpatialRef +
|
||
"&styles=" +
|
||
/*$"&styles=" + (obj.DataSourceType.Equals("shp")
|
||
? Path.GetFileName(obj.AppendPath)?.Replace(".sld", "")
|
||
: "") +*/
|
||
$"&format=application/openlayers";
|
||
record.ServerUrl = serverUrl;
|
||
var pbfUrl = $"{geoserver}/gwc/service/tms/1.0.0/{workspace}:{obj.DataTable}@{obj.SpatialRef}@pbf";
|
||
record.PbfUrl = pbfUrl;
|
||
}
|
||
|
||
var settingResponse = await GeoUtil.SettingLayerReturnFormat("", obj.DataTable);
|
||
if (!settingResponse.Result)
|
||
{
|
||
// 设置返回格式失败
|
||
}
|
||
|
||
var flag = await Repository.InsertAsync(record);
|
||
return new Response<bool>
|
||
{
|
||
Result = flag,
|
||
Message = flag && getBboxResponse.Code == 200 ? "success" : flag ? "成功但获取serverurl失败" : "error"
|
||
};
|
||
}
|
||
|
||
public async Task<Response<PageInfo<List<ShpGeoLayer>>>> LoadPage(ShpGeoLayerReq req)
|
||
{
|
||
RefAsync<int> totalCount = 0;
|
||
var sources = await base.Repository.AsQueryable()
|
||
.WhereIF(!string.IsNullOrEmpty(req.SererName), a => a.ServerName.Contains(req.SererName))
|
||
.WhereIF(!string.IsNullOrEmpty(req.startTime) && !string.IsNullOrEmpty(req.endTime), a =>
|
||
SqlFunc.Between(a.CreateTime, Convert.ToDateTime(req.startTime),
|
||
Convert.ToDateTime(req.endTime))) // 时间段查询
|
||
.ToPageListAsync(req.page, req.limit, totalCount);
|
||
|
||
return new Response<PageInfo<List<ShpGeoLayer>>>
|
||
{
|
||
Result = new PageInfo<List<ShpGeoLayer>>
|
||
{
|
||
Items = sources,
|
||
Total = totalCount
|
||
}
|
||
};
|
||
}
|
||
|
||
public ShpGeoLayer Get(string id)
|
||
{
|
||
return Repository.GetById(id);
|
||
}
|
||
|
||
private static async Task UnZip(string zipFilePath, string extractPath)
|
||
{
|
||
await Task.Run(() =>
|
||
{
|
||
Directory.CreateDirectory(extractPath);
|
||
// 设置字符编码
|
||
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
|
||
var gbk = Encoding.GetEncoding("utf-8");
|
||
using (var archive = ZipFile.OpenRead(zipFilePath))
|
||
{
|
||
// 列举ZIP文件中的条目
|
||
foreach (var entry in archive.Entries)
|
||
{
|
||
var xxx = gbk.GetString(Encoding.Default.GetBytes(entry.FullName));
|
||
Console.WriteLine(xxx);
|
||
}
|
||
|
||
// 提取ZIP文件中的所有文件到指定目录
|
||
|
||
foreach (var entry in archive.Entries)
|
||
{
|
||
if (entry.Name != string.Empty)
|
||
{
|
||
// 确保完整路径存在 entry.FullName 是否可以编码
|
||
var fixedEntryName = entry.FullName.Replace("/", "");
|
||
var destinationPath = Path.GetFullPath(Path.Combine(extractPath, fixedEntryName));
|
||
Console.WriteLine("解压文件路径：" + destinationPath);
|
||
if (!destinationPath.StartsWith(Path.GetFullPath(extractPath) + Path.DirectorySeparatorChar))
|
||
{
|
||
throw new UnauthorizedAccessException("试图提取的文件超出了目标文件夹的路径边界。");
|
||
}
|
||
|
||
// 提取条目到目标路径
|
||
entry.ExtractToFile(destinationPath, overwrite: true);
|
||
}
|
||
}
|
||
}
|
||
|
||
// 遍历解压目录，是否有shp后缀的文件
|
||
if (!Directory.Exists(extractPath))
|
||
{
|
||
throw new Exception("文件解压失败");
|
||
}
|
||
});
|
||
}
|
||
|
||
private static void DeleteFile(string extractPath)
|
||
{
|
||
try
|
||
{
|
||
// 递归删除目录和其内容
|
||
Directory.Delete(extractPath, true);
|
||
Console.WriteLine("解压目录删除成功");
|
||
}
|
||
catch (IOException ex)
|
||
{
|
||
Console.WriteLine("解压目录不能删除: " + ex.Message);
|
||
}
|
||
catch (UnauthorizedAccessException ex)
|
||
{
|
||
Console.WriteLine("没有权限删除解压目录: " + ex.Message);
|
||
}
|
||
}
|
||
|
||
|
||
private static string MapDbaseTypeToPostgresString(char dbaseType)
|
||
{
|
||
return dbaseType switch
|
||
{
|
||
'C' => "varchar",
|
||
'N' => "numeric",
|
||
'L' => "boolean",
|
||
'D' => "date",
|
||
'M' => "text",
|
||
_ => "varchar"
|
||
};
|
||
}
|
||
|
||
|
||
public async Task<Response<bool>> Test(string tableName, string serverName, string srid, string shpPath)
|
||
{
|
||
using var db = Repository.AsSugarClient();
|
||
|
||
// 校验 表是否存在
|
||
var isExists = db.DbMaintenance.IsAnyTable(tableName, false);
|
||
if (isExists)
|
||
{
|
||
//throw new Exception(tableName + "该数据库表已存在");
|
||
db.DbMaintenance.DropTable(tableName);
|
||
}
|
||
|
||
var shpGeoLayer = await db.Queryable<ShpGeoLayer>()
|
||
.Where(c => c.ServerName.Equals(serverName))
|
||
.FirstAsync();
|
||
if (!shpGeoLayer.IsNullOrEmpty())
|
||
{
|
||
throw new Exception("服务名已存在");
|
||
}
|
||
|
||
// 插入图斑数据
|
||
shpPath = Path.Combine(_filePath, shpPath);
|
||
var extractPath = shpPath.Substring(0, shpPath.LastIndexOf(".", StringComparison.Ordinal)) + "extract";
|
||
await UnZip(shpPath, extractPath);
|
||
var searchPattern = "*.shp"; // 设置你想要遍历的文件后缀名
|
||
var fileName = Directory.GetFiles(extractPath, searchPattern, SearchOption.AllDirectories);
|
||
if (fileName.IsEmpty())
|
||
{
|
||
throw new Exception("未找到shp文件");
|
||
}
|
||
|
||
string shpFileName = fileName[0];
|
||
// shpFile 文件路径
|
||
string shpFile = Path.Combine(extractPath, shpFileName);
|
||
var rows = new List<Dictionary<string, object>>();
|
||
|
||
using (var dataReader = new ShapefileDataReader(shpFile, GeometryFactory.Default))
|
||
{
|
||
var headers = new List<DataTableAttr>();
|
||
|
||
for (var i = 0; i < dataReader.DbaseHeader.NumFields; i++)
|
||
{
|
||
var colAttr = new DataTableAttr();
|
||
//colAttr.DataType = dataReader.DbaseHeader.Fields[i].Type;
|
||
colAttr.RefName = dataReader.DbaseHeader.Fields[i].Name;
|
||
colAttr.InitName = dataReader.DbaseHeader.Fields[i].Name;
|
||
colAttr.Length = dataReader.DbaseHeader.Fields[i].Length;
|
||
headers.Add(colAttr);
|
||
}
|
||
|
||
// 创建数据表
|
||
var typeBuilder = db.DynamicBuilder().CreateClass(tableName,
|
||
new SugarTable() { TableName = tableName, TableDescription = serverName + "图斑" });
|
||
//添加主键
|
||
typeBuilder.CreateProperty("id", typeof(string), new SugarColumn()
|
||
{
|
||
IsPrimaryKey = true,
|
||
IsIdentity = false,
|
||
ColumnDataType = "varchar",
|
||
Length = 36,
|
||
ColumnDescription = "主键",
|
||
});
|
||
typeBuilder.CreateProperty("Geometry", typeof(string), new SugarColumn()
|
||
{
|
||
IsPrimaryKey = false,
|
||
IsIdentity = false,
|
||
ColumnDataType = string.Concat("geometry(GEOMETRY, ",
|
||
srid.AsSpan(srid.LastIndexOf(":", StringComparison.Ordinal) + 1), ")"),
|
||
ColumnDescription = "图斑",
|
||
});
|
||
headers.ForEach(u =>
|
||
{
|
||
typeBuilder.CreateProperty(u.RefName, typeof(string), new SugarColumn()
|
||
{
|
||
IsPrimaryKey = false,
|
||
IsIdentity = false,
|
||
//ColumnDataType = u.Type,
|
||
IsNullable = true,
|
||
Length = u.Length,
|
||
ColumnDescription = u.InitName,
|
||
});
|
||
});
|
||
// 开启事务
|
||
await db.Ado.BeginTranAsync(IsolationLevel.ReadCommitted);
|
||
// 创建表
|
||
db.CodeFirst.InitTables(typeBuilder.BuilderType());
|
||
var x = 0;
|
||
List<string> cols = new List<string>();
|
||
// 读取一下行数据
|
||
while (dataReader.Read() && x++ < 20)
|
||
{
|
||
// 增加 id 及
|
||
// var cols = new string[dataReader.DbaseHeader.NumFields + 2];
|
||
var insertObj = new Dictionary<string, object>
|
||
{
|
||
{ "id", Guid.NewGuid().ToString() }
|
||
};
|
||
Console.Write("类型： " + dataReader.Geometry.GeometryType);
|
||
// 读取一行数据
|
||
for (int i = 0, j = 0; i < dataReader.FieldCount; i++)
|
||
{
|
||
cols.Add(dataReader.GetName(i));
|
||
if (dataReader.GetName(i).ToLower().Equals("geometry"))
|
||
{
|
||
var geom = dataReader.GetValue(i) as Geometry;
|
||
//var srid = geom.SRID;
|
||
insertObj.Add(dataReader.GetName(i), geom.AsText());
|
||
}
|
||
else
|
||
{
|
||
insertObj.Add(dataReader.GetName(i), dataReader.GetValue(i));
|
||
}
|
||
}
|
||
|
||
rows.Add(insertObj);
|
||
}
|
||
|
||
/*await db.Insertable(rows)
|
||
.AS(tableName) // 指定目标表名
|
||
//.InsertColumns(cols) // 指定要插入的列
|
||
.ExecuteCommandAsync();*/
|
||
var sql = "INSERT INTO \"public\".\"" + tableName + "\" (";
|
||
// to do
|
||
var colsStr = "";
|
||
foreach (var key in rows.First().Keys)
|
||
{
|
||
colsStr += "\"" + key + "\",";
|
||
}
|
||
|
||
colsStr = colsStr.Substring(0, colsStr.Length - 1);
|
||
sql += colsStr + ") VALUES (";
|
||
var values = "";
|
||
foreach (var dictionary in rows)
|
||
{
|
||
// todo 如何处理 geometry 类型
|
||
foreach (var value in dictionary.Values)
|
||
{
|
||
values += "'" + value + "',";
|
||
}
|
||
}
|
||
|
||
await db.Ado.ExecuteCommandAsync(sql);
|
||
await db.Ado.CommitTranAsync();
|
||
}
|
||
|
||
|
||
return new Response<bool>()
|
||
{
|
||
Result = true,
|
||
Message = "ok"
|
||
};
|
||
}
|
||
|
||
|
||
/// <summary>
|
||
/// 图层更新
|
||
/// </summary>
|
||
/// <param name="updateLayerReq"></param>
|
||
/// <returns></returns>
|
||
public async Task<Response<bool>> UpdateLayer(UpdateGeoLayerShpReq updateLayerReq)
|
||
{
|
||
var db = Repository.AsSugarClient();
|
||
await db.Ado.BeginTranAsync();
|
||
var layer = await Repository.GetByIdAsync(updateLayerReq.Id);
|
||
//var styleName = layer.StyleName; // 前面使用表名作为样式名称
|
||
var newStyleName = updateLayerReq.StyleName;
|
||
var result = await GeoUtil.UpdateStyle(GeoContext.Instance.Workspace, layer.DataTable, newStyleName);
|
||
/* if (!string.IsNullOrEmpty(updateLayerReq.sldPath))
|
||
{
|
||
// 更新样式
|
||
// 取得全路径
|
||
// var sldPath = Path.Combine(_filePath, updateLayerReq.sldPath);
|
||
var response = await GeoUtil.UpdateSldStyle(sldPath, styleName);
|
||
if (!response)
|
||
{
|
||
throw new Exception("样式更新失败");
|
||
}
|
||
}*/
|
||
if (!result.Result)
|
||
{
|
||
throw new Exception("更换样式失败");
|
||
}
|
||
|
||
var record = updateLayerReq.MapTo<ShpGeoLayer>();
|
||
await db.Updateable(record).IgnoreNullColumns().ExecuteCommandAsync();
|
||
await db.Ado.CommitTranAsync();
|
||
return new Response<bool>()
|
||
{
|
||
Result = true,
|
||
Message = "更新成功"
|
||
};
|
||
}
|
||
|
||
/// <summary>
|
||
/// 删除图层
|
||
/// </summary>
|
||
/// <param name="id"></param>
|
||
/// <returns></returns>
|
||
public async Task<Response<bool>> Delete(string id)
|
||
{
|
||
var db = Repository.AsSugarClient();
|
||
await db.Ado.BeginTranAsync();
|
||
|
||
var shp = await Repository.GetByIdAsync(id);
|
||
var dataTable = shp.DataTable;
|
||
db.DbMaintenance.DropTable(dataTable);
|
||
// todo 删除对应图层
|
||
await db.Deleteable<ShpGeoLayer>().Where(c => c.Id.Equals(id)).ExecuteCommandAsync();
|
||
await db.Ado.CommitTranAsync();
|
||
return new Response<bool>()
|
||
{
|
||
Result = true,
|
||
Message = "删除成功"
|
||
};
|
||
}
|
||
|
||
public async Task<Response<ShpInfo>> ParseShpInfo(string zipFilePath, string tableName,
|
||
string srid)
|
||
{
|
||
using var db = Repository.AsSugarClient();
|
||
// 开启事务
|
||
|
||
if (db.DbMaintenance.IsAnyTable(tableName))
|
||
{
|
||
throw new Exception("表名已存在");
|
||
}
|
||
|
||
// 取得文件完全路径
|
||
zipFilePath = Path.Combine(_filePath, zipFilePath);
|
||
// 确保ZIP文件存在
|
||
if (!File.Exists(zipFilePath))
|
||
{
|
||
throw new FileNotFoundException("ZIP文件未找到。");
|
||
}
|
||
|
||
var exist = await db.Queryable<ShpGeoLayer>()
|
||
.Where(c => c.DataType.Equals(tableName))
|
||
.CountAsync();
|
||
if (exist > 0)
|
||
{
|
||
throw new Exception(tableName + "表名已存在");
|
||
}
|
||
|
||
// 打开ZIP存档 取除后缀之前的字符串
|
||
var extractPath = zipFilePath.Substring(0, zipFilePath.LastIndexOf(".", StringComparison.Ordinal)) +
|
||
"extract";
|
||
// 解压文件
|
||
await UnZip(zipFilePath, extractPath);
|
||
var searchPattern = "*.shp"; // 设置你想要遍历的文件后缀名
|
||
var fileName = Directory.GetFiles(extractPath, searchPattern, SearchOption.AllDirectories);
|
||
if (fileName.IsEmpty())
|
||
{
|
||
throw new Exception("压缩文件中无shp文件");
|
||
}
|
||
|
||
var shpFileName = fileName[0];
|
||
var shpFile = Path.Combine(extractPath, shpFileName);
|
||
var shapeType = Shapefile.GetShapeType(shpFile);
|
||
var shpInfo = new ShpInfo();
|
||
switch (shapeType)
|
||
{
|
||
case ShapeType.Point:
|
||
shpInfo.DataType = "点";
|
||
break;
|
||
case ShapeType.Polygon:
|
||
shpInfo.DataType = "面";
|
||
break;
|
||
case ShapeType.PolyLine:
|
||
shpInfo.DataType = "线";
|
||
break;
|
||
default:
|
||
throw new Exception("不支持的shp类型: " + shapeType);
|
||
}
|
||
|
||
var attrs = new List<DataTableAttr>();
|
||
// 添加自定义字段
|
||
var geom = new DataTableAttr
|
||
{
|
||
Name = "Geometry",
|
||
Type = string.Concat("geometry(GEOMETRY,",
|
||
srid.AsSpan(srid.LastIndexOf(":", StringComparison.Ordinal) + 1),
|
||
")")
|
||
};
|
||
attrs.Add(geom);
|
||
using (var reader = new ShapefileDataReader(shpFile, GeometryFactory.Default))
|
||
{
|
||
// 获取DbfDataReader，用于读取属性信息
|
||
var dbfReader = reader.DbaseHeader;
|
||
foreach (var dbaseFieldDescriptor in dbfReader.Fields)
|
||
{
|
||
DataTableAttr attr = new DataTableAttr();
|
||
attr.Name = dbaseFieldDescriptor.Name;
|
||
attr.Length = dbaseFieldDescriptor.Length;
|
||
attr.Type = MapDbaseTypeToPostgresString(dbaseFieldDescriptor.DbaseType);
|
||
attrs.Add(attr);
|
||
}
|
||
}
|
||
|
||
shpInfo.Headers = attrs;
|
||
shpInfo.TableName = tableName;
|
||
return new Response<ShpInfo>()
|
||
{
|
||
Result = shpInfo,
|
||
Message = "ok"
|
||
};
|
||
}
|
||
|
||
// todo 拆分，主方法中完成数据库操作，
|
||
public async Task<Response<bool>> CreateDataTable(ShpGeoLayerShpReq shpInfo)
|
||
{
|
||
using var db = Repository.AsSugarClient();
|
||
|
||
var tableName = shpInfo.DataTable;
|
||
// 校验 表是否存在
|
||
var exist = await db.Queryable<ShpGeoLayer>()
|
||
.Where(c => c.DataTable.Equals(tableName))
|
||
.CountAsync();
|
||
if (exist > 0)
|
||
{
|
||
throw new Exception(tableName + "库表名称已存在");
|
||
}
|
||
|
||
var shpGeoLayer = await db.Queryable<ShpGeoLayer>()
|
||
.Where(c => c.ServerName.Equals(shpInfo.ServerName))
|
||
.FirstAsync();
|
||
if (!shpGeoLayer.IsNullOrEmpty())
|
||
{
|
||
throw new Exception("未执行shp解析");
|
||
}
|
||
|
||
// 创建数据表
|
||
var typeBuilder = db.DynamicBuilder().CreateClass(tableName,
|
||
new SugarTable() { TableName = tableName, TableDescription = shpInfo.ServerName + "图斑" });
|
||
//添加主键
|
||
typeBuilder.CreateProperty("id", typeof(string), new SugarColumn()
|
||
{
|
||
IsPrimaryKey = true,
|
||
IsIdentity = false,
|
||
ColumnDataType = "varchar",
|
||
Length = 36,
|
||
ColumnDescription = "主键",
|
||
});
|
||
|
||
shpInfo.Headers.ForEach(u =>
|
||
{
|
||
typeBuilder.CreateProperty(u.RefName, typeof(string), new SugarColumn()
|
||
{
|
||
IsPrimaryKey = false,
|
||
IsIdentity = false,
|
||
ColumnDataType = u.Type,
|
||
IsNullable = true,
|
||
Length = u.Length,
|
||
ColumnDescription = u.InitName,
|
||
});
|
||
});
|
||
// 开启事务
|
||
await db.Ado.BeginTranAsync(IsolationLevel.ReadCommitted);
|
||
// 创建表
|
||
db.CodeFirst.InitTables(typeBuilder.BuilderType());
|
||
|
||
// 插入图斑数据
|
||
var shpPath = Path.Combine(_filePath, shpInfo.ShpPath);
|
||
var extractPath = shpPath.Substring(0, shpPath.LastIndexOf(".", StringComparison.Ordinal)) + "extract";
|
||
var searchPattern = "*.shp"; // 设置你想要遍历的文件后缀名
|
||
var fileName = Directory.GetFiles(extractPath, searchPattern, SearchOption.AllDirectories);
|
||
if (fileName.IsEmpty())
|
||
{
|
||
throw new Exception("未找到shp文件");
|
||
}
|
||
|
||
var shpFileName = fileName[0];
|
||
// shpFile 文件路径
|
||
var shpFile = Path.Combine(extractPath, shpFileName);
|
||
shpInfo.Headers.Add(new DataTableAttr
|
||
{
|
||
RefName = "",
|
||
InitName = "",
|
||
Name = ""
|
||
});
|
||
|
||
// 批量插入
|
||
var cols = new string[shpInfo.Headers.Count];
|
||
for (var i = 0; i < shpInfo.Headers.Count - 1; i++)
|
||
{
|
||
cols[i] = shpInfo.Headers[i].RefName;
|
||
}
|
||
|
||
cols[^1] = "id";
|
||
using (var dataReader = new ShapefileDataReader(shpFile, GeometryFactory.Default))
|
||
{
|
||
Console.WriteLine("记录数" + dataReader.RecordCount);
|
||
//throw new Exception("失败");
|
||
var objs = new List<Dictionary<string, object>>(1001);
|
||
var num = 0;
|
||
int count = 0;
|
||
while (dataReader.Read())
|
||
{
|
||
count++;
|
||
Console.WriteLine("记录行：" + count);
|
||
var insertObj = new Dictionary<string, object>
|
||
{
|
||
{ "id", Guid.NewGuid().ToString() }
|
||
};
|
||
// 读取列
|
||
for (int i = 0, j = 0; i < dataReader.FieldCount; i++)
|
||
{
|
||
var colName = dataReader.GetName(i);
|
||
var header = shpInfo.Headers[j];
|
||
if (header.Name.Equals(colName))
|
||
{
|
||
if (colName.ToLower().Equals("geometry"))
|
||
{
|
||
var geometry = (Geometry)dataReader.GetValue(i);
|
||
var srid = shpInfo.SpatialRef.Substring(
|
||
shpInfo.SpatialRef.IndexOf(":", StringComparison.Ordinal) + 1);
|
||
var geometryForWgs84 = GeometryFactory.Default.WithSRID(int.Parse(srid))
|
||
.CreateGeometry(geometry);
|
||
insertObj.Add(shpInfo.Headers[j++].RefName, geometryForWgs84.AsText());
|
||
}
|
||
else
|
||
{
|
||
insertObj.Add(shpInfo.Headers[j++].RefName, dataReader.GetValue(i));
|
||
}
|
||
}
|
||
}
|
||
|
||
objs.Add(insertObj);
|
||
if (num++ == 999)
|
||
{
|
||
await db.Insertable(objs)
|
||
.AS(tableName) // 指定目标表名
|
||
.InsertColumns(cols) // 指定要插入的列
|
||
.ExecuteCommandAsync();
|
||
num = 0;
|
||
objs.Clear();
|
||
}
|
||
}
|
||
|
||
// 读取末尾
|
||
await db.Insertable(objs)
|
||
.AS(tableName) // 指定目标表名
|
||
.InsertColumns(cols) // 指定要插入的列
|
||
.ExecuteCommandAsync();
|
||
}
|
||
|
||
await db.Ado.CommitTranAsync();
|
||
// 删文件
|
||
//DeleteFile(extractPath);
|
||
return new Response<bool>()
|
||
{
|
||
Result = true,
|
||
Message = "ok"
|
||
};
|
||
}
|
||
|
||
public async Task<Response<bool>> Test1(string tableName, string serverName, string srid, string shpPath)
|
||
{
|
||
var db = Repository.AsSugarClient();
|
||
|
||
|
||
// 输出目
|
||
/*var db = Repository.AsSugarClient();
|
||
shpPath = Path.Combine(_filePath, shpPath);
|
||
var extractPath = shpPath.Substring(0, shpPath.LastIndexOf(".", StringComparison.Ordinal)) + "extract";
|
||
var searchPattern = "*.shp"; // 设置你想要遍历的文件后缀名
|
||
var fileName = Directory.GetFiles(extractPath, searchPattern, SearchOption.AllDirectories);
|
||
if (fileName.IsEmpty())
|
||
{
|
||
throw new Exception("未找到shp文件");
|
||
}
|
||
|
||
var shpFileName = fileName[0];
|
||
// shpFile 文件路径
|
||
var shpFile = Path.Combine(extractPath, shpFileName);
|
||
await db.Ado.BeginTranAsync();
|
||
var rows = new List<Dictionary<string, object>>();
|
||
using (var dataReader = new ShapefileDataReader(shpFile, GeometryFactory.Default))
|
||
{
|
||
while (dataReader.Read())
|
||
{
|
||
var insertObj = new Dictionary<string, object>
|
||
{
|
||
{ "id", Guid.NewGuid().ToString() },
|
||
{ "geometry", dataReader.Geometry.AsBinary() },
|
||
};
|
||
rows.Add(insertObj);
|
||
}
|
||
}
|
||
|
||
int insertCount = await db.Insertable(rows)
|
||
.AS(tableName) // 指定目标表名
|
||
.InsertColumns("id", "geometry") // 指定要插入的列
|
||
.ExecuteCommandAsync();
|
||
/*
|
||
foreach (var geometry in Shapefile.ReadAllGeometries(shpFile))
|
||
{
|
||
var insertObj = new Dictionary<string, object>
|
||
{
|
||
{ "id", Guid.NewGuid().ToString() },
|
||
{ "geometry", geometry.AsBinary() },
|
||
};
|
||
int insertCount = await db.Insertable(insertObj)
|
||
.AS(tableName) // 指定目标表名
|
||
.InsertColumns("id", "geometry") // 指定要插入的列
|
||
.ExecuteCommandAsync();
|
||
}
|
||
#1#
|
||
|
||
await db.Ado.CommitTranAsync();*/
|
||
return new Response<bool>
|
||
{
|
||
Result = true
|
||
};
|
||
}
|
||
|
||
/// <summary>
|
||
/// 获取图层中心点
|
||
/// </summary>
|
||
/// <param name="tableName"></param>
|
||
/// <returns></returns>
|
||
public async Task<Response<string>> GetGeomData(string tableName)
|
||
{
|
||
var sql = "select ST_AsText(ST_Centroid(ST_Extent(geometry))) from \"public\".\"" + tableName + "\"";
|
||
var db = Repository.AsSugarClient();
|
||
var result = await db.Ado.SqlQuerySingleAsync<string>(sql);
|
||
return new Response<string>
|
||
{
|
||
Result = result,
|
||
Message = "ok"
|
||
};
|
||
}
|
||
|
||
public byte[] QueryVectorTileByTable1(QueryVectorTileByTableReq req)
|
||
{
|
||
VectorTileSearchModel searchModel = new VectorTileSearchModel
|
||
{
|
||
x = req.x,
|
||
y = req.y,
|
||
z = req.z,
|
||
//field = req.field,
|
||
table = req.table,
|
||
filter = req.filter,
|
||
};
|
||
|
||
var fieldArray = req.field.TrimEnd(',').Split(",");
|
||
//req fields
|
||
var fields = "";
|
||
var cols = _client.DbMaintenance.GetColumnInfosByTableName(req.table);
|
||
foreach (var dbColumnInfo in cols)
|
||
{
|
||
if (dbColumnInfo.DbColumnName.Equals("geometry"))
|
||
{
|
||
continue;
|
||
}
|
||
|
||
fields += dbColumnInfo.DbColumnName + ",";
|
||
}
|
||
|
||
fields = fields.TrimEnd(',');
|
||
var fieldArrayTemp = fields.Split(",");
|
||
// 取两个数组的交集
|
||
fields = string.Join(",", fieldArrayTemp.Intersect(fieldArray)) + ",";
|
||
searchModel.field = fields;
|
||
var result = _commonDataManager.VectorTile1(searchModel);
|
||
return result;
|
||
}
|
||
|
||
#region 获取矢量切片
|
||
|
||
/// <summary>
|
||
/// 根据表名获取矢量切片
|
||
/// </summary>
|
||
/// <param name="req"></param>
|
||
/// <returns></returns>
|
||
public byte[] QueryVectorTileByTable(QueryVectorTileByTableReq req)
|
||
{
|
||
VectorTileSearchModel searchModel = new VectorTileSearchModel
|
||
{
|
||
x = req.x,
|
||
y = req.y,
|
||
z = req.z,
|
||
field = req.field,
|
||
table = req.table,
|
||
filter = req.filter,
|
||
};
|
||
var result = new CommonDataManager(base.UnitWork.CreateContext().Db).VectorTile(searchModel);
|
||
return result;
|
||
}
|
||
|
||
#endregion
|
||
|
||
public dynamic GetDataTableRecord(string dataTable, string id)
|
||
{
|
||
using var db = Repository.AsSugarClient();
|
||
var list = db.DbMaintenance.GetColumnInfosByTableName(dataTable);
|
||
var fields = string.Join(",", list.Where(x => x.DbColumnName != "geometry").Select(x => x.DbColumnName));
|
||
return db.Ado.SqlQuery<dynamic>("select " + fields + " from " + dataTable + " where id='" + id + "'").First();
|
||
}
|
||
} |