Gdal.Npgsql 8.2.0

The package is an Npgsql plugin that allows you to interact with spatial data provided by the PostgreSQL PostGIS extension. On the .NET side, the plugin adds support for geometry types from the GDAL(OGR) library, allowing you to read and write them directly to PostgreSQL.

GDAL is connected via the nuget package MaxRev.Gdal.Core

Usage

Add a dependency on this package, then register the plugin on an NpgsqlDataSource. PostGIS geometry and geography columns — and arrays of them — are mapped to PgGeometry.

using Npgsql;
using Npgsql.Gdal;
using OSGeo.OGR;

var dataSourceBuilder = new NpgsqlDataSourceBuilder(connectionString);

dataSourceBuilder.UseGdal();

var dataSource = dataSourceBuilder.Build();
using (var conn = await dataSource.OpenConnectionAsync())
{
    using var point = Geometry.CreateFromWkt("POINT (30 10)");

    using (var cmd = new NpgsqlCommand("INSERT INTO data (geom) VALUES (@p)", conn))
    {
        cmd.Parameters.AddWithValue("@p", PgGeometry.FromGeometry(point, srid: 4326));
        cmd.ExecuteNonQuery();
    }

    using (var cmd = new NpgsqlCommand("SELECT geom FROM data", conn))
    using (var reader = cmd.ExecuteReader())
    {
        reader.Read();
        var value = reader.GetFieldValue<PgGeometry>(0);

        Console.WriteLine(value.GeometryType);   // no OGR object is created
        Console.WriteLine(value.SRID);

        using var geometry = value.CreateGeometry();   // you own it, you dispose it
    }
}

Creating a geometry, and getting the bytes back

PgGeometry always holds the geometry as EWKB internally, whichever way it was built. These members cover the conversions:

All of the inputs accept either encoding of Z and M — the ISO form (+1000/+2000/+3000 on the geometry type) and the flag form PostGIS uses — and either byte order. Whatever comes in is converted to EWKB right away, so the encoding of an existing PgGeometry never depends on how it was made. Byte order is preserved.

The one-argument FromWkb takes the SRID from the bytes and settles on 0 when they carry none, so it accepts anything without complaint; FromEWkb is the same operation under a name that says what the bytes are.

Naming an SRID in the second argument is an assertion about the bytes, not an override. Bytes that carry no SRID get the one you name; bytes that carry a different one raise ArgumentException rather than silently picking a winner. That applies to 0 as well — FromWkb(bytes, 0) asserts the geometry has no SRID, which is why "don't care" is a separate overload rather than a default value.

var fromDatabaseBytes = PgGeometry.FromWkb(ewkb);          // SRID: whatever the bytes say
var withKnownSrid     = PgGeometry.FromWkb(wkb, 4326);     // attaches 4326, or throws on a clash

byte[] ewkbOut  = withKnownSrid.GetEWkb();   // for PostGIS: carries the SRID
byte[] plainOut = withKnownSrid.GetWkb();    // for everything else: no SRID
int srid        = withKnownSrid.SRID;

GetWkb() and GetEWkb() both return a fresh array every time, so the caller is free to keep or modify what they get.

Bulk insert

GetEWkb() is what a bulk insert needs when the geometries do not go through Npgsql's own type mapping — for example when they are copied into a bytea staging column and turned into geometries on the server:

using (var writer = await conn.BeginBinaryImportAsync(
           "COPY staging (id, ewkb) FROM STDIN (FORMAT BINARY)"))
{
    foreach (var (id, geometry) in rows)
    {
        await writer.StartRowAsync();
        await writer.WriteAsync(id);
        await writer.WriteAsync(PgGeometry.FromGeometry(geometry, srid: 4326).GetEWkb());
    }

    await writer.CompleteAsync();
}

// INSERT INTO data (id, geom) SELECT id, ST_GeomFromEWKB(ewkb) FROM staging;

When the target column is a geometry and the plugin is registered, a PgGeometry can also be written to the COPY stream directly, without touching the bytes at all.

Why PgGeometry and not Geometry

OSGeo.OGR.Geometry wraps native memory and has to be disposed. If a query result were mapped straight to it, every geometry read from the database would be created with no owner — including the ones your code never looks at. Their memory is invisible to the .NET garbage collector, so it is released late and unpredictably.

PgGeometry holds the geometry as EWKB bytes instead:

• reading a row costs a buffer copy, and no native object is created at all;
• CreateGeometry() builds the OGR object only when you ask for one, and the caller owns it;
• FromGeometry(geometry, srid) serialises immediately — the geometry you pass stays yours;
• GetEWkb() hands you the PostGIS wire encoding when you need to feed the bytes to the server yourself, and GetWkb() the same geometry without the SRID;
• the type is immutable, so it is safe to cache, store and pass between threads;
• the database path never touches native GDAL, so reading and writing works without GdalBase.ConfigureAll(). Only CreateGeometry() requires the native libraries.

PgGeometry accepts both the ISO and the PostGIS encoding of Z and M on input, and preserves whatever byte order was used.

Migration from 8.0.x

Version 8.2.0 is a breaking change.

Reading a column now looks like this:

// 8.0.x
var eg = (reader[0] as ExtGeometry)!;
DoSomething(eg.Geometry);          // never disposed

// 8.1.0
var value = reader.GetFieldValue<PgGeometry>(0);
using var geometry = value.CreateGeometry();
DoSomething(geometry);

Fixed along the way:

• M values of POINTM/LINESTRINGM geometries were written as 0;
• the SRID of a geometry was read from the database but never written back to it;
• array mappings (geometry[], geography[]) were registered but never took effect;
• rings and sub-geometries were cloned and leaked while reading collections.