CODEWITHSUNDEEP
sql-serverdatabasealgorithmgoogle-mapsgeolocation
Amrit
Amrit

Reputation: 421

Indexing latitude and longitude SQL Server 2008

Hi I have database table struture Like following table - ItemLocation

ItemId varchar(10)          ItemGeo (geography)
123n13                      POINT (-79.660470500000017 43.5482682)
n4f923                      POINT (-79.7511776 43.6575816)

and more rows.......................

And i am using a simple insert to insert the data now, But actually i want to insert the rows by location so that Items which are close will stay together in the database so If a try to insert a new item 'q3eqwe2' which is more close to 123n13 so it will go next to 123n13 and replace n4f923 index and all the bottom rows will move to index + 1. Is this feasible ?

ItemId varchar(10)          ItemGeo (geography)
    123n13                      POINT (-79.660470500000017 43.5482682)
    q3eqwe2                     POINT (-79.66039911221 43.5479121)
    n4f923                      POINT (-79.7511776 43.6575816)

If it is feasible i dont want to go through all the rows checking ItemGeo column to find the perfect index for new row, so can I add new column which will Index on the basis of the Item location and i can easily find the closest location match to insert? Hope it make sense :)

Upvotes: 0

Views: 338

Answers (1)

podiluska
podiluska

Reputation: 51494

I'm not sure it does make sense. Effectively with a geography type, you're asking for a two dimensional index mapped onto a globe. If A is close to B, and B is close to C, A could be close to C, or far from C, and then we're into the travelling salesman problem. Where does your index start and end?

You can create a spatial index on your geography type. I think that's the best option.

The root of the question is why do you think you need to?

Upvotes: 1

Related Questions