2

I have a list of stores with their post codes. What I need to do is when a user types in a postcode on the webpage it search the db and then finds the closest 10 to that postcode.

example of data i have:

ID  = 1
Store =Halfords 
Address 1 =Unit 6b
Address 2 = Braidfute Retail Park   
Address 3   = Lanark    
Address 4 =South Lanarkshire
Postal Code = ML11 9AE
Lat = 55.6711692810059
Long = -3.77314400672913

I am able to convert the postcode into longitude and latitude, what query do i need to run to find stores within 10miles in ym table of this cord

Beginner
  • 25,463
  • 62
  • 148
  • 228
  • Do you have a data source that knows the location of postcodes? – Marc Talbot Mar 09 '12 at 15:05
  • 2
    Postal codes by themselves won't be of much use unless you have corresponding Geo coordinates. Do you have those? – George Johnston Mar 09 '12 at 15:05
  • All I have is a database table with a store name and next to it a postcode and lat and long – Beginner Mar 09 '12 at 15:06
  • 1
    I would recommend looking at this SO post to solve your issue: http://stackoverflow.com/questions/8907873/distance-between-two-points-using-geography-datatype-in-sqlserver-2008 – George Johnston Mar 09 '12 at 15:10
  • Postcode to lat/long is available for free from ordanance survey; http://www.ordnancesurvey.co.uk/oswebsite/products/code-point-open/index.html – Alex K. Mar 09 '12 at 15:10
  • sorry i need further help understanding how to use that function as im not good with stored procdures etc – Beginner Mar 09 '12 at 15:19

2 Answers2

7

Firstly, you'll need the lat/lon of each postcode and store those in a table along with a geospatial representation (GEOGRAPHY, or GEOMETRY datatype). When a post code is entered by the user, you can the look up that post code in your db to find it's location.

You'll also need to store a GEOGRAPHY/GEOMETRY representation of each of the stores lat/lons you currently have.

Based on that, you can use all of SQL Server's geospatial functionality to perform geospatial searches.

I've made available on GitHub a .NET app that will import the free Ordnance Survey postcode dataset (all post codes in GB) into SQL Server, converting all the Eastings/Northings provided by it, into lat/lon and then creating a geography column. I have a full blog post on that here. The end result would be used like this:

DECLARE @home GEOGRAPHY
SELECT @home = GeoLocation 
FROM dbo.PostCodeData 
WHERE OutwardCode = 'AB12' AND InwardCode = '3CD' -- Postcode entered by user

-- find all stores within 10 miles of the entered postcode
SELECT *
FROM dbo.Stores
WHERE GeoLocation.STDistance(@home) <= (10 * 1609) -- 1609 = approx metres in 1 mile

Hopefully, this will be a good starting point.

AdaTheDev
  • 128,935
  • 26
  • 185
  • 187
  • sorry i still dont understand, i have the longitude and latitude of the entered post code, what query should i run against my table to find the items within 10miles? – Beginner Mar 09 '12 at 15:37
  • You need to convert those lat/lon coordinates into an new column on your "store" table with a GEOGRAPHY datatype. Then you need a new table to hold the coordinates of every GB postcode (see my linked blog post). Once you have those, you can then run the query like I gave at the end of my answer. You need to read up on geospatial support in SQL Server first, get an understanding of that. Some good links here: http://stackoverflow.com/questions/551894/whats-the-best-way-to-store-co-ordinates-longitude-latitude-from-google-maps – AdaTheDev Mar 09 '12 at 15:44
  • Fantastic answer this will save me a lot of time. Thank you. – Grenter Apr 17 '13 at 21:12
1

You'll want to start with Geospatial functionality in SQL Server.

CAbbott
  • 7,998
  • 4
  • 29
  • 38