Skip to content

.NET Standard 2.0 library with extensions methods for PostGIS geometries methods access with linq2db

License

Notifications You must be signed in to change notification settings

apdevelop/linq2db-postgis-extensions

Repository files navigation

Linq2db PostGIS Extensions

PostGIS 2.5 PostGIS 3.0 PostGIS 3.1 PostGIS 3.2

.NET Standard 2.0 library with OGC extensions methods on geometry (NpgsqlTypes.PostgisGeometry or NetTopologySuite.Geometries.Geometry) instances, providing strongly typed access to PostGIS functions on server side while using linq2db LINQ to database provider.

Two implementations

Property LinqToDBPostGisNpgsqlTypes LinqToDBPostGisNetTopologySuite
Extending type NpgsqlTypes.PostgisGeometry NetTopologySuite.Geometries.Geometry
Npgsql version 3.x 4.x
PostGIS reference version 1.5 3.0/3.1/3.2/3.3
Status Legacy Active
NuGet NuGet
Dependencies linq2db, Npgsql linq2db (>= 3.0.0),
NetTopologySuite (>= 2.0.0),
NetTopologySuite.IO.PostGis (>= 2.0.0),
Npgsql (>= 4.1.0),
Npgsql.NetTopologySuite (>= 4.1.0)

Usage

NpgsqlTypes.PostgisGeometry or NetTopologySuite.Geometries.Geometry gets additional methods, similar to Microsoft.SqlServer.Types.SqlGeometry OGC Methods on Geometry Instances or NetTopologySuite plugin for Entity Framework Core for PostgreSQL. These methods will be translated into PostGIS SQL operations, so evaluation will happen on the server side. Calling these methods on client side results in throwing InvalidOperationException. Naming convention follows OGC methods names, starting with ST* prefix.

Using extensions methods inside LINQ expression (Npgsql 4):

using LinqToDBPostGisNetTopologySuite

using (var db = new PostGisTestDataConnection())
{
    NetTopologySuite.Geometries.Point point = new Point(new Coordinate(1492853, 6895498)) { SRID = 3857 };

    var dms = db.Select(() => GeometryOutput.STAsLatLonText(point));

    var nearestCity = db.Cities
        .OrderBy(c => c.Geometry.STDistance(point))
        .FirstOrDefault();

    var selected = db.Polygons
        .Where(p => p.Geometry.STArea() > 150.0)
        .OrderBy(p => p.Geometry.STDistance(point))
        .ToList();

    var stats = db.Polygons
        .Select(c => new
             {
                 Id = c.Id,
                 Name = c.Name,
                 Area = c.Geometry.STArea(),
                 Distance = c.Geometry.STDistance(point),
                 NumPoints = c.Geometry.STNPoints(),
                 Srid = c.Geometry.STSrId(),
                 Wkt = c.Geometry.STAsText(),
             })
        .ToList();
}
[Table("test_geometry", Schema = "public")]
public class PolygonEntity
{
    [Column("geom")]
    public NetTopologySuite.Geometries.Geometry Geometry { get; set; }
}

[Table("owm_cities", Schema = "public")]
public class CityEntity
{
    [Column("geom")]
    public NetTopologySuite.Geometries.Geometry Geometry { get; set; }
}

class PostGisTestDataConnection : LinqToDB.Data.DataConnection
{
    public ITable<PolygonEntity> Polygons { get { return GetTable<PolygonEntity>(); } }
    public ITable<CityEntity> Cities { get { return GetTable<CityEntity>(); } }
}

Technologies

Developed using MS Visual Studio 2019. Tested on PostgreSQL version 9.6/11, PostGIS version 2.5/3.0/3.1/3.2. Depends on linq2db, Npgsql.

Getting started with demo application

  • Make sure you have PostgreSQL DBMS with PostGIS extension installed. Execute SELECT PostGIS_Full_Version() query to check PostGIS version.
  • Create new database named "postgistest" (or any other name), add support of spatial features for this database.
  • Execute SQL script Sql\create_tables.sql in this database.
  • Open solution LinqToDBPostGis.sln in Visual Studio.
  • Check database connection string in App.config of all projects.
  • Run application, view table data along with PostGIS functions results in console output.

TODOs

  • Implement full set of PostGIS methods.
  • Add support for PostGIS geography data type.
  • Test on various versions of PostgreSQL/PostGIS and platforms (including .NET 5 and later).
  • More tests for corner cases.

References