0

I followed this blog [http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-2008-proximity-search-with-th] and did everything which was guided and I am glad things went fine, my table definition is mentioned below and the query I am executing is also mentioned below.

I am using CodePointData data which is downloadable from https://www.ordnancesurvey.co.uk/opendatadownload/products.html

USE [TestDb] GO

/****** Object:  Table [dbo].[PostCode]    Script Date: 08/23/2012 05:32:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PostCode](
    [PostCode] [nvarchar](10) NOT NULL,
    [Coordinates] [geography] NOT NULL,
    [Coordinates1] [geography] NULL,
 CONSTRAINT [PK_PostCode] PRIMARY KEY CLUSTERED
(
    [PostCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)

ON [PRIMARY] ) ON [PRIMARY]

GO



SELECT distinct(left(h.postcode,4))
from postcode g
join postcode h on g.postcode <> h.postcode
and g.postcode ='IG1 4LF'
and h.postcode <> 'IG1 4LF'
Where g.coordinates.STDistance(h.coordinates)<=(1*1609.344)
order by left(h.postcode,4)

When I am running the above query it is giving lots of postcodes which are not part of that Radius. I am verifying my results with another site http://www.freemaptools.com/find-uk-postcodes-inside-radius.htm and this site is saying that 1 Mile of Radius contains postcode IG1,IG2,IG3, but when I am running the above query my results are coming as E12,IG1,IG2,IG3,IG4,IG5,IG6. Which I am unable to find out why it is giving me this much or post codes in just mile of Radius.

please help

Shax
  • 4,207
  • 10
  • 46
  • 62

1 Answers1

0

The distance between longitude lines go to zero as they approach either pole. You are likely assuming that the earth is flat (a flat projection) so that the longitude distance at the equator (or other location) is constant over the entire earth. The flat earth assumption would error would cause distances calculations to be increasing too large nearer the poles.

user2170704
  • 41
  • 2
  • 7