question

shridhar avatar image
shridhar asked

get address from geography

Hi I have two tabale 1) table1 contain id (bigint) location (geoghrphy) name (varchar(400) lat (varchar(40) lng (varchar(40) 1 0xE6100000010CFC dadar mumbai mh 18.5555 70.5878 2) table2 contain deviceid (bigint) lat (varchar(40) lng (varchar(40) adrgeo (geoghrphy) 1 18.5555 70.5878 0xE6100000010CFC 1 18.5655 70.5678 0xE878700010CFC 1 18.7316 70.1234 0xE78784000010CFC and so on table 1 have more than 5 lac entries and table 2 have 20000 row I want find nearest location name from lat long Output will be name lat lng dadar mumbai mh 18.5555 70.5878 Pune 18.5655 70.5678 Kothrud Pune mh 18.7316 70.1234 I did 1) SELECT c.*, fnc.name AS fName FROM table2 c CROSS APPLY ( SELECT TOP 1 name FROM table1 WITH(index(SpatialIndex_Area)) WHERE location.STDistance(adrgeo) <50 ORDER BY location.STDistance(adrgeo) ) fnc But It take 3 minute to get output of 2000 rows kindly suggest best solution for this
sql-server-2008sql server 2014rowgeographylatitude-longitude
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.