-
Notifications
You must be signed in to change notification settings - Fork 161
/
SqlServerUseGeometryController.cs
239 lines (224 loc) · 10.1 KB
/
SqlServerUseGeometryController.cs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using CoreWebsite.Converters;
using CoreWebsite.EntityFramework;
using CoreWebsite.EntityFramework.Models.UseGeometry;
using GeoAPI.Geometries;
using GeoJSON.Net.Geometry;
using Microsoft.AspNetCore.Mvc;
using NetTopologySuite;
using NetTopologySuite.Geometries;
using Newtonsoft.Json;
namespace CoreWebsite.Controllers
{
public class SqlServerUseGeometryController : Controller
{
//参考资料
//https://docs.microsoft.com/zh-cn/ef/core/modeling/spatial
//https://docs.microsoft.com/zh-cn/sql/t-sql/spatial-geometry/spatial-types-geometry-transact-sql?view=sql-server-2017
private readonly WebsiteDbContext _dbContext;
private static int srid = 4326;
public SqlServerUseGeometryController(WebsiteDbContext dbContext)
{
_dbContext = dbContext;
}
#region Point
//http://localhost:61541/SqlServerUseGeometry/CreatePoint?cityName=%E5%8C%97%E4%BA%AC&x=100&y=10
public IActionResult CreatePoint(string cityName, double x, double y)
{
IPoint currentLocation = GetLocation(x, y);
_dbContext.Cities.Add(new City()
{
CityName = cityName,
Location = currentLocation
});
_dbContext.SaveChanges();
return Json("ok");
}
private static IPoint GetLocation(double x, double y)
{
//空间引用标识符(spatial reference identifier, SRID):所使用的坐标空间引用系统
//srid=4326:wgs84
var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(srid: srid);
var currentLocation = geometryFactory.CreatePoint(new Coordinate(x, y));
return currentLocation;
}
//http://localhost:61541/SqlServerUseGeometry/UpdatePoint?citiId=1&cityName=%E6%88%90%E9%83%BD&x=100&y=10
public IActionResult UpdatePoint(int citiId, string cityName, double x, double y)
{
var city = _dbContext.Cities.FirstOrDefault(c => c.CityID == citiId);
if (city == null)
{
return Json($"查询不到id为{citiId}的城市");
}
city.CityName = cityName;
city.Location = GetLocation(x, y);
_dbContext.SaveChanges();
return Json("ok");
}
//http://localhost:61541/SqlServerUseGeometry/GetPoint/1
[Route("/SqlServerUseGeometry/GetPoint/{citiId}")]
public IActionResult GetPoint(int citiId)
{
var city = _dbContext.Cities.FirstOrDefault(c => c.CityID == citiId);
if (city == null)
{
return Json($"查询不到id为{citiId}的城市");
}
//理解:Geometry/geojson/WKT 三者可以互转
//Geometry to GeoJSON
//GeoJSON:http://geojson.org/
//GeoJSON.Net:
//https://github.com/GeoJSON-Net/GeoJSON.Net
//https://github.com/GeoJSON-Net/GeoJSON.Net/tree/master/src/GeoJSON.Net.Tests/Geometry
//原理是先把Geometry对象转成geojson对象,然后再序列化
Position position = new Position(city.Location.X, city.Location.Y);
GeoJSON.Net.Geometry.Point point = new GeoJSON.Net.Geometry.Point(position);
var s = JsonConvert.SerializeObject(point);
//Geometry to wkt
//http://nettopologysuite.github.io/html/class_net_topology_suite_1_1_i_o_1_1_w_k_t_reader.html
var s2= NetTopologySuite.IO.WKTWriter.ToPoint(city.Location.Coordinate);
return Json($"GeoJSON结果:{s},WKT结果:{s2}");
}
#endregion
#region Road
[Route("/SqlServerUseGeometry/CreateRoad/{roadName}")]
public IActionResult CreateRoad(string roadName)
{
//LinearRing的点必须形成一个封闭的线串,而LineString则不需要
var line = new NetTopologySuite.Geometries.LineString(new Coordinate[]
{
new Coordinate(10,0),
new Coordinate(10,10),
new Coordinate(0,10),
new Coordinate(0,0),
//new Coordinate(10,0),
});
//设置坐标系
line.SRID = srid;
_dbContext.Roads.Add(new Road()
{
RoadName= roadName,
Line = line
});
_dbContext.SaveChanges();
return Json("ok");
}
[Route("/SqlServerUseGeometry/GetRoad/{roadId}")]
public IActionResult GetRoad(int roadId)
{
var road = _dbContext.Roads.FirstOrDefault(x => x.RoadID == roadId);
if (road == null)
{
return Json($"查询不到id为{roadId}的道路");
}
//Geometry to GeoJSON
var coordinates = new List<IPosition>();
foreach (var item in road.Line.Coordinates)
{
coordinates.Add(new Position(item.X, item.Y, item.Z));
}
GeoJSON.Net.Geometry.LineString line = new GeoJSON.Net.Geometry.LineString(coordinates);
var s= JsonConvert.SerializeObject(line);
//Geometry to wkt
var s2 = NetTopologySuite.IO.WKTWriter.ToLineString(road.Line.CoordinateSequence);
return Json($"GeoJSON结果:{s},WKT结果:{s2}");
}
#endregion
#region Polygon
//http://localhost:61541/SqlServerUseGeometry/CreatePolygon/%E4%B8%AD%E5%9B%BD
[Route("/SqlServerUseGeometry/CreatePolygon/{countryName}")]
public IActionResult CreatePolygon(string countryName)
{
var geom = new NetTopologySuite.Geometries.Polygon(
new LinearRing(new Coordinate[]
{
//逆时针绘制
new Coordinate(10,0),
new Coordinate(10,10),
new Coordinate(0,10),
new Coordinate(0,0),
new Coordinate(10,0),
}));
//设置坐标系
geom.SRID = srid;
_dbContext.Countries.Add(new Country()
{
CountryName = countryName,
Border = geom
});
_dbContext.SaveChanges();
return Json("ok");
}
[Route("/SqlServerUseGeometry/GetPolygon/{countryId}")]
public IActionResult GetPolygon(int countryId)
{
var country = _dbContext.Countries.FirstOrDefault(x => x.CountryID == countryId);
if (country == null)
{
return Json($"查询不到id为{countryId}的国家");
}
//Geometry to GeoJSON
//https://nettopologysuite.github.io/html/class_net_topology_suite_1_1_geometries_1_1_polygon.html
//https://github.com/synhershko/nettopologysuite/blob/master/NetTopologySuite.IO/NetTopologySuite.IO.GeoJSON/Converters/GeometryConverter.cs
//->已copy到GeometryConverter和GeometryArrayConverter
var lines = new List<GeoJSON.Net.Geometry.LineString>();
var polygon = country.Border as NetTopologySuite.Geometries.Polygon;
List<Coordinate[]> res = new List<Coordinate[]>();
res.Add(polygon.Shell.Coordinates);
foreach (ILineString interiorRing in polygon.InteriorRings)
res.Add(interiorRing.Coordinates);
foreach(var line in res)
{
var coordinates = new List<IPosition>();
foreach (var item in line)
{
coordinates.Add(new Position(item.X, item.Y, item.Z));
}
lines.Add(new GeoJSON.Net.Geometry.LineString(coordinates));
}
GeoJSON.Net.Geometry.Polygon jsonPolygon = new GeoJSON.Net.Geometry.Polygon(lines);
var s = JsonConvert.SerializeObject(jsonPolygon);
//Geometry to wkt
//点和线的是静态方法,面的是方法_(:з」∠)_
var writer = new NetTopologySuite.IO.WKTWriter();
var s2 = writer.Write(country.Border);
return Json($"GeoJSON结果:{s},WKT结果:{s2}");
}
#endregion
#region Calculation
[Route("/SqlServerUseGeometry/CalculateDistanceBetweenPoints/{x1}/{y1}/{x2}/{y2}")]
public IActionResult CalculateDistanceBetweenPoints(double x1, double y1, double x2, double y2)
{
//https://nettopologysuite.github.io/html/class_net_topology_suite_1_1_operation_1_1_distance_1_1_distance_op.html
var point1 = new NetTopologySuite.Geometries.Point(x1, y1) { SRID = srid };
var point2 = new NetTopologySuite.Geometries.Point(x2, y2) { SRID = srid };
var distance= NetTopologySuite.Operation.Distance.DistanceOp.Distance(point1, point2);
return Json($"点({x1},{y1})和点({x2},{y2})的距离是{distance}.");
}
[Route("/SqlServerUseGeometry/CheckIsPointInRegion/{x}/{y}")]
public IActionResult CheckIsPointInRegion(double x, double y)
{
var geom = new NetTopologySuite.Geometries.Polygon(
new LinearRing(new Coordinate[]
{
//逆时针绘制
new Coordinate(10,0),
new Coordinate(10,10),
new Coordinate(0,10),
new Coordinate(0,0),
new Coordinate(10,0),
}));
//设置坐标系
geom.SRID = srid;
var point = new NetTopologySuite.Geometries.Point(x, y) { SRID = srid };
//https://stackoverflow.com/questions/53820355/fast-find-if-points-belong-to-polygon-nettopologysuite-geometries-c-net-cor
var prepGeom = NetTopologySuite.Geometries.Prepared.PreparedGeometryFactory.Prepare(geom);
var isContain = prepGeom.Contains(point);
return Json(isContain?$"点({x},{y})包含在面以内": $"点({x},{y})不包含在面以内");
}
#endregion
}
}