Efcore: SQL Server Spatial Indexes

[bricelam]

After #1100 is implemented, we should consider allowing spatial indexes to be defined on spatial columns. My initial thought is to promote (by convention) regular indexes defined on these columns to spatial indexes, and for backends that support additional facets, we should expose Fluent API.

[bricelam]

Reference:

• PostgreSQL (via PostGIS)
• SQL Server
• SQLite (via SpatiaLite)

[roji]

I don't know much about the other databases, but at least for PostgreSQL I'm not sure that spatial indexes require any special handling beyond what exists for regular indexes... Npgsql already supports specifying the index method (gist vs. brin etc.), is anything else required as far as you know?

[bricelam]

Don't know, but that seems sufficient enough for now. Unfortunately, SQL Server and SQLite require entirely different DDL.

[roji]

Ah, got it...

Things always seem so complicated in those evil parallel universes...

[garfbradaz]

@roji is it a simple case of this when declaring a spatial index of using HasIndex or do I need to use ForNpgsqlHasIndex instead? :

```c#
public class Port
{
public Guid Id {get; set;}
public Point Location { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
       if (this.Database.ProviderName == "Npgsql.EntityFrameworkCore.PostgreSQL")
        {
            modelBuilder.HasPostgresExtension("postgis");
            modelBuilder.Entity<Port>()
                        .HasIndex(l => l.Location);
        }
 }

```

[roji]

@garfbradaz according to the docs, the same DDL is used to create indexes on PostGIS objects - so that should be fine. You may need to specify the index method (GIST) but that's already supported (ForNpgsqlHasMethod).