February 10, 2015 at 8:49 am
Hi Eirik,
This is what i have tried from the thread you shared,
with GEO_DATA AS
(
SELECT
DG.Zipcode,
DG.city
,DG.state
,Geography::Point(COALESCE(cast(DG.latitude as float),0),COALESCE(cast(DG.longitude as float),0), 4326) AS GEO_POINT
FROM dbo.ZipCode DG
WHERE ZipCode = '10001'
)
,FINAL_SET AS
(
SELECT
GD.State
,GD.Zipcode
--,(LD.GEO_POINT.STDistance(GD.GEO_POINT) * 0.001) AS DIST_METER
,(GD.GEO_POINT.STDistance(GD.GEO_POINT) * 0.0006213) AS DIST_MILE
from GEO_DATA GD
)
SELECT
FS.Zipcode,
FS.state
,FS.DIST_MILE
FROM FINAL_SET FS
WHERE FS.DIST_MILE < 20;
but it didn't give proper result. am i doing anything wrong here. I suppose to get 143 rows. but i got only one row. please help me on this
February 10, 2015 at 7:24 pm
Hi Eirik,
finally adding spatial index on the geography column help me to speed up the process. am all set. Thanks for your knowledge sharing.
thank you dwain too.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply