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? :

    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).

[AntoCanza]

@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).

modelBuilder .HasIndex(e => e.Shape) .HasMethod("GIST");

indeed with only HasIndex you get a btree

[jamesra]

Apparently I'm a rare SQL spatial user. There are a lot of tunable parameters in a MS-SQL spatial index. Would EF Core support specifying the extent of the spatial coordinates or is that a follow up direct SQL query in a migration?

A current workaround for this issue is a migration with raw SQL to create the index:

migrationBuilder.Sql(@"  CREATE SPATIAL INDEX [MosaicShape_Index] ON [dbo].[Location]
                    (
	                    [MosaicShape]
                    ) USING  GEOMETRY_AUTO_GRID 
                    WITH (BOUNDING_BOX =(0, 0, 150000, 150000), 
                    CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                    GO");```

[MithrilMan]

is there still no support for spatial indexes in EF Core? In my scenario I've to create an index for locations (lat,lon) Think about having a table with Points of Interests

I'm using NetTopologySuite with a custom type and a converter but using HasIndex gives error, so what should I do beside using RAW SQL ?

[roji]

For now, using raw SQL is the way to go. This issue has only received 10 votes up to now, which is one reason we haven't been able to prioritize it.

[bjCorrosionInstruments]

Is there a work around for creating a mock database from my DbContext using spatial indexing? I'm thinking maybe working around (removing) the spatial indexes during database creation to avoid errors and then recreate them on the mock database after using RawSQL.

[roji]

@bjCorrosionInstruments yes, that approach would likely work; simply don't define your indexes in EF Core's model, and use SQL in your migrations to create them.

[ronaldhoek]

Is there a work around for creating a mock database from my DbContext using spatial indexing? I'm thinking maybe working around (removing) the spatial indexes during database creation to avoid errors and then recreate them on the mock database after using RawSQL.

You could try to detect the database type during creation and skip the spatial index creation script, when its not supported by the database type

[ronaldhoek]

Apparently I'm a rare SQL spatial user. There are a lot of tunable parameters in a MS-SQL spatial index. Would EF Core support specifying the extent of the spatial coordinates or is that a follow up direct SQL query in a migration?

A current workaround for this issue is a migration with raw SQL to create the index:

migrationBuilder.Sql(@"  CREATE SPATIAL INDEX [MosaicShape_Index] ON [dbo].[Location]
                    (
	                    [MosaicShape]
                    ) USING  GEOMETRY_AUTO_GRID 
                    WITH (BOUNDING_BOX =(0, 0, 150000, 150000), 
                    CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                    GO");```

One could start with the basics like:

• no parameters for geography type
• only BBOX parameter for geometry type