Location Based Content - Database

Designing the Database

As this is just an example of how this is going to work, I have decided to take a very simple approach of a single table.  This holds the location information, which I am going to restrict to Plymouth City Centre.

CREATE TABLE dbo.lobacos

      (

      lobaco_id int NOT NULL IDENTITY (1, 1) PRIMARY KEY,

      lobaco_name nvarchar(50) NOT NULL,

      Description nvarchar(MAX) NOT NULL,

      CreationDate datetime NOT NULL DEFAULT (getDate()),

      longitude float(53) NOT NULL,

      latitude float(53) NOT NULL,

      WebUrl nvarchar(50) NULL,

      OfferUrl nvarchar(50) NULL,

      Icon nvarchar(50)NOT NULL DEFAULT 'marker.png',

      Image nvarchar(50) NULL,

      Category nvarchar(50) NOT NULL DEFAULT 'Other'

      )

The main key is the Longitude and Latitude.  To enable to fine granularity required these two are using the Float value and not the Northings and Eastings values.

We need to build a couple of functions to return a number of items when given the location co-ordinates.  I am going use and tweak the code found on NerdDinner (http://nerddinnerbook.s3.amazonaws.com/Part11.htm )

CREATE FUNCTION [dbo].[DistanceBetween](@Lat1 as real,

                @Long1 as real, @Lat2 as real, @Long2 as real)

RETURNS real

AS

BEGIN

DECLARE @dLat1InRad as float(53);

SET @dLat1InRad = @Lat1 * (PI()/180.0);

DECLARE @dLong1InRad as float(53);

SET @dLong1InRad = @Long1 * (PI()/180.0);

DECLARE @dLat2InRad as float(53);

SET @dLat2InRad = @Lat2 * (PI()/180.0);

DECLARE @dLong2InRad as float(53);

SET @dLong2InRad = @Long2 * (PI()/180.0);

DECLARE @dLongitude as float(53);

SET @dLongitude = @dLong2InRad - @dLong1InRad;

DECLARE @dLatitude as float(53);

SET @dLatitude = @dLat2InRad - @dLat1InRad;

/* Intermediate result a. */

DECLARE @a as float(53);

SET @a = SQUARE (SIN (@dLatitude / 2.0)) + COS (@dLat1InRad)

                 * COS (@dLat2InRad)

                 * SQUARE(SIN (@dLongitude / 2.0));

/* Intermediate result c (great circle distance in Radians). */

DECLARE @c as real;

SET @c = 2.0 * ATN2 (SQRT (@a), SQRT (1.0 - @a));

DECLARE @kEarthRadius as real;

/* SET kEarthRadius = 3956.0 miles */

SET @kEarthRadius = 6376.5;        /* kms */

DECLARE @dDistance as real;

SET @dDistance = @kEarthRadius * @c;

return (@dDistance);

END

This “NearestItems” function returns all the items located in a radius of 0.1 miles of the co-ordinates we have given it.

CREATE FUNCTION [dbo].[NearestItems]

      @lat real,

      @long real,

        @num int,

        @distance float

      )

RETURNS  TABLE

AS

      RETURN

      SELECT top (@num) Lobacos.lobaco_id

      FROM   Lobacos

      WHERE  dbo.DistanceBetween(@lat, @long, Latitude, Longitude) < @distance