USE master
GO

IF (EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = 'SpatialPlayground'))
BEGIN
  DROP DATABASE SpatialPlayground
END
GO

CREATE DATABASE SpatialPlayground
GO

USE SpatialPlayground
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE GeoItem
(
	geoItemId INTEGER IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	itemName NVARCHAR(150) NOT NULL,
	latitude NUMERIC(18, 6) NOT NULL,
	longitude NUMERIC(18, 6) NOT NULL
)

IF ISNULL(OBJECT_ID('CalculateGeoExtremas', 'P'), 0) > 0
	DROP PROCEDURE CalculateGeoExtremas
GO

/**
 * Calculates the min/max pair of geo-coordinates (lat/lon pairs) from
 * the specified geo-coordinate given the specified distance.
 */ 
CREATE PROCEDURE CalculateGeoExtremas
	@MinLat NUMERIC(18, 6) OUTPUT,
	@MinLon NUMERIC(18, 6) OUTPUT,
	@MaxLat NUMERIC(18, 6) OUTPUT,
	@MaxLon NUMERIC(18, 6) OUTPUT,
	@Latitude NUMERIC(18, 6),
	@Longitude NUMERIC(18, 6),
	@Distance NUMERIC(18, 6)
AS
BEGIN
	-- Constant Conversion Factor: 1 degree of latitude ~= 69 miles
	DECLARE @MILE_CONVERSION NUMERIC(18, 6);
	SET @MILE_CONVERSION = 69.0;

	SET @MinLat = @Latitude - (@Distance / @MILE_CONVERSION);
	SET @MinLon = @Longitude - (@Distance / ABS(COS(RADIANS(@Latitude)) * @MILE_CONVERSION));
	
	SET @MaxLat = @Latitude + (@Distance / @MILE_CONVERSION);
	SET @MaxLon = @Longitude + (@Distance / ABS(COS(RADIANS(@Latitude)) * @MILE_CONVERSION));

	RETURN
END
GO

IF ISNULL(OBJECT_ID('CalculateDistance', 'FN'), 0) > 0
	DROP FUNCTION CalculateDistance
GO

/**
 * Checks if the specified CalendarEvent is a RideEvent
 * @return True if the specified CalendarEvent is a RideEvent; otherwise false
 */ 
CREATE FUNCTION CalculateDistance(
	@OriginLatitude NUMERIC(18, 6),
	@OriginLongitude NUMERIC(18, 6),
	@DestinationLatitude NUMERIC(18, 6),
	@DestinationLongitude NUMERIC(18, 6)
)
RETURNS NUMERIC(18, 6)
AS
BEGIN
	RETURN 3956 * 2 * ASIN(SQRT( 
		POWER(SIN((@OriginLatitude - @DestinationLatitude) * PI() / 180 / 2), 2) 
		+ 
		COS(@OriginLatitude * PI() / 180) 
		* 
		COS(@DestinationLatitude * PI() / 180)
		*
		POWER(SIN((@OriginLongitude - @DestinationLongitude) * PI() / 180 / 2), 2)
	))
END
GO

INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Yaupon Creek', 30.36353, -97.96862)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Windy Point', 30.4152, -97.90084)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Winns Heliport', 30.40131, -97.96029)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('West Ridge Middle School', 30.30382, -97.8914)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('White Rock Cemetery', 30.30993, -97.96251)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Wild Basin Wilderness Park', 30.30993, -97.82167)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Renaissance Austin Hotel', 30.3933, -97.7496)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Zachary T and Sallie Lee Scott Senior House', 30.30271, -97.77945)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('West Bull Creek', 30.35937, -97.78779)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('West Bull Creek Preserve', 30.38131, -97.80251)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('West Lake Hills', 30.29798, -97.80195)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Westminster Presbyterian Church', 30.30743, -97.7639)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Woodcliff Baptist Church', 30.36409, -97.66862)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('William Braxton Barr House', 30.35159, -97.6475)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Winn Elementary School', 30.3141, -97.66611)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('North Austin Plaza Hotel and Suites', 30.3296, -97.7051)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('La Quinta Inn Austin Highland Mall', 30.3189, -97.7076)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Embassy Suites Hotel Austin-North', 30.3197, -97.7066)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Hampton Inn Austin North', 30.3374, -97.7004)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Young Chapel African Methodist Episcopal Chur)ch', 30.32771, -97.70139)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Westview Middle School', 30.41631, -97.68917)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Wheless Lane Church of Christ', 30.3091, -97.68028)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Red Bud', 30.29798, -97.80834)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Northcross Suites Austin Nw', 30.3535, -97.7356)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Habitat Suites', 30.3285, -97.7104)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Esa Austin-Arboretum', 30.3904, -97.7366)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Esd Austin-North Central', 30.345, -97.6959)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Vintage Villas', 30.4039, -97.9312)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Springhill Suites By Marriott Austi)n North', 30.4051, -97.6753)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Fairfield Inn And Suites By Marriott Austin Northwest', 30.3988, -97.7304)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Quality Inn & Suites', 30.3492, -97.5343)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Casa Lago Resort', 30.4177, -98.0149)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Courtyard by Marriott Downtown Conven)tion Center', 30.36, -97.8)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Courtyard by Marriott Austin Airport', 30.36, -97.8)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Courtyard by Marriott Austin Arboretum', 30.3852, -97.7417)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Best Western Atrium North', 30.3431, -97.7031)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Towneplace Suites By Marriott Austin Northwest)', 30.3906, -97.7383)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Ossiport Heliport', 30.32382, -97.86806)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('KUT-FM', 30.31437, -97.8664)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Texas Military Forces Museum', 30.31076, -97.75973)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('The Brown School', 30.32687, -97.74167)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('The Specialty Hospital of Austin', 30.31215, -97.74306)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Spicewood Spring', 30.36298, -97.74806)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Steck Valley Park', 30.38659, -97.75834)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Seiders Spring', 30.30576, -97.74723)
INSERT INTO GeoItem(itemName, latitude, longitude) VALUES('Seton Medical Center H-4 Heliport', 30.30437, -97.74612)