Olawale Lawal
Writingtransmissions / 011

PostGIS with EF Core: Building Location-Aware .NET Applications

7 min read.Net CorePostgreSQLspatialEF Core

PostGIS with EF Core: Building Location-Aware .NET Applications

“Find the nearest branch.” “Is this delivery address inside the service zone?” “Show me every agent within two kilometres.” Each of those is a one-line SQL query if your database understands geometry, and a slow, wrong, hand-rolled Haversine loop if it doesn’t.

PostgreSQL with the PostGIS extension understands geometry. EF Core talks to it through Npgsql and a geometry library called NetTopologySuite (NTS), and once the three are wired together you write spatial queries in LINQ and get indexed SQL out the other end. This post sets that up from scratch, covers the two decisions that trip most people up (geometry versus geography, and which way round longitude and latitude go), and finishes with the errors you’ll see in your first week.

What PostGIS adds

PostGIS is an extension, enabled per database, that adds:

  • Column types. geometry for shapes on a flat plane and geography for shapes on the Earth’s surface. Points, lines, polygons, and collections of each.
  • Functions. ST_Distance, ST_DWithin, ST_Contains, ST_Intersects, ST_Buffer and a few hundred more.
  • Index support. GiST indexes over spatial columns, which is what makes “nearest 5 of a million rows” fast.

Nothing in your application needs to know any of that directly. EF Core translates NTS method calls into the ST_* functions, and the migration creates the columns and indexes.

Setup

You need a PostGIS-enabled Postgres. The official image is the easiest way:

# docker-compose.yml
services:
  db:
    image: postgis/postgis:16-3.4
    environment:
      POSTGRES_DB: geoapp
      POSTGRES_PASSWORD: dev
    ports:
      - "5432:5432"

Then the packages:

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL.NetTopologySuite

The second package is the one that teaches Npgsql how to map NTS types and translate their methods. Without it, Point is just an unknown CLR type and you’ll get a “could not be mapped” error at model building.

DbContext

using Microsoft.EntityFrameworkCore;
using NetTopologySuite.Geometries;

public class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
    public DbSet<Business> Businesses => Set<Business>();
    public DbSet<ServiceZone> ServiceZones => Set<ServiceZone>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Makes the migration emit CREATE EXTENSION IF NOT EXISTS postgis
        modelBuilder.HasPostgresExtension("postgis");

        modelBuilder.Entity<Business>(b =>
        {
            b.Property(x => x.Location).HasColumnType("geography (point)");
            b.HasIndex(x => x.Location).HasMethod("GIST");
        });

        modelBuilder.Entity<ServiceZone>(z =>
        {
            z.Property(x => x.Area).HasColumnType("geography (polygon)");
            z.HasIndex(x => x.Area).HasMethod("GIST");
        });
    }
}

And the registration in Program.cs:

builder.Services.AddDbContext<AppDbContext>(o =>
    o.UseNpgsql(builder.Configuration.GetConnectionString("Default"),
                npgsql => npgsql.UseNetTopologySuite()));

Two lines in there do most of the work. HasPostgresExtension("postgis") means a fresh database gets the extension created by your migration instead of by a DBA remembering to run it. And HasColumnType("geography (point)") is the decision we need to talk about.

Geometry or geography?

If you skip the HasColumnType call, Npgsql maps Point to geometry. Geometry means a flat Cartesian plane. Distances come back in the units of your coordinate system, and if your coordinates are longitude and latitude (SRID 4326), that unit is degrees. A degree of longitude is about 111 km at the equator and about 55 km at 60° north, so “multiply by 111,319 to get metres” is only right along the equator and gets worse the further you move from it. The first version of this post made exactly that mistake.

Geography treats coordinates as points on a spheroid. Distance returns metres. DWithin takes metres. It costs a little CPU per comparison, and some functions (area, buffer) are either slower or unavailable, but for anything involving real-world locations and “how far” it’s the right default.

Use geometry when you’re working in a projected coordinate system already in metres (a national grid, for example), when you need the full function catalogue, or when you’re doing pure computational geometry that has nothing to do with the planet.

The model

using NetTopologySuite.Geometries;

public class Business
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public required Point Location { get; set; }   // X = longitude, Y = latitude
}

public class ServiceZone
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public required Polygon Area { get; set; }
}

Read the comment on Location twice. NTS uses mathematical convention: X is the horizontal axis, which is longitude; Y is vertical, latitude. Most people say “lat, long” out loud, most map APIs return {lat, lng} objects, and new Point(lat, lng) compiles without complaint and puts every one of your businesses in the ocean somewhere off the coast of Somalia. A small factory method saves you:

public static class Geo
{
    private static readonly GeometryFactory Factory =
        NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326);

    public static Point Point(double longitude, double latitude) =>
        Factory.CreatePoint(new Coordinate(longitude, latitude));
}

Using a factory with the SRID baked in also means you never forget to set SRID = 4326, which is the other way to get a confusing error (more on that below).

Seeding a few rows:

db.Businesses.AddRange(
    new Business { Name = "Cafe Azul",  Location = Geo.Point(3.3792, 6.5244) },
    new Business { Name = "Book Nook",  Location = Geo.Point(3.3841, 6.5201) },
    new Business { Name = "Night Pharm", Location = Geo.Point(3.3610, 6.5310) });

await db.SaveChangesAsync();

Nearest-neighbour queries

var here = Geo.Point(3.3800, 6.5250);

var nearest = await db.Businesses
    .OrderBy(b => b.Location.Distance(here))
    .Take(5)
    .Select(b => new
    {
        b.Name,
        Metres = b.Location.Distance(here),
    })
    .ToListAsync();

Because the column is geography, Metres is actually metres. Check what SQL this produces with ToQueryString(); you should see the <-> operator in the ORDER BY:

SELECT b."Name", ST_Distance(b."Location", @__here_0) AS "Metres"
FROM "Businesses" AS b
ORDER BY b."Location" <-> @__here_0
LIMIT 5

<-> is the index-assisted KNN distance operator. With a GiST index on the column, Postgres walks the index to find the closest rows instead of computing the distance to every row and sorting. On a table of a few thousand rows you won’t notice the difference. On a few million you’ll notice the absence.

“Nearest five” with no radius will happily return five businesses 400 km away when there’s nothing close. Usually you want “nearest five within N metres”, and that’s IsWithinDistance, which translates to ST_DWithin:

var nearby = await db.Businesses
    .Where(b => b.Location.IsWithinDistance(here, 2_000))   // metres, because geography
    .OrderBy(b => b.Location.Distance(here))
    .Take(5)
    .ToListAsync();

ST_DWithin is also index-aware, so the filter and the ordering both use the GiST index. This is the query shape you want for almost every “near me” feature.

Geofencing: is this point inside the zone?

var address = Geo.Point(3.3755, 6.5180);

var zone = await db.ServiceZones
    .FirstOrDefaultAsync(z => z.Area.Intersects(address));

Intersects translates to ST_Intersects, which is index-assisted. Contains works too and maps to ST_Covers for geography, but Intersects is the one that handles points exactly on the boundary the way users expect. To build a polygon from the ring of coordinates your admin draws on a map:

var ring = coords                           // IEnumerable<(double lng, double lat)>
    .Select(c => new Coordinate(c.lng, c.lat))
    .Append(new Coordinate(coords[0].lng, coords[0].lat))   // rings must be closed
    .ToArray();

var polygon = Factory.CreatePolygon(ring);

The ring has to be closed: first coordinate equals last. NTS throws if it isn’t, which is better than Postgres silently rejecting it later.

Raw SQL when LINQ gets in the way

Most spatial queries translate cleanly, but when you want ST_Buffer, ST_Centroid, or a function the provider doesn’t know about, drop to SQL:

var sql = """
    SELECT b."Id", b."Name", b."Location"
    FROM "Businesses" b
    WHERE ST_DWithin(b."Location", {0}, {1})
    ORDER BY b."Location" <-> {0}
    LIMIT 5
    """;

var closest = await db.Businesses
    .FromSqlRaw(sql, here, 2_000.0)
    .ToListAsync();

