/*=====================================================================

  File:      SpatialTables.sql for Spatial Sample
  Summary:   Implements the schema and loads test data for demonstrating the 
			 Spatial library.
  Date:	     August 19, 2005

---------------------------------------------------------------------
  This spatial search library was developed by Alexander Szalay, Robert Brunner, 
  Peter Kunszt, and Gyorgy Fekete at the Department of Physics and Astronomy, 
  The Johns Hopkins University, in collaboration with Jim Gray, Microsoft Research.
  Details of the algorithms can be found at http://www.sdss.jhu.edu/htm/.
 
  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) Microsoft Corporation.  All rights reserved.
======================================================= */

-------------------------------------------------------------------------
-- Script to create the spatial database of USGS places and river stations.
-- This database is used in the SQL2005 Spatial Index sample. 
-- It assumes the Spatial database has been created.
-- It also assumes the C# HTM (Hierarcical Triangular Mesh) package has been installed. 
-- It also assumes that the data files are located at c:\HtmCsharp\data\
-- It creates the Place, Station, and SpatialIndex tables and populates them. 
--
set nocount on
go
use Spatial
go
create table Place(
      HtmID       bigint       not null primary key, -- spatial index key
      PlaceName   varchar(100) not null, -- City name
      State       char(2)      not null, -- 2 char state code
      Population  int          not null, -- Number of residents (1993?)
      Households  int          not null, -- Number of homes (1993?)
      LandAreaKm  int          not null, -- Area in sqare KM
      WaterAreaKm int          not null, -- water area within land area
      Lat         float        not null, -- latitude in decimal degrees
      Lon         float        not null  -- longitude decimal degrees
);
go
create table Station (
				StationNumber int          not null,     -- USGS Station Number
				StationName   varchar(100) not null,     -- USGS Station Name
				Lat           float        not null,     -- Latitude in Decimal	
				Lon           float        not null,     -- Longitude in Decimal
				DrainageArea  float        not null,     -- Drainage Area (km2)
				FirstYear     int          not null,     -- First Year operation 
				YearsRecorded int          not null,     -- Record years (at Y2k)
				State         char(2)      not null,     -- State location
				IsActive      bit          not null,     -- Was it active at Y2k?   
				IsRealTime    bit          not null,     -- On Internet at Y2K?
				HtmID         bigint       not null,     -- HTM spatial key
  														 -- (based on lat/lon)
				primary key(HtmID, StationNumber) );
GO  
--
-- bulk insert the character source files.
--
DECLARE @SamplesPath nvarchar(1024)
-- You may need to modify the value of the this variable if you have installed the sample someplace other than the default location.
SELECT @SamplesPath = replace(physical_name, 'Microsoft SQL Server\MSSQL.2\MSSQL\DATA\master.mdf', 'Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\') 
	FROM master.sys.database_files 
	WHERE name = 'master';
EXEC('bulk insert Station from ''' + @SamplesPath + 'Spatial\data\StationC.dat'';');
EXEC('bulk insert Place from ''' + @SamplesPath + 'Spatial\data\PlaceC.dat'';');
go

create index Place_Name     on Place(PlaceName);
create index Station_Name   on Station(StationName);
create index Station_Number on Station(StationNumber);
go
create table SpatialIndex (
		HtmID   bigint   not null , -- HTM spatial key (based on lat/lon)
		Lat     float    not null , -- Latitude in Decimal	
		Lon     float    not null , -- Longitude in Decimal
		x       float    not null , -- Cartesian coordinates,
		y       float    not null , -- derived from lat-lon
		z       float    not null , --,
		Type    char(1)  not null , -- place (P) or gauge (G)
		ObjID   bigint   not null , -- object ID in table
		primary key (HtmID, ObjID) );
go
insert SpatialIndex
select 	P.HtmID, Lat, Lon, XYZ.x, XYZ.y, XYZ.z, 
  		'P' as [Type], P. HtmID as ObjID
  from   dbo.Place P cross apply Spatial.dbo.fHtmLatLonToXyz(P.Lat, P.Lon)XYZ;

insert SpatialIndex
  select S.HtmID, Lat, Lon, XYZ.x, XYZ.y, XYZ.z, 
 		'S' as [Type], S.StationNumber as ObjID
  from   dbo.Station S cross apply Spatial.dbo.fHtmLatLonToXyz(S.Lat, S.Lon) XYZ;
go
DBCC INDEXDEFRAG    ( Spatial  , Station, 1);
DBCC INDEXDEFRAG    ( Spatial  , Station, Station_Name);
DBCC INDEXDEFRAG    ( Spatial  , Station, Station_Number);
DBCC INDEXDEFRAG    ( Spatial  , Place,   1);
DBCC INDEXDEFRAG    ( Spatial  , Place,   Place_Name);
DBCC INDEXDEFRAG    ( Spatial  , SpatialIndex, 1);  
DBCC SHRINKDATABASE ( Spatial  , 1  ); -- 1% spare space

-- create index Spatial_index on SpatialIndex(HtmID)
