Recently I found a wrapper around the google geocode API here. Which got me thinking about map coordinates of Australian suburbs and being able to do distance from or surrounding suburbs searches for starters.

One thing I found with using this wrapper is that I had to go to google and register for access to use the API (and I was issued a key to use with the wrapper). To register for the key I had to enter the URL that would be making calls to the Google API but I found that the calls worked either from a win forms app or an ASP.NET website both running locally (and in debug mode).

I then found a site that publish a CSV file (amoungst others) with all Australia post codes and their lats and longs here.

The only thing left was to find a routine that calculated the distance between 2 lat and longs. I have attached the SQL CLR user defined function source file to the bottom of this article. 

The only thing left was to create a SQL function that returned the results. Simply pass in the postode you are starting from and the distance you want to search from the postode. I group by postcode and get the max distance because in most instances there postcodes that cover multiple suburbs. This function retrieves the "distinct" list of souurounding suburbs.

I'm sure there can be improvements in my implementation.

GeoDistance.cs (1.17 kb)

Greg Low did an amazing talk at Tech.Ed about full text seach (FTS) in SQL2008. I hadn't had much exposure to FTS so it was good to see what it had to offer. I thought it was just an efficient way to do a like search in a text field. It's so much more. 

For example, FTS out of the box can index may common file formats i.e. doc, xls. You can get the full list of supported document types by running the following query:

select document_type, path from sys.fulltext_document_types

Here's a detailed article on how to setup a FTS catalog. One this that is not all that clear is that on the "Select Table Columns" step, if your FTS column is a data type of image you are required to select another column in the table the holds the document type of the document in the image column. This is how FTS knows which filter to use to index the data.

http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/ 

I found an excellent article that explains the capabilities of FTS. It's SQL2005 specific, however everything (to my knowledge is supported in SQL2008).

http://www.developer.com/db/article.php/3446891 

Things to investigate:

1. How to install and activate other predefined filters (PDF not supported by default),
2. For the hard core, how to write your own filter extension.