Search Results for

    Show / Hide Table of Contents

    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 quite a good candidate. The Npgsql EF Core provider has a plugin which allows you to map NetTopologySuite's types PostGIS columns, and even translate many useful spatial operations to SQL. 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 arranged for the ADO.NET plugin to be set up.

    Setup

    To set up 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 will set 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
    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)
    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)
    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)
    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)
    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)
    • Improve this Doc
    In This Article
    Back to top © Copyright 2020 The Npgsql Development Team