How to add spatial index to a table with geography column

Question

I have a table on azure sql database (hosted instance). I am trying to set a spatial index to a geography column.

Query:

=================================== Index query ===================================
CREATE SPATIAL INDEX sp_idx ON Routes(Route_st_way_end) USING GEOGRAPHY_GRID WITH (GRIDS = (LEVEL_1 = LOW, LEVEL_2 = LOW, LEVEL_3 = HIGH, LEVEL_4 = HIGH), CELLS_PER_OBJECT = 16, DROP_EXISTING = ON)

Error :

Msg 12009, Level 16, State 2, Line 1 Could not find the spatial index 'sp_idx' on table 'Routes'. Either no spatial index with this name exists, or a non-spatial index might be using the same name. Fix the index name, avoiding duplicates. If a relational index has the same name, drop the regular relational index.

Create Table Script :

=================================== Table Script ===================================
CREATE TABLE [dbo].[Routes] (
    [route_id]               INT               IDENTITY (1, 1) NOT NULL,
    [driverPostsTrip_id]     INT               NOT NULL,
    [Route_st_way_end]       [sys].[geography] NOT NULL,
    [TotalDuration]          FLOAT (53)        NOT NULL,
    [TotalDistance]          FLOAT (53)        NOT NULL,
    [TripDeparture_DateTime] DATETIME          NOT NULL,
    [TripArrival_DateTime]   DATETIME          NOT NULL,
    PRIMARY KEY CLUSTERED ([route_id] ASC),
    FOREIGN KEY ([driverPostsTrip_id]) REFERENCES [dbo].[DriverPostsTrip] ([driverPostsTrip_id])
);

I don't understand why is it erroring out.

Note: I am using Azure data studio on Mac (I don't have a way to use SSMS).

Please check the attached screenshot for details around the error and table design.

Answer 1

Hi @Sunraj sharma

Notice that there is DROP_EXISTING = ON in your code, which specifies that the named, preexisting spatial index is dropped and rebuilt. The default is OFF.

With this option, a matching index (of correct type) needs to exist otherwise you will get this error. You could think of CREATE INDEX WITH DROP_EXISTING = ON as an ALTER INDEX operation.

If you are creating the index from scratch on a table, then you need to omit the DROP_EXISTING = ON option.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Thanks for posting your question in the Microsoft Q&A forum.

The error message indicates that the index name "sp_idx" is either already being used by a non-spatial index on the "Routes" table.

Check if there is an existing non-spatial index on the "Routes" table with the name "sp_idx". If so, you'll need to drop that index first before creating the new spatial index.

I hope you can identify the issue with the query below:

SELECT 
       t.name TableName,
       ind.name IndexName
FROM 
     sys.indexes ind 
INNER JOIN 
     sys.index_columns ic 
	 ON  ind.object_id = ic.object_id 
	 and ind.index_id = ic.index_id 
INNER JOIN 
     sys.tables t 
	 ON ind.object_id = t.object_id 
WHERE 
     ind.is_primary_key = 0 

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful