Spatial Indexing in sql server

Question

I am trying to migrate from postgres to sql-server(windows). But I have no idea of ms-sql syntax and documentation is also doing me no good. I have a table 'geocodes' storing latitude and longitude data in decimal format. And I have an migration for indexing the lat, long data using postgis

create index index_on_geocodes_location ON geocodes using gist (st_geographyfromtext(((('POINT('::text || longitude) || ' '::text) || latitude) || ')'::text))

I am having a hard time generating a equivalent query for sql-server. According to the documents its pretty easy if the column datatype is geography, but since I already have an existing database with decimal datatype this wont help.

PS: I am using rails migration to execute the queries

Answer 1

Assuming that your use case is executing spatial queries, otherwise there's no point of making spatial index. Spatial index in SQL Server can be created on top of column of spatial data type - geometry (planar) or geography (curved). You could create persisted computed column and create index on it, something like this (assuming geography data type and SRID 4326):

create table Geocodes(
id int identity primary key,
long decimal,
lat decimal,
point as geography::Point(long, lat, 4326) persisted
)

create spatial index SPIX_Geocodes_Point on Geocodes(point) using geography_auto_grid