Harnessing PostGIS in .NET: Building Location-Aware Applications

Spatial data (geographic coordinates, polygons and geometric shapes) opens a world of features such as "find the nearest coffee shop", geo-fencing alerts and mapping visualizations. PostgreSQL, when extended with PostGIS, provides a powerful, open-source spatial database. Combined with .NET and EF Core (via NetTopologySuite), you can effortlessly persist, index and query spatial data.

In this article, we will:

1. Explain PostGIS and its role in handling spatial data.
2. Set up a .NET Core project with EF Core and NetTopologySuite.
3. Design a spatial model for businesses (or users).
4. Configure spatial indexes for performance.
5. Query nearest neighbors using LINQ and raw SQL.
6. Test & Visualize results

What is PostGIS?

PostGIS is a PostgreSQL extension that adds support for geographic objects:

• Geometry & Geography types: Store points, lines and polygons.
• Spatial Functions: Distance calculations (ST_Distance), containment tests (ST_Contains) and more.
• Indexes: GiST and SP-GiST for efficient spatial queries.

With PostGIS, PostgreSQL becomes a full-featured spatial database, removing the need for external GIS systems.

Setting Up .Net Core with EF Core & NetTopologySuite

Add Nuget packages

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

Configure DbContext

using Microsoft.EntityFrameworkCore;
using NetTopologySuite.Geometries;

public class AppDbContext : DbContext
{
    public DbSet<Business> Businesses { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseNpgsql(
            "Host=localhost;Database=geoapp;Username=postgres;Password=mysupersecretpassword",
            o => o.UseNetTopologySuite());
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Configure spatial index
        modelBuilder.Entity<Business>()
            .HasIndex(b => b.Location)
            .HasMethod("GIST");
    }
}

Designing a Spatial Model

Define a simple Business entity with a Point for location.

using NetTopologySuite.Geometries;

public class Business
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Point Location { get; set; }  // Longitude, Latitude
}

Migration will create a geometry(Point, 4326) column in your Businesses table, where SRID 4326 represents WGS84 coordinates.

Populating Sample Data

Seed a few businesses:

var businesses = new[] {
    new Business { Name = "Cafe Azul", Location = new Point(-73.9857, 40.7484) { SRID = 4326 } },
    new Business { Name = "Book Nook", Location = new Point(-73.9876, 40.7492) { SRID = 4326 } },
    // ...more
};

await context.AddRangeAsync(businesses);
await context.SaveChangesAsync();

Querying Nearest Neighbor

Using LINQ with EF Core

// Given a user location
var userLocation = new Point(-73.9860, 40.7489) { SRID = 4326 };

// Find the 5 closest businesses
var nearest = await context.Businesses
    .OrderBy(b => b.Location.Distance(userLocation))
    .Take(5)
    .Select(b => new {
        b.Name,
        DistanceInMeters = b.Location.Distance(userLocation) * 111_319  // approx meters per degree
    })
    .ToListAsync();

EF Core translates Distance()into ST_Distance and generates SQL like:

SELECT name,
       ST_Distance(location, ST_GeomFromText('POINT(-73.9860 40.7489)', 4326))
FROM "Businesses"
ORDER BY location <-> ST_GeomFromText('POINT(-73.9860 40.7489)', 4326)
LIMIT 5;

RAW SQL for Advanced Control

var sql = @"
  SELECT id, name,
         ST_Distance(location::geography, @userPoint::geography) AS dist_meters
  FROM "Businesses"
  ORDER BY location <-> @userPoint
  LIMIT 5;";

var param = new NpgsqlParameter("userPoint", NpgsqlTypes.NpgsqlDbType.Geometry)
{ Value = userLocation };

var closest = await context.Businesses
    .FromSqlRaw(sql, param)
    .ToListAsync();

This uses geography casts for accurate meter-based distances on Earth's curve.

Performance Tips

• Spatial Indexes: Always index geometric/geography columns with GIST.
• Use <-> for KNN: The "distance operator" <-> leverages GiST indexes for fast nearest-neighbor searches.
• Batch Queries: For many points, consider processing in batches or using bounding-box filters (e.g ST_DWithin).

Testing & Visualization

• Integration Tests: Spin up a Dockerized PostGIS instance, seed data, verify nearest neighbor results
• Map Visualization: Return GeoJson and render with Leaflet.js or Mapbox in your frontend

Conclusion

By combining PostgreSQL's PostGIS extension with .NET core, EF Core and NetTopologySuite, you can build powerful, location-aware applications. Whether finding the nearest store, geofencing users or driving spacial analytics, this stack delivers performance and flexibility without licensing costs.