attachment:Ch06.sql of GeographicInformationSystems/Geocoding

   1 USE SpatialDemo
   2 
   3 GO
   4 
   5 EXEC sp_configure 'clr enabled', '1';
   6 GO
   7 
   8 RECONFIGURE
   9 GO
  10 
  11 ALTER DATABASE SpatialDemo SET TRUSTWORTHY ON;
  12 
  13 CREATE ASSEMBLY ProSpatialCh06
  14 FROM 'E:\SQLBackups\ProSpatialCh06\ProSpatialCh06.dll'
  15 WITH PERMISSION_SET = EXTERNAL_ACCESS;
  16 GO
  17 
  18 CREATE FUNCTION dbo.Geocode(
  19 	@addressLine nvarchar(max),
  20 	@locality nvarchar(max),      --city?
  21 	@adminDistrict nvarchar(max), --state?
  22 	@postalCode nvarchar(max),
  23 	@countryRegion nvarchar(max)
  24 ) RETURNS geography
  25 AS EXTERNAL NAME
  26 ProSpatialCh06.[ProSpatial.Ch6.UserDefinedFunctions].GeocodeUDF;
  27 
  28 GO
  29 
  30 SELECT dbo.Geocode('223 Spring Street', 'New York', 'NY', '10013', 'USA').ToString();
  31 
  32 GO
  33 
  34 CREATE FUNCTION dbo.GeocodeTVF(
  35 	@addressLine nvarchar(255),
  36 	@locality nvarchar(255),
  37 	@adminDistrict nvarchar(255),
  38 	@postalCode nvarchar(255),
  39 	@countryRegion nvarchar(255)
  40 ) RETURNS table (Name nvarchar(255), Point geography, BoundingBox geography)
  41 AS EXTERNAL NAME
  42 ProSpatialCh06.[ProSpatial.Ch6.UserDefinedFunctions].GeocodeTVF;
  43 
  44 GO
  45 
  46 SELECT * 
  47 FROM dbo.GeocodeTVF('1600 Pennsylvania Avenue', '', '', '', 'USA');
  48 
  49 GO
  50 
  51 /*
  52 --Although this is in the book, we didn't do it as it basically ends the chapter without using it. 
  53 
  54 CREATE TABLE Addresses (
  55 	AddressID int identity(1,1),
  56 	Address nvarchar(255),
  57 	Location geography
  58 );
  59 
  60 GO
  61 
  62 
  63 CREATE TRIGGER tgGeocodeAddress
  64 ON Addresses
  65 FOR INSERT, UPDATE AS
  66 BEGIN
  67 	SET NOCOUNT ON;
  68 	UPDATE Addresses
  69 	SET Location = dbo.Geocode(Address)
  70 	WHERE AddressID IN (SELECT AddressID FROM inserted);
  71 END;
  72 */
  73 GO