Setting up Entity Framework to work with geographical distances

This is a post about a simple solution to a simple problem:

Find distances between things in a database using .net core

Here's how I did it.

The requirements:

.Net Core 3.1 with Entity Framework

This is the base stack.

A Linux database

I couldn't use MSSQL as I'm restricted to Linux based docker images.

Server evaluated geographical distances

I need to find the distances between two points on the database side. Over-returning results and then filtering isn't ideal for anything.

The solution

Database - PostGIS a.k.a Postgres + GIS

It took a few goes (MySQL, Sqlite and then Postgres), but Postgres was the winner in getting things to work actually work and easily work with distance.
npgsql.org has very good documentation and the steps are easy to follow.

Using docker compose

version: '3.1'
services:
  db:
    image: postgis/postgis
    restart: always
    environment:
      POSTGRES_PASSWORD: mysecretpassword_shhhh
    ports:
     - 5432:5432
  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

The connection string for this setup is:
"Server=localhost;Database=mydatabase;Uid=postgres;Pwd=mysecretpassword_shhhh;"

Entity Framework and NetTopologySuite

Install Packages

• NetTopologySuite <- "NTS" Provides Points and other spatial types.
• Microsoft.EntityFrameworkCore
• Microsoft.EntityFrameworkCore.Proxies <- Lazy loading
• Npgsql.EntityFrameworkCore.PostgreSQL
• Npgsql.EntityFrameworkCore.PostgreSQL.NetTopologySuite <- "NTS " Adapter

Use Points for your locations

If you are using Lat/Lon co-ordinates, then the points should be stored in Postgres as geoGRAPHY and not geoMETRY (Capitalisation is for emphasis only).
There is supposedly an extra CPU cost to calculating queries against Geographic data, but it's negligible.

using Point = NetTopologySuite.Geometries.Point;
public class MyEntity
{ 
   [Key] public int Id { get; set; }

   // Set the column type either here or in the context. 
   // NOTE: The type is geoGRAPHY not geoMETRY
   [Column(TypeName="geography")] 
   [Required] public Point Location { get; set; }
}

Setup your EF database context

The postgis extension is required for geometry and geographical types and is installed per database. The call to HasPostgresExtension ensures that this is installed.

public class MyPostgresContext: MyDatabaseContext
{
public PostgresMyContext(DbContextOptions options) : base(options){}
protected override void OnModelCreating(ModelBuilder builder)
{
base.OnModelCreating(builder);
builder.HasPostgresExtension("postgis");

// Equivalent to using [Column(TypeName="geography")] 
// NOTE: The type is geoGRAPHY not geoMETRY
builder.Entity<MyEntity>().Property(b => b.Location).HasColumnType("geography (point)");
}

And in your Startup.cs:

public void ConfigureServices(IServiceCollection services)
{
services
 .AddDbContext<MyDatabaseContext, MyPostgresContext>(builder =>
 {
     builder.UseNpgsql(CONNECTION_STRING, x => x.UseNetTopologySuite());
     builder.EnableDetailedErrors();
     builder.UseLazyLoadingProxies();     
 }) 
}

Query all the things

With this all in place, you can use the methods that deal with distance, or many other things.

var distanceInMetres = 1000; // 1 km
var entities = context.MyEntities.Select(x => x.Location.Distance(new Point(1,1)) <= distanceInMetres) .ToArray();

Hope this helps!