Setting up Entity Framework  to work with geographical distances
Tags: csharp, database, postgres
Published: 2020-11-14 19:43:10

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] (https://www.npgsql.org/efcore/mapping/nts.html) and the steps are easy to follow. 

#### Using `docker compose`
```yaml
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](https://github.com/NetTopologySuite/NetTopologySuite).
- `Microsoft.EntityFrameworkCore`
- `Microsoft.EntityFrameworkCore.Proxies` <- [Lazy loading](https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.proxiesextensions.uselazyloadingproxies?view=efcore-5.0)
- `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](https://www.npgsql.org/efcore/mapping/nts.html#geography-geodetic-support) (Capitalisation is for emphasis only). 
There is supposedly an extra CPU cost to calculating queries against Geographic data, but it's negligible.

```csharp
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. 

```csharp
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`:
```csharp
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](https://www.npgsql.org/efcore/mapping/nts.html#operation-translation). 

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

Hope this helps!