USE SpatialDemo

GO

EXEC sp_configure 'clr enabled', '1';
GO

RECONFIGURE
GO

ALTER DATABASE SpatialDemo SET TRUSTWORTHY ON;

CREATE ASSEMBLY ProSpatialCh06
FROM 'E:\SQLBackups\ProSpatialCh06\ProSpatialCh06.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE FUNCTION dbo.Geocode(
	@addressLine nvarchar(max),
	@locality nvarchar(max),      --city?
	@adminDistrict nvarchar(max), --state?
	@postalCode nvarchar(max),
	@countryRegion nvarchar(max)
) RETURNS geography
AS EXTERNAL NAME
ProSpatialCh06.[ProSpatial.Ch6.UserDefinedFunctions].GeocodeUDF;

GO

SELECT dbo.Geocode('223 Spring Street', 'New York', 'NY', '10013', 'USA').ToString();

GO

CREATE FUNCTION dbo.GeocodeTVF(
	@addressLine nvarchar(255),
	@locality nvarchar(255),
	@adminDistrict nvarchar(255),
	@postalCode nvarchar(255),
	@countryRegion nvarchar(255)
) RETURNS table (Name nvarchar(255), Point geography, BoundingBox geography)
AS EXTERNAL NAME
ProSpatialCh06.[ProSpatial.Ch6.UserDefinedFunctions].GeocodeTVF;

GO

SELECT * 
FROM dbo.GeocodeTVF('1600 Pennsylvania Avenue', '', '', '', 'USA');

GO

/*
--Although this is in the book, we didn't do it as it basically ends the chapter without using it. 

CREATE TABLE Addresses (
	AddressID int identity(1,1),
	Address nvarchar(255),
	Location geography
);

GO


CREATE TRIGGER tgGeocodeAddress
ON Addresses
FOR INSERT, UPDATE AS
BEGIN
	SET NOCOUNT ON;
	UPDATE Addresses
	SET Location = dbo.Geocode(Address)
	WHERE AddressID IN (SELECT AddressID FROM inserted);
END;
*/
GO