-- Reload the file
BULK INSERT test_load_htm FROM 'c:\result.csv' WITH (FIELDTERMINATOR = '","')

-- Create the htmid
UPDATE [sebastien_for_jayant].[dbo].[test_load_htm]
   SET [HtmID] = [sebastien_for_jayant].[dbo].[fHtmLatLon] (dec_cent, ra_cent)

-- create and insert cx, cy, cz
UPDATE [sebastien_for_jayant].[dbo].[FrameWCS] 
SET cx = (select x FROM [sebastien_for_jayant].[dbo].[fHtmLatLonToXyz] (dec_cent, ra_cent)),
    cy = (select y FROM [sebastien_for_jayant].[dbo].[fHtmLatLonToXyz] (dec_cent, ra_cent)),
    cz = (select z FROM [sebastien_for_jayant].[dbo].[fHtmLatLonToXyz] (dec_cent, ra_cent))

-- create htm table
create table Htm (
  ObjID   bigint   not null , -- object ID in table
  HtmID   bigint   not null , -- HTM spatial key (based on lat/lon)
  cx       float    not null , -- Cartesian coordinates,
  cy       float    not null , -- derived from lat-lon
  cz       float    not null , --,	
  ra     float    not null , -- Latitude in Decimal	
  dec     float    not null , -- Longitude in Decimal
  primary key (ObjID) );
go

-- Insert into htm table
insert Htm
    select P.HtmID, P. HtmID as ObjID, XYZ.x, XYZ.y, XYZ.z, ra_cent, dec_cent
        
    from 
        dbo.test_load_htm P cross apply [sebastien_for_jayant].[dbo].[fHtmLatLonToXyz] (P.ra_cent, P.dec_cent) XYZ
where P.zoom=0

-- Create primary key
ALTER TABLE dbo.FrameWCS_test ADD  CONSTRAINT [pk_Frame_fieldID_zoom] PRIMARY KEY CLUSTERED 
(
	[fieldID] ASC,
	[zoom] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]


-- create indices
ALTER TABLE dbo.FrameWCS ADD  CONSTRAINT [pk_Frame_fieldID_zoom] PRIMARY KEY CLUSTERED 
(
	[fieldID] ASC,
	[zoom] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [i_Frame_zoom] ON [dbo].[FrameWCS] 
(
	[fieldid] ASC,
	[zoom] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

USE [NDWFS]
GO
/****** Object:  Index [i_htmid]    Script Date: 08/05/2006 23:28:52 ******/
CREATE NONCLUSTERED INDEX [i_htmid] ON [dbo].[FrameWCS] 
(
	[htmid] ASC,
	[cx] ASC,
	[cy] ASC,
	[cz] ASC
) ON [PRIMARY]