Spatial Mapping with NetTopologySuite
Note
It's recommended that you start by reading the general Entity Framework Core docs on spatial support.
PostgreSQL supports spatial data and operations via the PostGIS extension, which is a mature and feature-rich database spatial implementation. .NET doesn't provide a standard spatial library, but NetTopologySuite is a leading spatial library. The Npgsql EF Core provider has a plugin which allows you to map the NTS types to PostGIS columns, allowing seamless reading and writing. This is the recommended way to interact with spatial types in Npgsql.
Note that the EF Core NetTopologySuite plugin depends on the Npgsql ADO.NET NetTopology plugin, which provides NetTopologySuite support at the lower level. The EF Core plugin automatically sets up the ADO.NET plugin.
Setup
To use the NetTopologySuite plugin, add the Npgsql.EntityFrameworkCore.PostgreSQL.NetTopologySuite nuget to your project. Then, make the following modification to your UseNpgsql() line:
protected override void OnConfiguring(DbContextOptionsBuilder builder)
{
builder.UseNpgsql("Host=localhost;Database=test;Username=npgsql_tests;Password=npgsql_tests",
o => o.UseNetTopologySuite());
}
This sets up all the necessary mappings and operation translators. In addition, to make sure that the PostGIS extension is installed in your database, add the following to your DbContext:
protected override void OnModelCreating(ModelBuilder builder)
{
builder.HasPostgresExtension("postgis");
}
At this point spatial support is set up. You can now use NetTopologySuite types as regular properties in your entities, and even perform some operations:
public class City
{
public int Id { get; set; }
public string Name { get; set; }
public Point Location { get; set; }
}
var nearbyCities = context.Cities.Where(c => c.Location.Distance(somePoint) < 100);
Constraining your type names
With the code above, the provider will create a database column of type geometry. This is perfectly fine, but be aware that this type accepts any geometry type (point, polygon...), with any coordinate system (XY, XYZ...). It's good practice to constrain the column to the exact type of data you will be storing, but unfortunately the provider isn't aware of your required coordinate system and therefore can't do that for you. Consider explicitly specifying your column types on your properties as follows:
[Column(TypeName="geometry (point)")]
public Point Location { get; set; }
This will constrain your column to XY points only. The same can be done via the fluent API with HasColumnType().
Geography (geodetic) support
PostGIS has two types: geometry (for Cartesian coordinates) and geography (for geodetic or spherical coordinates). You can read about the geometry/geography distinction in the PostGIS docs or in this blog post. In a nutshell, geography is much more accurate when doing calculations over long distances, but is more expensive computationally and supports only a small subset of the spatial operations supported by geometry.
The Npgsql provider will be default map all NetTopologySuite types to PostGIS geometry. However, you can instruct it to map certain properties to geography instead:
protected override void OnModelCreating(ModelBuilder builder)
{
builder.Entity<City>().Property(b => b.Location).HasColumnType("geography (point)");
}
or via an attribute:
public class City
{
public int Id { get; set; }
public string Name { get; set; }
[Column(TypeName="geography")]
public Point Location { get; set; }
}
Once you do this, your column will be created as geography, and spatial operations will behave as expected.
Operation translation
The following table lists NetTopologySuite operations which are translated to PostGIS SQL operations. This allows you to use these NetTopologySuite methods and members efficiently - evaluation will happen on the server side. Since evaluation happens at the server, table data doesn't need to be transferred to the client (saving bandwidth), and in some cases indexes can be used to speed things up.
Note that the plugin is far from covering all spatial operations. If an operation you need is missing, please open an issue to request for it.
| .NET | SQL | Notes |
|---|---|---|
| geom.Area() | ST_Area(geom) | |
| geom.AsBinary() | ST_AsBinary(geom) | |
| geom.AsText() | ST_AsText(geom) | |
| geom.Boundary | ST_Boundary(geom) | |
| geom.Buffer(d) | ST_Buffer(geom,d) | |
| geom.Centroid | ST_Centroid(geom) | |
| geom1.Contains(geom2) | ST_Contains(geom1, geom2) | |
| geomCollection.Count | ST_NumGeometries(geom1) | |
| linestring.Count | ST_NumPoints(linestring) | |
| geom1.ConvexHull() | ST_ConvexHull(geom1) | |
| geom1.Covers(geom2) | ST_Covers(geom1, geom2) | |
| geom1.CoveredBy(geom2) | ST_CoveredBy(geom1, geom2) | |
| geom1.Crosses(geom2) | ST_Crosses(geom1, geom2) | |
| geom1.Difference(geom2) | ST_Difference(geom1, geom2) | |
| geom1.Dimension | ST_Dimension(geom1) | |
| geom1.Disjoint(geom2) | ST_Disjoint(geom1, geom2) | |
| geom1.Distance(geom2) | ST_Distance(geom1, geom2) | |
| EF.Functions.DistanceKnn(geom1, geom2) | geom1 <-> geom2 | Added in 6.0 |
| EF.Functions.Distance(geom1, geom2, useSpheriod) | ST_Distance(geom1, geom2, useSpheriod) | Added in 6.0 |
| geom1.Envelope | ST_Envelope(geom1) | |
| geom1.ExactEquals(geom2) | ST_OrderingEquals(geom1, geom2) | |
| lineString.EndPoint | ST_EndPoint(lineString) | |
| polygon.ExteriorRing | ST_ExteriorRing(polygon) | |
| geom1.Equals(geom2) | geom1 = geom2 | |
| geom1.Polygon.EqualsExact(geom2) | geom1 = geom2 | |
| geom1.EqualsTopologically(geom2) | ST_Equals(geom1, geom2) | |
| EF.Functions.Force2D | ST_Force2D(geom) | Added in 6.0 |
| geom.GeometryType | GeometryType(geom) | |
| geomCollection.GetGeometryN(i) | ST_GeometryN(geomCollection, i) | |
| linestring.GetPointN(i) | ST_PointN(linestring, i) | |
| geom1.Intersection(geom2) | ST_Intersection(geom1, geom2) | |
| geom1.Intersects(geom2) | ST_Intersects(geom1, geom2) | |
| geom.InteriorPoint | ST_PointOnSurface(geom) | |
| lineString.IsClosed() | ST_IsClosed(lineString) | |
| geomCollection.IsEmpty() | ST_IsEmpty(geomCollection) | |
| linestring.IsRing | ST_IsRing(linestring) | |
| geom.IsWithinDistance(geom2,d) | ST_DWithin(geom1, geom2, d) | |
| EF.Functions.IsWithinDistance(geom1, geom2, d, useSpheriod) | ST_DWithin(geom1, geom2, d, useSpheriod) | Added in 6.0 |
| geom.IsSimple() | ST_IsSimple(geom) | |
| geom.IsValid() | ST_IsValid(geom) | |
| lineString.Length | ST_Length(lineString) | |
| geom.Normalized | ST_Normalize(geom) | |
| geomCollection.NumGeometries | ST_NumGeometries(geomCollection) | |
| polygon.NumInteriorRings | ST_NumInteriorRings(polygon) | |
| lineString.NumPoints | ST_NumPoints(lineString) | |
| geom1.Overlaps(geom2) | ST_Overlaps(geom1, geom2) | |
| geom.PointOnSurface | ST_PointOnSurface(geom) | |
| geom1.Relate(geom2) | ST_Relate(geom1, geom2) | |
| geom.Reverse() | ST_Reverse(geom) | |
| geom1.SRID | ST_SRID(geom1) | |
| lineString.StartPoint | ST_StartPoint(lineString) | |
| geom1.SymmetricDifference(geom2) | ST_SymDifference(geom1, geom2) | |
| geom.ToBinary() | ST_AsBinary(geom) | |
| geom.ToText() | ST_AsText(geom) | |
| geom1.Touches(geom2) | ST_Touches(geom1, geom2) | |
| EF.Functions.Transform(geom, srid) | ST_Transform(geom, srid) | |
| geom1.Union(geom2) | ST_Union(geom1, geom2) | |
| geom1.Within(geom2) | ST_Within(geom1, geom2) | |
| point.M | ST_M(point) | |
| point.X | ST_X(point) | |
| point.Y | ST_Y(point) | |
| point.Z | ST_Z(point) |