Npgsql knows how to send an NTS Point as a parameter, so no manual WKT formatting. Keep the parameters as parameters; string-interpolating coordinates into SQL is both an injection risk and a culture bug waiting to happen (decimal commas).

Returning results as GeoJSON

If anything on a map is going to consume your API, return GeoJSON rather than inventing your own {lat, lng} shape. Add NetTopologySuite.IO.GeoJSON4STJ and register its converter:

builder.Services.ConfigureHttpJsonOptions(o =>
    o.SerializerOptions.Converters.Add(new NetTopologySuite.IO.Converters.GeoJsonConverterFactory()));

Now any Point or Polygon on a response object serialises as a GeoJSON geometry, and Leaflet or Mapbox will draw it without any translation code on the front end. Without that converter, System.Text.Json tries to walk the NTS object graph, hits a cycle between Geometry and Factory, and throws.

Testing

Mocking a spatial database is pointless; the behaviour you care about lives in Postgres. Spin up a real one per test run with Testcontainers:

public sealed class PostgisFixture : IAsyncLifetime
{
    public PostgreSqlContainer Container { get; } = new PostgreSqlBuilder()
        .WithImage("postgis/postgis:16-3.4")
        .Build();

    public Task InitializeAsync() => Container.StartAsync();
    public Task DisposeAsync() => Container.DisposeAsync().AsTask();
}

That needs the Testcontainers.PostgreSql package and a running Docker daemon. In a test, build a context against Container.GetConnectionString(), run MigrateAsync(), seed three points, and assert the nearest-neighbour query returns them in the order you expect. It’s the only kind of test that will catch a swapped longitude and latitude, and it costs a few seconds of container start-up per run.

Performance notes, and when PostGIS is more than you need

  • Index every spatial column you filter or order on, with HasMethod("GIST"). Without it, every query is a sequential scan with a trig function per row.
  • Prefer IsWithinDistance plus OrderBy(Distance) over OrderBy(Distance) alone. Both use the index; the first also bounds the work.
  • For “which zone contains each of these 10,000 points”, do it in one query with a join on Intersects, not 10,000 queries.
  • VACUUM ANALYZE after bulk loading. The planner needs statistics on the geography column to choose the index.
  • If you need to run many distance calculations against a fixed set of points at high rate, and they fit in memory, NTS can do the maths in-process; Postgres isn’t the only place Distance works.

That last point generalises. A few hundred locations and one “nearest” feature don’t justify an extension and a column type; plain longitude and latitude columns plus NTS in memory is less to operate. SQL Server users get an equivalent geography type through Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite, and everything about NTS above applies there unchanged. And PostGIS gives you straight-line distance only. Drive times, routing and isochrones are pgRouting or an external engine such as OSRM or Valhalla, no matter how good your indexes are.

Troubleshooting

type "geography" does not exist or type "geometry" does not exist. The extension isn’t enabled in that database. Add HasPostgresExtension("postgis") and create a new migration, or run CREATE EXTENSION postgis; by hand.

Operation on mixed SRID geometries. One side of the comparison has SRID 4326 and the other has 0. Usually a Point created with new Point(x, y) instead of through a factory with the SRID set. Use the Geo.Point helper above everywhere, including in tests.

Every distance is roughly 0.01 or every result is thousands of kilometres away. Either the column is geometry and you’re reading degrees, or longitude and latitude are swapped. Check the column type in the migration, then check the order of arguments at every call site.

The entity type 'Point' requires a primary key. The NetTopologySuite plugin isn’t registered. Add npgsql => npgsql.UseNetTopologySuite() to UseNpgsql.

JsonException: A possible object cycle was detected. Serialising an NTS geometry without the GeoJSON converter. Register GeoJsonConverterFactory, or project to plain numbers in your Select.

Query is slow and EXPLAIN shows Seq Scan. No GiST index, or the planner has no statistics. Check the index exists in the migration, then ANALYZE the table.

Migration fails with permission denied to create extension. Creating extensions needs superuser or the postgis extension marked trusted. On managed Postgres (RDS, Cloud SQL, Azure) enable it through the provider’s console or with the admin account, then remove HasPostgresExtension if your app user can’t create it.