4

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

Abhinav Khare
  • 96
  • 1
  • 7

1 Answers1

9

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
Mladen Andzic - Msft
  • 141
  • 2
  • Why are you using persisted in table creation? – Junior Usca Oct 07 '20 at 17:22
  • 1
    Spatial index can be created on a computed column only if it is persisted. – Mladen Andzic - Msft Oct 08 '20 at 18:23
  • The "persisted" option is required only for computed columns that need to be indexed and will double the required storage requirement. Just store the lat and long data directly into the geography datatype there is no need for the "persisted". You can retrieve back the separate values using "Point.lat" and "Point.long" – Fábio Lobão Jul 07 '21 at 13:59