
-- this version differs from sss1809.sql, by addition of definitions of
-- fNearbyObjEq, fHTMCover, fGreatCircleDistance, (SSS-SDSS) CrossNeighbours and Neighbours

-- remove keys and references from Nige's schema and run them after creating the tables



use sssv2
go
create table SurveyInfo(
	-- Constraints within SurveyInfo table (to be applied after loading data):
	--
	-- survey ID - primary key
	--
	surveyID 	tinyint not null, -- the unique ID of the survey 
        surveyName 	varchar(16) not null, --    SERC-J/EJ, POSSI-E, or ESO-R etc.
	decMin          float  not null, --         southern limit of survey Decs / degrees
	decMax          float  not null, --         northern limit of survey Decs / degrees
	numFields       int not null, --            No. of fields in the survey
        telescope       varchar(64)  not null,  --  UK  or ESO or Palomar Oschin Schmidt.
	telAperture     float not null, --          eg. 1.2 or 1.0 metres
	telLong         float not null, --          Telescope Longitude / deg
	telLat          float not null, --          Telescope latitude /deg
	telHeight       real  not null, --          Telescope altitude / metres
	plateScale      real not null,  --          Nominal plate scale in arcsec/mm
        colour          varchar(16) not null, --    "blue", "red" or "near-infrared"
        waveMin         int not null, --            approx. minimum wavelength / nanometres
	waveMax         int not null, --            approx. maximum wavelength / nanometres
	waveEff         int not null, --            approx. effective wavelength / nanometres
	magLimit        real not null, --           nominal mag limit of survey, eg. 22.5 for Bj
        epochMin        real not null, --           minimum epoch of observations / yr
	epochMax        real not null, --           maximum epoch of observations / yr
	epTsys          varchar(16) not null, --    time system for epoch values in SurveyCatalog
        equinox         real not null, --           equinox for positions in SurveyCatalog
	eqTsys          varchar(16) not null, --    time system for equinox in SurveyCatalog
	surveyRef       varchar(256) not null   --  literature reference for survey details
	)
GO
Create table PlateInfo(
	-- Constraints within PlateInfo table (to be applied after loading data):
	--
	-- plateID - primary key
	-- surveyID - references SurveyInfo(surveyID)
	-- fieldID - references FieldInfo(fieldID)
	--
	plateID         int not null, -- the unique ID of the plate 
	surveyID        tinyint not null,   -- every plate comes from survey.
        fieldID         int not null, -- unique survey field identifier
        plateNum        smallint not null,  -- plate number assigned by observatory
        dateMeas        varchar(12) not null, --        eg. 1998:09:30 
        timeMeas        varchar(8) not null, --         eg. 15:33:47 
	instrument       varchar(16) not null, --    name of scanning machine
        softVersion     varchar(18) not null, --        eg. /home/scosdev/v033 
        operator        varchar(3) not null, --         eg. ebt 
	scanMaterial    varchar(16) not null,  --   "Original glass", "Copy film", "Copy glass"
	emulsion        varchar(8) not null, --    Kodak emulsion type, eg. 103a-E, IIIa-J, IV-N
	filterID        varchar(8) not null, --     eg. OG590, RG715, 2444...
        filterComm      varchar(16) not null, --    eg. "Schott glass", "Red plexiglass"...
        raPnt         real not null, --               actual plate centre RA (mean degrees)
        decPnt        real not null, --               actual plate centre Dec (mean degrees)
        radecSys      varchar(4) not null, --         eg. FK4; system used in raCen and decCen
        equinox      real not null, --               eg. 1950; equinox of raCen and decCen (year)
        eqTsys        varchar(12) not null, --        eg. BESSELIAN; time system of equinox
        utDateObs    varchar(8) not null, --         eg. 19781025; Gregorian calendar date of obs (yyyymmdd)
        lstObs       varchar(4) not null, --         eg. 2336; Local sidereal time at start of exp (24-hour)
        expLength    real not null, --              eg. 70 exposure length (minutes)
        epoch         real not null, --         eg. 1978.9; epoch of observation (years)
        domeTemp      real not null, --        eg. 273.155 (K)  dome temp at observation
        domePressure  real not null, --        eg. 1013.25 (mBar) dome pressure at observation 
        domeHumid     real not null, --        eg. 0.5: dome relative humidity at obsevation
        waveEffect    smallint not null, --        eg. 4500: effective wavelength (Angstrom) 
        tropl         real not null, --        eg. 0.0065: tropospheric lapse rate (K/m)
        mjd           float not null, -- eg. 43806.500505336; Modified Julian Date at mid exp (Julian days)
        focusNX        tinyint not null, --        eg. 6; No. of focus points in X
        focusNY        tinyint not null, --        eg. 6; No. of focus points in Y
        calType        varchar(16) not null, --        eg. SPLINE; calibration wedge fit type 
        stepWdg       varchar(16) not null, --        eg. KPNO; calibration step-wedge type
        nSteps         tinyint  not null, --        eg.       8; no. of wedge steps
	orientation     varchar(4) not null,  --        SuperCOSMOS plate orientation e.g. "news"
	emulpos         varchar(4) not null, --         SuperCOSMOS emulsion position, e.g. "up"
	sosp		smallint not null, --                SuperCOSMOS start of scan pix number
	stepsize	real not null, --		   SuperCOSMOS nominal pixel size in microns
	scanlen		smallint not null, --		   SuperCOSMOS lane scan length (pixels)
	aXmin		real not null, --		   SuperCOSMOS XMIN of measured area (micron)
	aXmax		real not null, --                  SuperCOSMOS XMAX of measured area (micron)
	aYmin		real not null, --		   SuperCOSMOS YMIN of measured area (micron)
	aYmax		real not null, --		   SuperCOSMOS YMAX of measured area (micron)
        xPnt            real not null, --        eg.   177500.0; centre of scan area in X (micron)
        yPnt            real not null, --        eg.   177500.0; centre of scan area in Y (micron)
	areaCut         tinyint not null, --		   min. number of pixels for object detection
	apParam	        real not null, --	           areal profile parameter
	dbParam	        real not null, --               deblending parameter
	dbAmin	        tinyint not null, --   min. area of parent image that can be deblended (pixels)
	dbAmax		int not null, --   max. area of parent image that can be deblended (pixels)
	dbAcut          tinyint not null, --               min. number of pixels for deblended object detection
	dbLevel	        tinyint not null, --               No. of rethresholding levels for deblending
	skySquare	smallint not null, --		   Blocksize (one side) of sky background (pixels)
	skyDefn	        varchar(16) not null, --       method of defining sky, eg. "median"
	skyFilter       varchar(16) not null, --       Box filter weights for sky determination 
	fThresh         tinyint not null, --               Sky filtering parameter
	fSclen		tinyint not null, --	           Scale size for iterative filter application
        pCut           real not null, --        eg.   9.000004; "percentage cut" for pixel analysis
	starCat		varchar(80) not null, -- name of reference star catalogue used for astrometry
        brightLim      real not null, --        eg.   9; bright mag limit for selected astrometric refstars 
        faintLim       real not null, --        eg.          30; faint limit for selected refstars
        equinRef        real not null, --        eg.          2000; Equinox of refstar positions
        tSysRef         varchar(12) not null, --  eg. JULIAN; time system of equinox of refstar positions
        maxIter        int not null, --        eg. 500; max no. of iterations of non-linear astrometric fit
        rCritIni       real not null, --        eg.  5000; initial matching tolerance for refstars (micron)
        rCritAbs       real not null, --        eg.     400; absolute object rejection criterion (micron)
        rCritRel       real not null, --        eg.        1; relative object rejection criterion
        rCritFin      real not null, --        eg.        2.5; final residual tolerance (sigma)
        distType        varchar(80) not null, --        eg. R/TAN(R); radial distortion term in solution
	scdGrid		varchar(80) not null, --	   Non-linear distortion mask
	raCol		varchar(16) not null, --	   name of RA column in reference catalogue
	decCol		varchar(16) not null, --	   name of Dec column in reference catalogue
	raPMCol	        varchar(16) not null, -- name of RA proper motion column in reference catalogue
	decPMCol	varchar(16) not null, -- name of Dec proper motion column in reference catalogue
	plxCol		varchar(16) not null, --	   name of parallax column in reference catalogue
	rvCol	        varchar(16) not null, --	   name of radial velocity column in reference catalogue
        magCol         varchar(16) not null, --   eg. VT; name of magnitude column in reference catalogue 
        cEquinox        real not null, --        eg.          2000; catalogue equinox (year)
        cEqTSys       varchar(16) not null, --        eg. JULIAN; catalogue equinox time system
        cEpoch        real not null, --        eg.          2000; catalogue epoch (year)
        cEpTSys       varchar(16) not null, --        eg. JULIAN; catalogue epoch time system
        starsInit         int not null, --        eg.  774; no. of refstars in initial astrometric solution
        starsSelec        int not null, --        eg.       1101; no. of refstars initially selected
        starsUsed      int not null, --        eg.       645; no. of refstars in final astrometric solution
        coeffs1       float not null, --        eg.  17.769323185753; coeff(1) of fit for XY to obs. xi,eta
        coeffs2       float not null, --        eg. -307.26853505792; coeff(2) of fit for XY to obs. xi,eta
        coeffs3       float not null, --        eg. -1.2193865505773; coeff(3) of fit for XY to obs. xi,eta
        coeffs4       float not null, --        eg. 17.977685228561; coeff(4) of fit for XY to obs. xi,eta
        coeffs5       float not null, --        eg. -1.2206735583232; coeff(5) of fit for XY to obs. xi,eta
        coeffs6       float not null, --        eg. 307.25667155971; coeff(6) of fit for XY to obs. xi,eta
        cubicDist       float not null, --        eg. -0.33333333333333; cubic coeff. of radial distortion
        obsRaPnt         float not null, --        eg. 0.20764561186538; observed plate centre RA (radians)
        obsDecPnt        float not null, --    eg.  -0.52083245365957; observed plate centre Dec (radians)
        shiftPnt      float not null, -- eg. 0.0006493107920652; shift of nomin. to actual centre (radians)
        areaMin        int not null, --        eg.       8; minimum object area (pixels)
        areaMax       int not null, --        eg.       175210; maximum object area (pixels)
        magMin         real not null, --        eg.          -31451; minimum instrumental mag
        magMax         real not null, --        eg.          -17811; maximum instrumental mag
        ellipMin         real not null, --        eg. 0.0003272891; minimum object ellipticity
        ellipMax         real not null, --        eg.          1; maximum object ellipticity
        ellipMode         real not null, --        eg.          0.22; modal object ellipticity
        orientMode          real not null, --        eg.          48; modal orientation (degrees)
        ellipMed         real not null, --        eg.          0.29; median ellipticity
        orientMed          real not null, --        eg.          76; median orientation (degrees)
        ellipMean        real not null, --        eg. 0.3065745; mean object ellipticity
        orientMean         real not null, --        eg. 81.38738; mean orientation (degrees)
        objNum         int not null, --        eg.       335403; no. of objects detected on plate
        nParents        int not null, --        eg.       313321; no. of parent objects detected on plate
        lane1Count         int not null, --      No. of objects in lane 1 of plate scan 
        lane2Count         int not null, --      No. of objects in lane 2 of plate scan
        lane3Count         int not null, --      No. of objects in lane 3 of plate scan       
        lane4Count         int not null, --      No. of objects in lane 4 of plate scan       
        lane5Count         int not null, --      No. of objects in lane 5 of plate scan       
        lane6Count         int not null, --      No. of objects in lane 6 of plate scan       
        lane7Count         int not null, --      No. of objects in lane 7 of plate scan       
        lane8Count         int not null, --      No. of objects in lane 8 of plate scan       
        lane9Count         int not null, --      No. of objects in lane 9 of plate scan
        lane10Count        int not null, --      No. of objects in lane 10 of plate scan     
        lane11Count        int not null, --      No. of objects in lane 11 of plate scan     
        lane12Count        int not null, --      No. of objects in lane 12 of plate scan      
        lane13Count        int not null, --      No. of objects in lane 13 of plate scan
        lane14Count        int not null, --      No. of objects in lane 14 of plate scan      
        lane15Count        int not null, --      No. of objects in lane 15 of plate scan      
        lane16Count        int not null, --      No. of objects in lane 16 of plate scan      
        lane17Count        int not null, --      No. of objects in lane 17 of plate scan      
        lane18Count        int not null, --      No. of objects in lane 18 of plate scan      
        lane19Count        int not null, --      No. of objects in lane 19 of plate scan      
        lane20Count        int not null, --      No. of objects in lane 20 of plate scan      
        lane21Count        int not null, --      No. of objects in lane 21 of plate scan      
        lane22Count        int not null, --      No. of objects in lane 22 of plate scan      
        lane23Count        int not null, --      No. of objects in lane 23 of plate scan      
        lane24Count        int not null, --      No. of objects in lane 24 of plate scan      
        lane25Count        int not null, --      No. of objects in lane 25 of plate scan      
        lane26Count        int not null, --      No. of objects in lane 26 of plate scan      
        lane27Count        int not null, --      No. of objects in lane 27 of plate scan      
        lane28Count        int not null, --      No. of objects in lane 28 of plate scan  
        lane29Count        int not null, --      No. of objects in lane 29 of plate scan  
        lane30Count        int not null, --      No. of objects in lane 30 of plate scan
        astResidX           real not null, --      Residual of astrometric fit in X (arcsec)   
        astResidY           real not null, --      Residual of astrometric fit in Y (arcsec)   
        nCalCoeffs          tinyint not null, --       No. of photometric calibration coefficients
        CalXK               real not null, -- stationary pt in fitted calib. curve (instrum. profile mag)
        gradient            real not null, --     gradient of linear portion of calibration curve
        getCal1             float not null, --     1st calibration coefficient
        getCal2             float not null, --     2nd calibration coefficient
        getCal3             float not null, --     3rd calibration coefficient
        getCal4             float not null, --     4th calibration coefficient
        getCal5             float not null, --     5th calibration coefficient
        getCal6             float not null, --     6th calibration coefficient
        ffZp                real not null, --     field-to-field zeropoint (magnitudes)
        galZp               float not null, --     zeropoint of galaxy instrumental scale (magnitudes)
        galGrad             float not null, --     gradient of galaxy instrumental mag scale
        directory           varchar(80) not null -- name of directory in filesystem of source data.
	)
GO
create table SurveyCatalog(
	-- Constraints within SurveyCatalog table (to be applied after loading data):
	--
	-- objID - primary key
	-- surveyID - references SurveyInfo(surveyID)
	-- plateID - references PlateInfo(plateID)
	-- fieldID - references FieldInfo(fieldID)
	-- mergeID - references MergedCatalog(mergeID)
	--
	objID   bigint not null, -- unique ID no. (will be primary key)
	surveyID tinyint not null,  -- the survey that collected the data
	plateID	int not null, -- this is the plate that the observation came from  
	fieldID   int not null,	--  no. of field in which object found
	parentID bigint not null,  -- objID of parent object (=sobjID for parent/undebl. object) 
        mergeID bigint not null,   -- objID for corresponding object in mergedCatalogue 
	ra	float not null,	-- degrees
	dec	float not null,	--  degrees 
	htmId	bigint not null, -- Hierarchical Triangular Mesh (20-deep) of centroid 
 	cx	float not null, -- Cartesian x of unit (ra,dec) vector on celestial sphere
 	cy	float not null, -- Cartesian y of unit (ra,dec) vector on celestial sphere
	cz	float not null, -- Cartesian z of unit (ra,dec) vector on celestial sphere
	xmin 	float not null,	-- leftmost extent of image on plate (microns)
	xmax 	float not null,	-- rightmost extent of image on plate (microns)
	ymin 	float not null,	-- bottommost extent of image on plate (microns)
	ymax 	float not null,	-- topmost extent of image on plate (microns)
	area   	int not null,	-- no. of pixels within detection isophote 
	ipeak	real not null, -- intensity of brightest pixel above sky
	cosmag	real not null,	-- isophotal magnitude (instrumental units) 
	isky	real not null,	-- estimate of sky intensity at (xCen, yCen) 
	xCen	float not null,	-- intensity-weighted X-centroid of image (microns) 
	yCen	float not null,	-- intensity-weighted Y-centroid of image (microns) 
	aU	real not null,	-- semi-major axis of unweighted fitted ellipse (microns) 
	bU	real not null,	-- semi-minor axis of unweighted fitted ellipse (microns)
	thetaU	smallint not null,	-- position angle of unweighted fitted ellipse  (degrees)
	aI	real not null,	-- semi-major axis of intensity-weighted fitted ellip. (microns) 
	bI	real not null,	-- semi-minor axis of intensity-weighted fitted ellip. (microns) 
	thetaI	smallint not null,	-- position angle of intensity-weighted fitted ellipse  (degrees)
	class 	tinyint not null,   -- classification flag 
	pa	smallint not null,	--  celestial position angle (degrees East of North)
	ap1   	int not null, -- no. of pixels above 1st areal profile threshold
	ap2   	int not null, -- no. of pixels above 2nd areal profile threshold
	ap3   	int not null, -- no. of pixels above 3rd areal profile threshold
	ap4   	int not null, -- no. of pixels above 4th areal profile threshold
	ap5   	int not null, -- no. of pixels above 5th areal profile threshold 
	ap6   	int not null, -- no. of pixels above 6th areal profile threshold
	ap7   	int not null, -- no. of pixels above 7th areal profile threshold
	ap8   	int not null, -- no. of pixels above 8th areal profile threshold
	blend 	int not null, -- flag encoding deblending info
	quality int not null,-- flag, with each bit indicating something different 
	prfStat real not null,  -- profile statistic (used for star/galaxy separation) 
	prfMag  real not null, -- profile magnitude
	gMag	real not null,	-- magnitude calculated on assumption object is galaxy
	sMag	real not null,	-- magnitude calculated on assumption object is star
	seam	smallint not null	-- seam index, telling whether object selected in overlap, etc
	)
 
go


create table MergedCatalog(
	-- Constraints within MergedCatalog table (to be applied after loading data):
	--
	-- objID - primary key
	-- surveyID - references SurveyInfo(surveyID)
	-- objIDB - references SurveyCatalog(objID)
	-- objIDR1 - references SurveyCatalog(objID)
	-- objIDR2 - references SurveyCatalog(objID)
	-- objIDI - references SurveyCatalog(objID)
	--
	objID bigint not null, -- unique ID no.
        objIDB  bigint not null,  --  objID for B band detection merged into this object  
        objIDR1 bigint not null,  --  objID for R1 band detection merged into this object  
        objIDR2 bigint not null,  --  objID for R2 band detection merged into this object  
        objIDI  bigint not null,  --  objID for I band detection merged into this object  
	htmId	bigint not null, -- Hierarchical Triangular Mesh (20-deep) of centroid 
	epoch  real not null, -- epoch for object positions (variance weighted mean epoch of available measures)
	ra	float not null,	-- master RA, computed from detections merged in this catalogue
	dec	float not null,	-- master Dec, computed from detections merged in this catalogue
        sigRA   float not null, -- uncertainty in RA (formal random error not inc. systematic errors)
        sigDec  float not null, -- uncertainty in Dec (formal random error not inc. systematic errors)
	cx	float not null,	-- Cartesian x of unit (ra,dec) vector on celestial sphere
	cy	float not null,	-- Cartesian y of unit (ra,dec) vector on celestial sphere
	cz	float not null,	-- Cartesian z of unit (ra,dec) vector on celestial sphere
	muAcosD    real not null, --   proper motion in RA direction (mas/yr)
	muD	   real not null, --   proper motion in Dec direction (mas/yr)
	sigMuAcosD real not null, --   error on proper motion in RA direction (mas/yr)
	sigMuD	   real not null, --   error on proper motion in Dec direction (mas/yr)
        chi2    real not null,    -- chi-squared value of proper motion solution
        Nplates tinyint not null, -- number of plates used for this proper motion measurement
        bestMagB  real not null, --  "best" B band magnitude 
        bestMagR1 real not null, --  "best" R1 band magnitude 
        bestMagR2 real not null, --  "best" R2 band magnitude 
	bestMagI  real not null, --	 "best" I band magnitude 
        gCorMagB  real not null, --   B band magnitude assuming object is galaxy 
        gCorMagR1 real not null, --   R1 band magnitude assuming object is galaxy 
        gCorMagR2 real not null, --   R2 band magnitude assuming object is galaxy 
	gCorMagI  real not null, --	  I band magnitude assuming object is galaxy 
        sCorMagB  real not null, --   B band magnitude assuming object is star 
        sCorMagR1 real not null, --   R1 band magnitude assuming object is star 
        sCorMagR2 real not null, --   R2 band magnitude assuming object is star 
	sCorMagI  real not null, --	  I band magnitude assuming object is star
	bestClass tinyint not null,	--  "best" guess at classification, based on individual classes 
        classB    tinyint not null, -- class from B band detection
	classR1   tinyint not null, -- class from R1 band detection
	classR2   tinyint not null, -- class from R2 band detection
	classI    tinyint not null, -- class from I band detection
        ellipB    real not null, -- ellipticity of B band detection
        ellipR1   real not null, -- ellipticity of R1 band detection
        ellipR2   real not null, -- ellipticity of R2 band detection
        ellipI    real not null, -- ellipticity of I band detection
        qualB     int not null, -- quality flag from B band detection
        qualR1    int not null, -- quality flag from R1 band detection
        qualR2    int not null, -- quality flag from R2 band detection
        qualI     int not null, -- quality flag from I band detection
        blendB    int not null, -- blend flag from B band detection
	blendR1   int not null, -- blend flag from R1 band detection
	blendR2   int not null, -- blend flag from R2 band detection
	blendI	  int not null, -- blend flag from I band detection
	prfStatB  real not null, -- profile statistic from B band detection
	prfStatR1  real not null, -- profile statistic from R1 band detection
	prfStatR2 real not null, -- profile statistic from R2 band detection
	prfStatI real not null -- profile statistic from I band detection
	)
go

create table FieldInfo(
	-- Constraints within FieldInfo table (to be applied after loading data):
	--
	-- surveyID - references SurveyInfo(surveyID)
	-- fieldID - primary key
	--
	surveyID 	tinyint not null,  -- the unique ID of the survey
        fieldID         int not null,  -- the unique survey field number: field number plus surveyID combo
        fieldNum        smallint not null, -- ESO/SRC or Palomar survey system field number
        nominalRA       float not null,    -- the nominal field centre RA (degrees)
        nominalDec      float not null,    -- the nominal field centre Dec (degrees)
        nominalSize     real not null,     -- the measured FOV on a side (degrees)
	htmId	bigint not null -- Hierarchical Triangular Mesh (20-deep) of nominal field centre
	)
GO

create table Extinction(
	htmID		bigint not null, -- HTM ID 
	ra		float not null, -- RA (degrees)
	dec		float not null, -- Dec (degrees)
	ax		real not null, -- extinction coefficient
	)

-- now load in the data from C:\Documents and Settings\bob\sss_sqlserver\sssv2
-- 
-- SurveyInfo.csv --> SurveyInfo
-- PlateInfo.csv --> PlateInfo 
-- surveycatalog_tst2.csv --> SurveyCatalog
-- mergedcatalog_tst2.csv --> MergedCatalog
-- fieldInfo.csv --> FieldInfo
-- extinction.csv --> extinction
-- 
-- all loaded OK

-- add primary keys to tables, as follows

alter table SurveyInfo add constraint pk_surveyID primary key (surveyID) 
alter table PlateInfo add constraint pk_plateID primary key (plateID)
alter table SurveyCatalog add constraint pk_sc_objID primary key (objID)
alter table MergedCatalog add constraint pk_mc_objID primary key (objID)
alter table FieldInfo add constraint pk_fieldID primary key (fieldID)

-- before adding the foreign keys, need to check which entries in mergedcatalog point
-- to "missing" entries in surveycatalog and then remove them.

select count (*) from MergedCatalog --  90946 sources, for "personal" version
select count (*) from surveyCatalog -- 169573 sources, for "personal" version

select count(*)
from MergedCatalog
where objIDB not in (select objID from SurveyCatalog) -- 4 sources

update mergedcatalog 
set objidb=0
where objidb not in (select objID from SurveyCatalog) -- (4 row(s) affected)

select count(*)
from MergedCatalog
where objIDR1 not in (select objID from SurveyCatalog)-- 4 sources

update mergedcatalog 
set objidr1=0
where objidr1 not in (select objID from SurveyCatalog) -- (4 row(s) affected)

select count(*)
from MergedCatalog
where objIDR2 not in (select objID from SurveyCatalog) -- 1 source

update mergedcatalog 
set objidr2=0
where objidr2 not in (select objID from SurveyCatalog) -- (1 row(s) affected)

select count(*)
from MergedCatalog
where objIDI not in (select objID from SurveyCatalog) -- 4 sources

update mergedcatalog 
set objidi=0
where objidi not in (select objID from SurveyCatalog) -- (4 row(s) affected)

-- these update statement are required to fudge the few "boundary effects": the
-- matching of entries in the mergedcatalog and surveycatalog tables are only
-- consistent over the area of a full plate, so if we cut out an area -- as done
-- in the definition of the "personal SSS" region -- then there are a few entries
-- in mergedcatalog which have single-band detections not included in surveycatalog

-- similarly, there will be some entries in surveycatalog pointing to an entry cropped
-- out of mergedcatalog

select count(*) from surveycatalog 
where mergeid not in (select objid from mergedcatalog) -- 4 entries

update surveycatalog 
set mergeid=0 
where mergeid not in (select objid from mergedcatalog) -- (4 row(s) affected)

-- other consistency checks 

select objIDB from MergedCatalog where objIDB not in (select objID from SurveyCatalog) --  4 selected
select objIDB from MergedCatalog where objIDB>0 and objIDB not in (select objID from SurveyCatalog) -- 4 selected

select count (*) from surveycatalog where objid=parentid and blend > 0 and parentid=0  -- 0 objects

select count(*) from MergedCatalog where objidb=0 and objidr1=0 and objidr2=0  and objidi=0 -- 1 object
select * from MergedCatalog where objidb=0 and objidr1=0 and objidr2=0  and objidi=0 -- it's the null object

select count (*) from MergedCatalog where nplates > 4 or nplates < 1 -- 1object
select * from MergedCatalog where nplates > 4 or nplates < 1 -- it's the null object

select count (*) from SurveyCatalog where blend > -1 and mergeid=0 and seam=0 and quality < 128 -- 0 objects

select count (*) from mergedcatalog as m, surveycatalog as s where m.objidb>0 and s.mergeid=0 and s.objid=m.objidb 
-- this gives 0 objects

--select m.objidb,m.objidr1,m.objidr2,m.objidi,s.objid, s.seam,s.quality from mergedcatalog as m, surveycatalog as s where m.objidb>0 and s.mergeid=0 and s.objid=m.objidb and s.quality<128 and s.seam=0
-- that returns no objects, showing that all 15531 objects in previous query do arise near field boundary in overlap, where
-- the one or more of the slaves for a master image are over the boundary and their seam indices would normally lead
-- to their not being chosen for a seamless catalogue -- i.e. the corresponding image from the other plate would be chosen.


-- can now add the foreign keys, etc, using Jim Gray's file SSS_ForeignKeys.sql, copied below

ALTER TABLE PlateInfo ADD CONSTRAINT fk_PlateInfo_fieldID_to_FieldInfo_fieldID 
	FOREIGN KEY (fieldID) REFERENCES FieldInfo(fieldID) -- ok

ALTER TABLE PlateInfo ADD CONSTRAINT fk_PlateInfo_surveyID_to_SurveyInfo_surveyID
	FOREIGN KEY (surveyID)REFERENCES SurveyInfo(surveyID) --ok
 
ALTER TABLE SurveyCatalog ADD CONSTRAINT fk_SurveyCatalog_surveyID_to_SurveyInfo_surveyID
	FOREIGN KEY (surveyID) REFERENCES SurveyInfo(surveyID) --ok

ALTER TABLE SurveyCatalog ADD CONSTRAINT fk_SurveyCatalog_plateID_to_PlateInfo_PlateID
	FOREIGN KEY (plateID) REFERENCES PlateInfo(plateID) -- ok

ALTER TABLE SurveyCatalog ADD CONSTRAINT fk_SurveyCatalog_fieldID_to_FieldInfo_fieldID
	FOREIGN KEY (fieldID) REFERENCES FieldInfo(fieldID) --ok

ALTER TABLE SurveyCatalog ADD CONSTRAINT fk_SurveyCatalog_mergeID_to_MergedCatalog_mergeID 
	FOREIGN KEY (mergeID) REFERENCES MergedCatalog(objID) -- ok now

ALTER TABLE MergedCatalog ADD CONSTRAINT fk_MergedCatalog_objIDB_to_SurveyCatalog_sobjID 
	FOREIGN KEY (objIDB) REFERENCES SurveyCatalog(objID) -- ok now

ALTER TABLE MergedCatalog ADD CONSTRAINT fk_MergedCatalog_objIDR1_to_SurveyCatalog_sobjID
	FOREIGN KEY (objIDR1) REFERENCES SurveyCatalog(objID) -- ok now

ALTER TABLE MergedCatalog ADD CONSTRAINT fk_MergedCatalog_objIDR2_to_SurveyCatalog_sobjID
	FOREIGN KEY (objIDR2) REFERENCES SurveyCatalog(objID) -- ok now

ALTER TABLE MergedCatalog ADD CONSTRAINT fk_MergedCatalog_objIDI_to_SurveyCatalog_sobjID
	FOREIGN KEY (objIDI) REFERENCES SurveyCatalog(objID) -- ok now

ALTER TABLE FieldInfo ADD CONSTRAINT fk_FieldInfo_surveyID_to_SurveyInfo_surveyID
	FOREIGN KEY (surveyID) REFERENCES SurveyInfo(surveyID) -- ok now

---- OK to here


-- the following stuff adds the spatial functions

-- before doing this, have to copy xp_SQL_HTM_dll.dll to the MSSQL/Binn directory

-- then do the following, which is copied from Jim Gray's sp_HTML.sql file

/*-----------------------------------------------
 * spHTM installation instructions
 * Move the xp_SQL_HTM_dll.dll file to the 
 *             C:\Program Files\Microsoft SQL Server\MSSQL\Binn directory
 * Then execute the commands
 *		sp_dropextendedproc 'xp_HTM_Cover'
 *		sp_dropextendedproc 'xp_HTM_Lookup'
 *		sp_addextendedproc  'xp_HTM_Cover',	'xp_SQL_HTM_dll.dll'
 *		sp_addextendedproc  'xp_HTM_Lookup', 	'xp_SQL_HTM_dll.dll'
 * in the SQL Query Analyzer (in the master database).
 * then run the attached script that defines the procedures.
*/
/*----------------------------------------------------------------
// xp_HTM_Lookup:  drop and then bind the Lookup procedure.	
*/
USE master
GO
sp_dropextendedproc 'xp_HTM_Cover'
GO
sp_dropextendedproc 'xp_HTM_Lookup'
GO
--sp_addextendedproc  'xp_HTM_Cover',  'SQL_HTM.dll'
sp_addextendedproc  'xp_HTM_Cover',  'xp_SQL_HTM_dll.dll'
GO
--sp_addextendedproc  'xp_HTM_Lookup', 'SQL_HTM.dll'
sp_addextendedproc  'xp_HTM_Lookup', 'xp_SQL_HTM_dll.dll'
go

USE SSS
--------------------------------------------------
-- uninstall the old stored spHTM_Lookup procedure
--
IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'spHTM_Lookup')
	DROP FUNCTION spHTM_Lookup 
GO
--------------------------------------------------
-- spHTM_Lookup (@Coordinates)
--    Find the HTM triangle ID of the specified point on the celestial sphere.
--    Coordiate syntax is:
--      J2000     depth ra dec	This is polar coordinates.
--	CARTESIAN depth x y z  where x, y, z are numbers giving the
--				position of the point on the sphere.
--
--		depth is an interger in the range 2..14 giving the mesh depth.
--		ra, dec, x, y, z are floats.
--	examples:
--		J2000     20 240.0 38.0	 -- 20 deep above San Francisco
--		CARTESIAN 9 1.0 1.0 1.0  -- 9 deep at the equator zero meridian					 
--
CREATE FUNCTION   spHTM_Lookup (@Coordinate VARCHAR(1000))
RETURNS BIGINT
AS
BEGIN
	DECLARE @HTM_Lookup BIGINT	-- the answer
	DECLARE @HTM_KLUDGE BINARY(8) 	-- a workaround for the fact that 
				      	-- external stored procs do not take BIGINT params
	DECLARE @retcode    INT		--
	DECLARE @ErrorMsg   VARCHAR(1000) -- error messsage from lookup (if coordinate has syntax error)
	EXECUTE  @retcode = master.dbo.xp_HTM_Lookup @Coordinate, @HTM_KLUDGE OUTPUT, @ErrorMsg OUTPUT
	IF (@retcode = 0)   		-- got bytes, cast them as an INT64
		BEGIN
		SET @HTM_Lookup = CAST(substring(@HTM_KLUDGE, 1, 8) AS BIGINT)
		END
	ELSE
	   BEGIN SET @HTM_Lookup = -@retcode END
	RETURN(@HTM_Lookup) 
END					-- end of HTM_Lookup
GO	


--------------------------------------------------
-- uninstall the old stored spHTM_Lookup_ErrorMessage procedure
--
IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'spHTM_Lookup_ErrorMessage')
	DROP FUNCTION spHTM_Lookup_ErrorMessage 
GO
--------------------------------------------------
-- spHTM_Lookup_ErrorMessage (@Coordinates)
--    Returns the ErrorMssage that spHTM_Lookup generated.
--    if there was no error, returns the string 'OK'
CREATE FUNCTION   spHTM_Lookup_ErrorMessage (@Coordinate VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
	DECLARE @HTM_KLUDGE BINARY(8) 	-- a workaround for the fact that 
				      	-- external stored procs do not take BIGINT params
	DECLARE @retcode    INT		--
	DECLARE @ErrorMsg   VARCHAR(1000) -- error messsage from lookup (if coordinate has syntax error)
	EXECUTE  @retcode = master.dbo.xp_HTM_Lookup @Coordinate, @HTM_KLUDGE OUTPUT, @ErrorMsg OUTPUT
	IF (@retcode != 0)   
		BEGIN
		SET @ErrorMsg = 'Call to master.dbo.xp_HTM_Lookup failed completely'
		END
	RETURN(@ErrorMsg) 
END
GO	

/*  TEST CASES
SELECT 'J2000     20 240.0 38.0 ' , dbo.spHTM_Lookup_ErrorMessage('J2000 20 240.0 38.0'     ) -- OK
SELECT 'J2000     0 240.0 38.0' , dbo.spHTM_Lookup('J2000 0 240.0 38.0'     )as HTM_ID --   =             13
SELECT 'J2000     5 240.0 38.0' , dbo.spHTM_Lookup('J2000 5 240.0 38.0'     )as HTM_ID --   =          14248
SELECT 'J2000     20 240.0 38.0' , dbo.spHTM_Lookup('J2000 20 240.0 38.0'     )as HTM_ID -- = 15299371974207
SELECT 'J2000     6 41.4 47.9' , dbo.spHTM_Lookup('J2000 6 41.4 47.9'     )as HTM_ID  -- 65351
SELECT 'CARTESIAN 9 1.0 1.0 1.0', dbo.spHTM_Lookup('CARTESIAN 9 1.0 1.0 1.0 ') as HTM_ID --4194303
SELECT 'CARTESIAN 9 1.0 1.0 1.0', dbo.spHTM_Lookup('CARTESIAN 1 1.0 1.0 1.0 ') as HTM_ID  -- 63
-- some error cases
SELECT 'J2001     20  240.0 38.0' , dbo.spHTM_Lookup_ErrorMessage('J2001 20 240.0 38.0') -- htmInterface:getCode: Unexpected command : J2001
SELECT 'J2000      100 240.0 38.0' , dbo.spHTM_Lookup_ErrorMessage('J2000 100 240.0 38.0') -- htmInterface:getDepth: Depth too large: Max is HTMMAXDEPTH
SELECT 'J2000     20 240.0' , dbo.spHTM_Lookup_ErrorMessage('J2000 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') -- htmInterface:getInteger: Expected integer at first position of Command.  : J2000 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SELECT 'J2000     20 240.0' , dbo.spHTM_Lookup_ErrorMessage('J2000 20 240.0') -- htmInterface:lookupIDCmd: Expect vector in Command.  : J2000 20 240.0
*/

--------------------------------------------------
-- uninstall the old stored spHTM_Cover procedure
--
IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'spHTM_Cover')
	DROP FUNCTION spHTM_Cover 
GO
--------------------------------------------------
-- install the spHTM_Cover stored procedure
--	depth is an int between 0...24
--	ra is right ascencion is float64  units are degrees
--	dec is right declination is float64  units are degrees
--	rad is circle radius in arcminutes float64 
--	x,y,z are coordinates in cartesian space, float64
--      d is a distance along the x, y, z vector when defining a cone (d in -1...+1).
--
--	a circular region: 	CIRCLE J2000  depth ra dec rad		    6 41.4 47.9 20
--				CIRCLE CARTESIAN depth x y z rad	    6 1.0 0.0 0.0 10.0
--	a convex hull:		CONVEX J2000  depth ra dec ra dec ra dec... 6 41.4 47.9 41.2 47.9 41.0 47.5 41.4 48
--				CONVEX  CARTESIAN depth x1 y1 z1 x2 y2 z2 ... xn yn zn    1 1 1 1 0 1 1 1 0
--	a domain:		DOMAIN 1 n x1 y1 z1 d1 x2 y2 z2 d2... xn yn zn dn  

CREATE FUNCTION spHTM_Cover (@Coordinates VARCHAR(8000))
RETURNS @Triangles TABLE (
		HTM_ID_START BIGINT NOT NULL PRIMARY KEY,  
		HTM_ID_END BIGINT NOT NULL)
/*Returns a result set that is rows_out triangles .*/
AS
BEGIN
   DECLARE @Vector 	VARBINARY(8000)	-- the outpvector from xp_HTM 
   DECLARE @Elements 	INT		-- size of output vector (in bigints)
   DECLARE @Cell     	INT		-- index to output vector cell
   DECLARE @StartHTM 	BIGINT		-- value of first htm cell
   DECLARE @EndHTM 	BIGINT		-- value of second htm cell
   DECLARE @Retcode 	INT		-- retcode from xp_HTM.
   DECLARE @ErrorMsg	VARCHAR(1000)   -- error message from HTM code
   -- get a vector of up to 1,000 triangles covering the desired area (input params missing at present).

   EXECUTE  @retcode = master.dbo.xp_HTM_Cover @Coordinates, @Vector OUTPUT, @ErrorMsg OUTPUT
/*   IF (@Retcode != 0) 	-- code to handle failure of xp_HTM
 	BEGIN 			--These should work but SQL2000 rejects them right now.
   	RAISERROR ('xp_HTM returned error:%d, no triangles returned.', 16, 1, @Retcode)
  	PRINT 'Stored procedure returned error: ' + CAST(@Retcode AS VARCHAR(10))
	RETURN
 	END
*/
   -- extract the triangle HTM IDs from the returned vector 
   --	 and insert the pair in the answer table
   SET @Elements = DATALENGTH(@Vector)/8		-- each element is 8 bytes
   IF ((@Elements % 2) != 0) SET @Elements = 0  -- error if an odd number of elements
   SET @Cell = 0				--
   WHILE (@Cell < @Elements)			-- loop over array
	BEGIN					-- extracting pairs of cells
	SET @StartHTM = CAST (substring(@Vector, (8 * @Cell)+1, 8) AS BIGINT)
	SET @EndHTM = CAST (substring(@Vector, (8 * (@Cell+1))+1, 8) AS BIGINT)
   	INSERT @Triangles VALUES(@StartHTM, @EndHTM+1)	-- insert pair in answer table
 	SET @Cell = @Cell + 2			-- go to next pair
	END					-- end of loop to get 
   RETURN					--
END						-- end of spHTM
GO

--------------------------------------------------
-- uninstall the old stored spHTM_Cover_ErrorMessage procedure
--
IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'spHTM_Cover_ErrorMessage')
	DROP FUNCTION spHTM_Cover_ErrorMessage
GO 
--------------------------------------------------
-- install the spHTM_Cover_ErrorMessage stored procedure
--	returns error message for area specification of an spHTM_Cover param. 
CREATE FUNCTION spHTM_Cover_ErrorMessage (@Coordinates VARCHAR(8000))
RETURNS VARCHAR(1000)
AS
BEGIN
   DECLARE @Vector 	VARBINARY(8000)	-- the outpvector from xp_HTM 
   DECLARE @retcode 	INT		-- retcode from xp_HTM.
   DECLARE @ErrorMesssage VARCHAR(1000)   -- error message from HTM code
   EXECUTE  @retcode = master.dbo.xp_HTM_Cover @Coordinates, @Vector OUTPUT, @ErrorMesssage OUTPUT
   IF (@retcode != 0)   
	BEGIN
	SET @ErrorMesssage = 'Call to master.dbo.xp_HTM failed completely'
	END 
    RETURN(@ErrorMesssage) 
END						-- end of spHTM_ErrorMessage
GO

-----------------------------------------------------------------
-- some test cases for spHTM
/* =====test cases
SELECT * FROM spHTM_Cover('CIRCLE J2000 3 41.4 47.9 .1 ') -- (1021, 1022)
SELECT * FROM spHTM_Cover('CIRCLE J2000 6 41.4 47.9 20 ') -- (65348, 65349), (65351, 65352) 
SELECT * FROM spHTM_Cover('CONVEX J2000 6 1 2 3 4 5 6 ') -- 8 cells ??(9 cells for RGM)
SELECT * FROM spHTM_Cover('DOMAIN  2 1 1 0 0 .5 1 0 1 0 .5 ')  -- 4 cells.
SELECT dbo.spHTM_Cover_ErrorMessage('CIRCLE J2001 3 41.4 47.9 .1 ') -- htmInterface:getCode: Unexpected command : J2001
SELECT dbo.spHTM_Cover_ErrorMessage('CONVEX J2000 6 1 2 3 4 5 6 ') -- OK
SELECT dbo.spHTM_Cover_ErrorMessage('DOMAIN  10 10 1 0 0 .5 1 0 1 0 .5 ') -- htmInterface:getFloat: Expected float at first position of Command.  : DOMAIN  10 10 1 0 0 .5 1 0 1 0 .5 

Create table t(HTM1 bigint,HTM2 bigint)
insert into t 
    select * from spHTM_Cover('CONVEX J2000 6 1 2 3 4 5 6 ')
select * from t 
drop table t 
 
SELECT * FROM spHTM_Cover('CIRCLE J2000 3 41.4 47.9 .1 ') 
*/
GO
--------------------------------------------------
-- uninstall the old stored spHTM_To_String procedure
--
IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'spHTM_To_String')
	DROP FUNCTION spHTM_To_String
GO 
 
--------------------------------------------------
-- spHTM_To_String (@HTM BIGINT)
--    translats an HTM to a string: face,t1,t2,...
--    face is 1..8, ti is 0..3
CREATE FUNCTION   spHTM_To_String (@HTM BIGINT)
RETURNS VARCHAR(1000)
AS
	BEGIN
	DECLARE @HTM_TEMP	BIGINT		-- eat away at @HTM as you parse it.
	DECLARE @Answer		VARCHAR(1000)	-- the answer string.
	DECLARE @Triangle 	INT		-- the triangle id (0..3)
	SET @Answer = ''			--
	SET @HTM_temp = @HTM			--
	------------------------------------------
	-- loop over the HTM pulling off a triangle till we have a faceid left (1...8)
	WHILE (@HTM_temp > 0)
		BEGIN
		IF (@HTM_temp <= 8) 		-- its a face
			BEGIN			-- add face to string.
			SET @Answer = CAST(@HTM_temp as VARCHAR(4)) + @Answer
			SET @HTM_temp  = 0
			END			-- end face case
		ELSE
			BEGIN			-- its a triangle
			SET @Triangle = @HTM_temp % 4	-- get the id into answer
			SET @Answer =  ',' +CAST(@Triangle as VARCHAR(4)) + @Answer
			SET @HTM_Temp = @HTM_temp / 4 	-- move on to next triangle
			END			-- end triangle case
		END				-- end loop
	RETURN(@Answer) 			
END			
GO	
--------------------------------------------------
-- some test cases
/*
select dbo.spHTM_To_String(65351) -- 3,3,3,3,1,0,1,3

select 		dbo.spHTM_To_String(HTM_ID_START) as HTM_ID_START,  
		dbo.spHTM_To_String(HTM_ID_END) as HTM_ID_END
from	 	spHTM_Cover('CIRCLE J2000 15 41.4 47.9 1 ') -- 22 rows

select dbo.spHTM_To_String(dbo.spHTM_Lookup('J2000 20 240.0 38.0')) -- 3,1,3,2,2,2,0,2,2,1,2,2,0,1,2,3,3,2,0,3,3,3

SELECT dbo.spHTM_Lookup_ErrorMessage('J2000 50 240 240 38')  --should give an error. -- it does
*/
GO

--

IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'fHTM_Cover')
	DROP FUNCTION fHTM_Cover 
GO
--

CREATE FUNCTION fHTM_Cover (@Area VARCHAR(8000))
---------------------------------------------------------
--/H  Returns a table of (HtmIdStart, HtmIdEnd) that covers the area.
---------------------------------------------------------
--/T This is the main access function to the HTM spatial index.
--/T <br>The result is a record set with the schema 
--/T <samp>(HtmIdStart bigint, HtmIdEnd bigint)</samp> 
--/T <br>The area is specified as a <samp> CIRCLE, CONVEX, or DOMAIN.</samp>
--/T <br>Each of these can use J2000 (ra,dec), or cartesian (xyz) coordinates.
--/T The area parameter begins with a 
--/T <samp>[CIRCLE | CONVEX] [J2000 | CARTESIAN] </samp> 
--/T header, then it has the following params:
--/T 
--/T  <li>Depth is an int between 0...24, it specifies how fine a triangular mesh is desired.
--/T  <li>ra is right ascencion is float64  units are degrees
--/T  <li>dec is right declination is float64  units are degrees
--/T  <li>rad is circle radius in arcminutes float64 
--/T  <li> x,y,z are coordinates in cartesian space, float64
--/T  <li>d is a distance along the x, y, z vector when defining a cone (d in -1...+1).
--/T <br>
--/T <br>The following examples request a 6-deep htm list covering a 
--/T     a circular region, convex hull or a domain 
--/T <br><samp>CIRCLE J2000  6 41.4 47.9 20 </samp> 
--/T <br>CIRCLE CARTESIAN 6 1.0 0.0 0.0 10.0 </samp>  
--/T <br>CONVEX J2000 6 41.4 47.9 41.2 47.9 41.0 47.5 41.4 48 </samp> 
--/T <br>CONVEX CARTESIAN depth x1 y1 z1 x2 y2 z2 ... xn yn zn </samp> 
--/T <br>CONVEX CARTESIAN 6 1 1 1 0 1 1 1 0 </samp> 
--/T <br>DOMAIN 1 n x1 y1 z1 d1 x2 y2 z2 d2... xn yn zn dn</samp> 
--/T <br>See also fHTM_Cover_ErrorMessage
------------------------------------------------------
RETURNS @Triangles TABLE (
		HTMIDstart BIGINT NOT NULL PRIMARY KEY,  
		HTMIDend   BIGINT NOT NULL)
AS
BEGIN
   DECLARE @Vector 	VARBINARY(8000)	-- the outpvector from xp_HTM 
   DECLARE @Elements 	INT		-- size of output vector (in bigints)
   DECLARE @Cell     	INT		-- index to output vector cell
   DECLARE @StartHTM 	BIGINT		-- value of first htm cell
   DECLARE @EndHTM 	BIGINT		-- value of second htm cell
   DECLARE @Retcode 	INT		-- retcode from xp_HTM.
   DECLARE @ErrorMsg	VARCHAR(1000)   -- error message from HTM code
   -- get a vector of up to 1,000 triangles covering the desired area (input params missing at present).

   EXECUTE  @retcode = master.dbo.xp_HTM_Cover @Area, @Vector OUTPUT, @ErrorMsg OUTPUT

--   IF (@Retcode != 0) 	-- code to handle failure of xp_HTM
-- 	BEGIN 			--These should work but SQL2000 rejects them right now.
--   	RAISERROR ('xp_HTM returned error:%d, no triangles returned.', 16, 1, @Retcode)
--  	PRINT 'Stored procedure returned error: ' + CAST(@Retcode AS VARCHAR(10))
--	RETURN
-- 	END

   -- extract the triangle HTM IDs from the returned vector 
   --	 and insert the pair in the answer table
   SET @Elements = DATALENGTH(@Vector)/8		-- each element is 8 bytes
   IF ((@Elements % 2) != 0) SET @Elements = 0  -- error if an odd number of elements
   SET @Cell = 0				--
   WHILE (@Cell < @Elements)			-- loop over array
	BEGIN					-- extracting pairs of cells
	SET @StartHTM = CAST (substring(@Vector, (8 * @Cell)+1, 8) AS BIGINT)
	SET @EndHTM = CAST (substring(@Vector, (8 * (@Cell+1))+1, 8) AS BIGINT)
   	INSERT @Triangles VALUES(@StartHTM, @EndHTM+1)	-- insert pair in answer table
 	SET @Cell = @Cell + 2			-- go to next pair
	END					-- end of loop to get 
   RETURN					--
END						-- end of fHTM
GO





IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'fHTM_Cover_ErrorMessage')
	DROP FUNCTION fHTM_Cover_ErrorMessage 
GO
--

CREATE FUNCTION fHTM_Cover_ErrorMessage (@Area VARCHAR(8000))
--------------------------------------------------------------------------------
--/H Returns error message for area specification of an fHTM_Cover param
--------------------------------------------------------------------------------
--/T <br> returns 'OK' if the @Area is an acceptable HTM area definition.
--/T <samp>SELECT dbo.fHTM_Cover_ErrorMessage('CONVEX J2000 6 1 2 3 4 5 6 ')<samp>
--/T <br> see fHTM_Cover for the definition of @Area.
---------------------------------------------------------------------------------
RETURNS VARCHAR(1000)
AS
BEGIN
   DECLARE @Vector 	VARBINARY(8000)	-- the outpvector from xp_HTM 
   DECLARE @retcode 	INT		-- retcode from xp_HTM.
   DECLARE @ErrorMesssage VARCHAR(1000)   -- error message from HTM code
   SET @ErrorMesssage = 'Call to master.dbo.xp_HTM failed completely'
   EXECUTE  @retcode = master.dbo.xp_HTM_Cover @Area, @Vector OUTPUT, @ErrorMesssage OUTPUT
   RETURN(@ErrorMesssage) 
END						-- end of fHTM_ErrorMessage
GO



-- next do the stuff from SSS_Nearfunctions.sql

----------------------------------------------------------------
--  Pure HTM functions (working off HTM table) V1: June 2002.
-- Based on SkyServer design of Alex Szalay and Jim Gray
--==============================================================
USE SSSv2
GO
-------------------------------------------------------------
-- fHtmLookupEq returns the htmID of a given RA,DEC
-------------------------------------------------------------
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'fHtmLookupEq' )
DROP FUNCTION fHtmLookupEq
GO
create function fHtmLookupEq (@ra float, @dec float)   
-------------------------------------------------------------
--/H Returns 20-deep HTMid of a given Equatorial point (@ra,@dec)
-------------------------------------------------------------
--/T Sample call to get the HTMid of ra,dec 185,0<br>
--/T <samp> 
--/T <br> select dbo.fGetNearbyObjEq(185,0)
--/T </samp> 
--/T <br>  
-------------------------------------------------------------
returns bigint
as 
begin
declare @cmd varchar (200)
set @cmd ='J2000 20 ' +str(@ra,15,7) +' ' +str(@dec,15,7)
--return skyserverV3.dbo.fHTM_Lookup( @cmd )
 return dbo.spHTM_Lookup( @cmd )
end
go


-------------------------------------------------------------
-- fGetNearbyObjEq Returns table of objects within @r arcmins of an Equatorial point (@ra,@dec)
-------------------------------------------------------------
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'fGetNearbyObjEq' )
DROP FUNCTION fGetNearbyObjEq
GO

CREATE FUNCTION fGetNearbyObjEq (@ra float, @dec float, @r float, @table tinyint )
-------------------------------------------------------------
--/H Returns table of objects from @table within @r arcmins of an Equatorial point (@ra,@dec)
-------------------------------------------------------------
--/T There is no limit on the number of objects returned, but there are about 40 per sq arcmin.  
--/T <p> returned table:  
--/T <li> objID bigint PRIMARY KEY,   -- Photo object identifier
--/T <li> htmID bigint,               -- Hierarchical Trangular Mesh id of this object
--/T <li> cx float NOT NULL,          -- x,y,z of unit vector to this object
--/T <li> cy float NOT NULL,
--/T <li> cz float NOT NULL,
--/T <li> distance float              -- distance in arc minutes to this object from the ra,dec.
--/T <br>
--/T Sample call to find all the Galaxies within 5 arcminutes of ra,dec 185,0<br>
--/T <samp> 
--/T <br> select * 
--/T <br> from Galaxy                         as G, 
--/T <br>      dbo.fGetNearbyObjEq(185,0,5,1) as N
--/T <br> where G.objID = N.objID
--/T </samp> 
--/T <br> see also fGetNearestObjEq, fGetNearbyObjXYZ, fGetNearestObjXYZ
-------------------------------------------------------------
  RETURNS @proxtab TABLE (
    objID bigint PRIMARY KEY,
    cx float NOT NULL,
    cy float NOT NULL,
    cz float NOT NULL,
    htmID bigint,
    distance float		-- distance in arc minutes
  ) 
  AS BEGIN
	DECLARE @d2r float, @nx float,@ny float,@nz float;
	set @d2r = PI()/180.0
	if (@r<0.1) set @r=0.1
	set @nx  = COS(@dec*@d2r)*COS(@ra*@d2r)
	set @ny  = COS(@dec*@d2r)*SIN(@ra*@d2r)
	set @nz  = SIN(@dec*@d2r)
	INSERT @proxtab	
	SELECT * FROM dbo.fGetNearbyObjXYZ(@nx,@ny,@nz,@r,@table);
  RETURN
  END
GO


IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'spNearestObjEq' )
DROP PROCEDURE spNearestObjEq
GO
-- 

CREATE PROCEDURE spNearestObjEq
	@ra  float, 
	@dec float, 
	@r   float,
	@table tinyint
------------------------------------------------------------------
--/H Returns the nearest @table object to @ra, @dec within @r arcmins
--/T <br>For the Navigator. Returns the nearest object to a given point
--/T <br> returns run (int), objId (bigint), 
--/T <br>    distance (in arcminutes rounded to two decimal places)
--/T <br> (ra,dec) given in degrees. The radius r is measured in arcmins.
--/T <br><samp> EXEC spNearestObjEq 180.0 -0.5 1.2 1 </samp>
------------------------------------------------------------------
AS
    SELECT TOP 1
	P.run,
	P.[objID] AS 'id',
	LTRIM(STR(P.ra,10,5))as ra,
	LTRIM(STR(P.dec,8,5)) as dec,
 	LTRIM(STR(distance*60,7,3)) AS 'dist'
    FROM fGetNearbyObjEq (@ra, @dec, @r, @table) as H, PhotoPrimary as P
    WHERE H.objID = P.objID
	AND P.i>0
	ORDER BY H.distance ASC;
GO

IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'fGetNearestObjEq')
DROP FUNCTION fGetNearestObjEq
GO

CREATE FUNCTION fGetNearestObjEq (@ra float, @dec float, @r float, @table tinyint)
-------------------------------------------------------------
--/H Returns table holding a record describing the closest object within @r arcminutes of (@ra,@dec).
-------------------------------------------------------------
--/T <p> returned table: 
--/T <li> objID bigint PRIMARY KEY,   -- Photo object identifier
--/T <li> cx float NOT NULL,          -- x,y,z of unit vector to this object
--/T <li> cy float NOT NULL,
--/T <li> cz float NOT NULL,
--/T <li> htmID bigint,               -- Hierarchical Trangular Mesh id of this object
--/T <li> distance float              -- distance in arc minutes to this object from the ra,dec.
--/T <br
--/T Sample call to find the table 1 object within 2 arcminutes of ra,dec 185 
--/T <br><samp>
--/T <br> select N.* 
--/T <br> from   dbo.fGetNearestObjEq(185,0,2,1)   
--/T </samp>  
--/T <br> see also fGetNearbyObjEq, fGetNearbyObjXYZ, fGetNearestObjXYZ
-------------------------------------------------------------
  RETURNS @proxtab TABLE (
    objID bigint PRIMARY KEY,
    cx float NOT NULL,
    cy float NOT NULL,
    cz float NOT NULL,
    htmID bigint,
    distance float		-- distance in arc minutes
  ) AS BEGIN
	DECLARE @d2r float,@nx float,@ny float,@nz float;
	set @d2r = PI()/180.0
	set @nx  = COS(@dec*@d2r)*COS(@ra*@d2r)
	set @ny  = COS(@dec*@d2r)*SIN(@ra*@d2r)
	set @nz  = SIN(@dec*@d2r)
	INSERT @proxtab	
	SELECT top 1 * FROM dbo.fGetNearbyObjXYZ(@nx,@ny,@nz,@r, @table);
  RETURN
  END
GO

IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'fGetNearestObjIdEq')
DROP FUNCTION fGetNearestObjIdEq
GO

CREATE FUNCTION fGetNearestObjIdEq(@ra float, @dec float, @r float, @table tinyint)
-------------------------------------------------
--/H Returns the objId of nearest @table object within @r arcmins
-------------------------------------------------
--/T This scalar function is used for matchups of external catalogs.
--/T It calls the <samp>fGetNearestObjEq(@ra,@dec,@r)</samp>, and selects
--/T the objId (a bigint). This can be called by a single SELECT from an uploaded
--/T (ra,dec) table.
--/T <br>An example: 
--/T <br><samp>
--/T <br>  SELECT id, ra,dec, dbo.fGetNearestObjIdEq(ra,dec,3.0,1) as objId
--/T <br>      FROM #upload
--/T <br>      WHERE dbo.fGetNearestObjIdEq(ra,dec,3.0,1) IS NOT NULL
--/T</samp><p>
-------------------------------------------------
RETURNS bigint
AS BEGIN
    RETURN (select objID from dbo.fGetNearestObjEq(@ra,@dec,@r,@table));
END
GO


IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'fGetNearbyObjXYZ')
DROP FUNCTION fGetNearbyObjXYZ
GO

CREATE FUNCTION fGetNearbyObjXYZ (@nx float, @ny float, @nz float, @r float, @table tinyint)
-------------------------------------------------------------
--/H Returns table of @table objects within @r arcmins of an xyz point (@nx,@ny, @nz).
-------------------------------------------------------------
--/T There is no limit on the number of objects returned, but there are about 40 per sq arcmin.
--/T <p>returned table:  
--/T <li> objID bigint PRIMARY KEY,   -- Photo object identifier
--/T <li> cx float NOT NULL,          -- x,y,z of unit vector to this object
--/T <li> cy float NOT NULL,
--/T <li> cz float NOT NULL,
--/T <li> htmID bigint,               -- Hierarchical Trangular Mesh id of this object
--/T <li> distance float              -- distance in arc minutes to this object from the ra,dec.
--/T <br> Sample call to find @table objects within 5 arcminutes of xyz -.0996,-.1,0
--/T <br><samp>
--/T <br>select *
--/T <br>from  dbo.fGetNearbyObjXYZ(-.996,-.1,0,5,1)  
--/T </samp>  
--/T <br>see also fGetNearbyObjEq, fGetNearestObjXYZ, fGetNearestObjXYZ
-------------------------------------------------------------
  RETURNS @proxtab TABLE (
    objID bigint PRIMARY KEY,
    cx float NOT NULL,
    cy float NOT NULL,
    cz float NOT NULL,
    htmID bigint,
    distance float		-- distance in arc minutes
  ) AS BEGIN
	DECLARE @d2r float, 
	    @cc float,@level int,@shift int,@cmd varchar(256)
	if (@r<0.1) set @r=0.1
	set @d2r = PI()/180.0
				       -- pick a granularity (depth) for the HTM
	set @level = CONVERT(int,(13- FLOOR(LOG(@r)/LOG(2.0))))
	if (@level<5)  set @level=5
	if (@level>13) set @level=13
	set @shift = CONVERT(int,POWER(4.,20-@level)) -- 4 = 2^2 and 2 bits per htm level  
	set @cmd = 'CIRCLE CARTESIAN '+str(@level)+' '
			+str(@nx,22,15)+' '+str(@ny,22,15)+' '+str(@nz,22,15)
			+ ' ' + str(@r,10,2)
	INSERT @proxtab	SELECT 
	    objID, 
	    cx,
	    cy,
	    cz,
	    htmID,
 	    2*DEGREES(ASIN(sqrt(power(@nx-cx,2)+power(@ny-cy,2)+power(@nz-cz,2))/2))*60 
	    --sqrt(power(@nx-cx,2)+power(@ny-cy,2)+power(@nz-cz,2))/@d2r*60 
	    FROM dbo.fHTM_Cover(@cmd) , HTMtable
	    WHERE tableID = @table
	    AND (HTMID BETWEEN  HTMIDstart*@shift AND HTMIDend*@shift)
	    AND ( (2*DEGREES(ASIN(sqrt(power(@nx-cx,2)+power(@ny-cy,2)+power(@nz-cz,2))/2))*60)< @r) 
	ORDER BY (2*DEGREES(ASIN(sqrt(power(@nx-cx,2)+power(@ny-cy,2)+power(@nz-cz,2))/2))*60) ASC
	OPTION(FORCE ORDER, LOOP JOIN)
  RETURN
  END
GO


IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'fGetNearestObjXYZ')
DROP FUNCTION fGetNearestObjXYZ
GO


CREATE FUNCTION fGetNearestObjXYZ (@nx float, @ny float, @nz float, @r float, @table tinyint)
-------------------------------------------------------------
--/H Returns nearest @table object within @r arcminutes of an xyz point (@nx,@ny, @nz).
-------------------------------------------------------------
--/T <p> returned table:  
--/T <li> objID bigint PRIMARY KEY,   -- Photo object identifier
--/T <li> cx float NOT NULL,          -- x,y,z of unit vector to this object
--/T <li> cy float NOT NULL,
--/T <li> cz float NOT NULL,
--/T <li> htmID bigint,               -- Hierarchical Trangular Mesh id of this object
--/T <li> distance float              -- distance in arc minutes to this object from the ra,dec.
--/T <br>
--/T Sample call to find the nearest @table object within 5 arcminutes of xyz -.0996,-.1,0 
--/T <br><samp>
--/T  <br> select *
--/T  <br> from  dbo.fGetNearestObjXYZ(-.996,-.1,0,5,@table)  
--/T  </samp>  
--/T  <br>see also fGetNearbyObjEq, fGetNearestObjEq, fGetNearbyObjXYZ, 
-------------------------------------------------------------
  RETURNS @proxtab TABLE (
    objID bigint PRIMARY KEY,
    cx float NOT NULL,
    cy float NOT NULL,
    cz float NOT NULL,
    htmID bigint,
    distance float		-- distance in arc minutes
  ) AS BEGIN
	INSERT @proxtab	
	SELECT top 1 * FROM dbo.fGetNearbyObjXYZ(@nx,@ny,@nz,@r,@table);
  RETURN
  END
GO



PRINT 'Proximity functions created'
GO

-- next move onto doing the stuff from "Filling in HTM x y z.sal 


use sssv2
go
update dbo.surveyCatalog
set cx = Cos(RADIANS(ra))*Cos(RADIANS(dec)),  
    cy = sin(RADIANS(ra))*cos(RADIANS(dec)), 
    cz = sin(RADIANS(dec)),
    htmID = dbo.fHtmLookupEq(ra,dec)
go

update  dbo.fieldInfo
set htmID = dbo.fHtmLookupEq(nominalRa,nominalDec)
go


update  dbo.mergedCatalog
set cx = Cos(RADIANS(ra))*Cos(RADIANS(dec)),  
    cy = sin(RADIANS(ra))*cos(RADIANS(dec)), 
    cz = sin(RADIANS(dec)),
    htmID = dbo.fHtmLookupEq(ra,dec)
go

create table HTMtable
	(HtmID	bigint not null, 	-- the object's HTM ID
	objID	bigint not null, 	-- the object's ID
	cx	real   not null,	-- celestial coordinates
	cy	real   not null,
	cz	real   not null,
	tableID tinyint not null,	-- the table this HTM comes from
	primary key (tableID,HtmID,ObjID) -- cluster by table.
	) 
go


create table HTMtableID 
	(tableID tinyint not null primary key,
	 Name	varchar(256) not null
	)
go

truncate table HTMtableID
insert into HTMtableID values (1, 'MergedCatalog')
insert into HTMtableID values (2, 'SurveyInfo')
insert into HTMtableID values (3, 'SurveyCatalog')
insert into HTMtableID values (4, 'PlateInfo')
insert into HTMtableID values (5, 'FieldInfo')		
go

insert HTMtable
  select htmID, objID, cx, cy, cz, 1 from MergedCatalog
go
insert HTMtable
  select htmID, objID, cx, cy, cz, 3 from SurveyCatalog
go

insert HTMtable
  select htmID, cast(fieldID as bigint), 
	cx = Cos(RADIANS(nominalRa))*Cos(RADIANS(nominalDec)),  
    	cy = sin(RADIANS(nominalRa))*cos(RADIANS(nominalDec)), 
    	cz = sin(RADIANS(nominalDec)) ,
	5 
	from FieldInfo
go	 

----- now add Jim Gray's performance monitoring stuff from SkyServer_MyTimeX.sql

--========================================================
-- Scripts for Performance measurements
-- Nov-13-2001 Jim Gray
--========================================================
--USE SkyServerV3
SET NOCOUNT ON
GO


CREATE TABLE QueryResults (
-------------------------------------------------------
--/H Store the results of performance tests here
-------------------------------------------------------
	query	 	varchar(100)	NOT NULL, 		--/D query name
	cpu_sec		float 		NOT NULL,		--/D CPU time  --/U sec
	elapsed_time	float 		NOT NULL,		--/D elapsed time --/U sec
      	physical_IO	float 		NOT NULL,		--/D Physical IO
        row_count	int 		NOT NULL,		--/D row count
	[time]		DateTime DEFAULT CURRENT_TIMESTAMP 	--/D timestamp
)
GO
--


IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'InitTimeX' 
	   AND 	  type = 'P')
    DROP PROCEDURE InitTimeX
GO


CREATE PROCEDURE InitTimeX	@clock 	     datetime	OUTPUT,
				@cpu 	     int        OUTPUT, 
        			@physical_io int	OUTPUT
----------------------------------------------------------------------
--/H Starts the wall, cpu, and IO clocks for performance testing 
----------------------------------------------------------------------
--/T  parameters are:
--/T  <li> clock (output): current 64bit wallclock datetime
--/T      <li> cpu (output):  an int of cpu milliseconds (wraps frequently so gives bogus answers)
--/T      <li> physical_Io (output): count of disk reads and writes
--/T  <br>
--/T  Here is an example that uses InitTimeX and EndTimeX to record the cost of 
--/T  some SQL statements. The example both records the results in the QueryResults table
--/T  and also prints out a message summarizing the test (that is what the 1,1 flags are for.)
--/T  <samp>
--/T  <br>declare @clock datetime, @cpu int, @physical_io int,  @elapsed int;
--/T  <br>exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT
--/T  <br>  .... do some SQL work....
--/T  <br>exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT, 
--/T  <br>                          'This is a TimeX test', 1, 1, @@RowCount 
--/T  </samp><p>
--/T  see also EndTimeX
---------------------------------------------------
AS	BEGIN
	select	@cpu   = sum(cpu), 
       		@physical_io = sum(physical_io)
	from master.dbo.sysprocesses;
	set	@clock = getdate();
	RETURN;
	END;
GO 
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'EndTimeX' 
	   AND 	  type = 'P')
    DROP PROCEDURE EndTimeX
GO


CREATE PROCEDURE EndTimeX	@clock 	     datetime,
				@elapsed     int        OUTPUT,
				@cpu 	     int        OUTPUT, 
        			@physical_io int	OUTPUT,
                                @comment     varchar(100) = '', 
				@print       int        =0,
				@table       int        =0,
				@row_count   int        =0
---------------------------------------------------
--/H Stops the clock for performance testing
---------------------------------------------------
--/T  <p>parameters are (inputs should be set with InitTimeX as shown in example):
--/T       <li> clock (input)       : current 64bit wallclock datetime
--/T       <li> elapsed (output)    : elapsed milliseconds of wall clock time  
--/T       <li> cpu (input, output) : an int of milliseconds of cpu time (wraps frequently so gives bogus answers)
--/T       <li> physical_Io (input, output): count of disk reads and writes
--/T       <li> commment (input)    : text string describing the experiment
--/T       <li> print (input)       : flag, if true prints the output statistics on the console (default =no)
--/T       <li> table (input)       : flag, if true inserts the statistics in the QueryResults table (default = no)
--/T       <li> row_Count(input)    : passed in @@RowCount for statistics
--/T  <br>Here is an example that uses InitTimeX and EndTimeX to record the cost of 
--/T  some SQL statements. The example both records the results in the QueryResults table
--/T  and also prints out a message summarizing the test (that is what the 1,1 flags are for.)
--/T  <samp>
--/T  <br>declare @clock datetime, @cpu int, @physical_io int,  @elapsed int;
--/T  <br>exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT
--/T  <br>  .... do some SQL work....
--/T  <br>exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT, 
--/T  <br>                          'This is a TimeX test', 1, 1, @@RowCount 
--/T  </samp>
--/T  <br>see also InitTimeX
---------------------------------------------------
AS	BEGIN
	set @elapsed = datediff(ms, @clock, getdate());
	select 	@cpu = sum(cpu)- @cpu, 
       		@physical_io = sum(physical_io) -@physical_io
	from master.dbo.sysprocesses;
        if (@print >0)
           BEGIN
           print @comment
               + ' cpu: '              + str(@cpu/1000.0,    8,2)   
               + ' sec, elapsed: '     + str(@elapsed/1000.0,8,2) 
               + ' sec, physical_io: ' + cast(@physical_io as varchar(20))
               + ' row_count: '        + cast(@row_count as varchar(20)); 
           END
        if (@table > 0)
           BEGIN
           insert dbo.QueryResults
              values(@comment, @cpu/1000.0, @elapsed/1000.0, @physical_io, @row_count,CURRENT_TIMESTAMP);
           END          
	RETURN;
	END;
GO
 

-- define fGreatCircleDist function

IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'fGreatCircleDist' )
DROP FUNCTION fGreatCircleDist

CREATE FUNCTION fGreatCircleDist (@ra1 float, @dec1 float, @ra2 float, @dec2 float)
---------------------------------------------------------------
----/H Returns float holding great circle distance [in arcsec] 
----/H between two points (ra1,dec1) & (ra2,dec2) [ras,decs in radians] 
---------------------------------------------------------------

RETURNS float
AS 
begin
return 3600.0*2.0*degrees(asin(sqrt(square(sin(0.5*(@dec1-@dec2))) + cos(@dec1)*cos(@dec2)* square(sin(0.5*(@ra1-@ra2))))))
END
GO

-- what to define SSS-SDSS cross-neighbours table, based on
-- SkyServer_CreateNrighbors.sql
-- 
-- as in SkyServer case, match objects within 30 arcsec

--====================================================
-- Load the PTag table with values from PhotoObj
-- only stars, galaxies, ... (no non objects)
DECLARE @Primary binary(4);
SET @Primary = skyserverv3.dbo.fPhotoStatus('Primary');
SELECT htmID, objID, cx,cy,cz, type,
 	 (CASE WHEN (status & @Primary ) > 0 THEN 1 ELSE 0 END) as isPrimary
INTO PTag
FROM  skyserverv3.dbo.PhotoObj  with(index(1))
WHERE type NOT IN (1, 2, 4, 7, 8)  -- not cosmic ray, defect, ghost, trail, or sky.
-- executed in 0:27, created 157,678 rows
GO



create table crossNeighbours(
htmid bigint not null, 
sdssid bigint not null,
sssid bigint not null,
distanceMins real not null,
sdsstype int not null)


--====================================================
-- The following program can take a VERY long time.
-- it computes all the primaries within 1/2 arcminte of 
-- of each primary and inserts the result in the neighbors
-- table.  On average this is about 10 objects per object.
-- it has restart logic built in to continue where it left off
-- starting just after the last objID it did.
--============================================
SET NOCOUNT ON;
SET IMPLICIT_TRANSACTIONS ON;
DECLARE @MaxDone BIGINT;
DECLARE @htmID BIGINT;
DECLARE @objID BIGINT;
DECLARE @type TINYINT;
DECLARE @isPrimary TINYINT;
DECLARE @x  FLOAT;
DECLARE @y  FLOAT;
DECLARE @z  FLOAT;
DECLARE @r   FLOAT;
DECLARE @count INT;
DECLARE @FETCH_STATUS INT;
-- Restart logic.
-- Neighbors are computed in ObjectID order, so
-- (re)start just after the "highest" object ID already done..
SET @MaxDone = (select max(htmID) from CrossNeighbours); 
IF (@MaxDone IS NULL) SET @MaxDone = 0;
PRINT 'MaxDone is: ' + cast (@MaxDone as varchar(32));
SET @count = 0;
SET @r =  .5; -- "nearby" radius is 1/2 arcminute.
DECLARE ObjCursor CURSOR
   FOR SELECT  htmID, objID, cx,cy,cz, type,isPrimary
            FROM  PTag
		WHERE    htmID > @MaxDone
		ORDER BY htmID ASC;
SET @FETCH_STATUS = 0;
OPEN ObjCursor;			 -- will reopen at each new transaction
--==============================================================
-- Now for each object, record neighbors within 30 arc seconds.
WHILE (@FETCH_STATUS = 0)               -- do till end of  table.
     BEGIN
--     OPEN ObjCursor;			 -- will reopen at each new transaction
     WHILE(@FETCH_STATUS = 0)
            BEGIN
            FETCH NEXT FROM Objcursor
                        INTO @htmID, @objID,  @x, @y, @z, @type, @isPrimary; 
            SET @FETCH_STATUS = @@FETCH_STATUS;
            IF (@FETCH_STATUS != 0) BREAK;
            INSERT INTO CrossNeighbours
                        SELECT  @htmID, @objID, N.objID, N.distance, @type
                        FROM dbo.fGetNearbyObjXYZ(@x, @y, @z ,@r, 1) AS N
                        SET @MaxDone = @htmID;
	    SET @count = @count + 1;
            IF ((@count % 1000)= 0) BREAK;  -- for debugging
            END;
     COMMIT TRANSACTION;
--     print ' Objects done: ' + cast(@count as varchar(20));
--     IF (@count >10000) break
     END;

--================================================================
-- cleanup, close and deallocate the cursor.   
CLOSE ObjCursor;
DEALLOCATE ObjCursor;
PRINT 'Neighbors Created'
GO
SET IMPLICIT_TRANSACTIONS OFF;
GO
--SELECT count(*) from CrossNeighbours;
--select min(distancemins) from crossneighbours
--select top 100 * from crossneighbours
-- 681,011 rows in 15 mins. 


-- now do similar for SSS neighbours table
drop table neighbours

create table Neighbours(
htmid bigint not null, 
objid bigint not null,
neighbourobjid bigint not null,
distanceMins real not null)

SET NOCOUNT ON;
SET IMPLICIT_TRANSACTIONS ON;
DECLARE @MaxDone BIGINT;
DECLARE @htmID BIGINT;
DECLARE @objID BIGINT;
DECLARE @x  FLOAT;
DECLARE @y  FLOAT;
DECLARE @z  FLOAT;
DECLARE @r   FLOAT;
DECLARE @count INT;
DECLARE @FETCH_STATUS INT;
-- Restart logic.
-- Neighbors are computed in ObjectID order, so
-- (re)start just after the "highest" object ID already done..
SET @MaxDone = (select max(htmID) from Neighbours); 
IF (@MaxDone IS NULL) SET @MaxDone = 0;
PRINT 'MaxDone is: ' + cast (@MaxDone as varchar(32));
SET @count = 0;
SET @r =  .5; -- "nearby" radius is 1/2 arcminute.
DECLARE ObjCursor CURSOR
   FOR SELECT  htmID, objID, cx,cy,cz
            FROM  mergedcatalog
		WHERE    htmID > @MaxDone
		ORDER BY htmID ASC;
SET @FETCH_STATUS = 0;
OPEN ObjCursor;			 -- will reopen at each new transaction
--==============================================================
-- Now for each object, record neighbors within 30 arc seconds.
WHILE (@FETCH_STATUS = 0)               -- do till end of  table.
     BEGIN
--     OPEN ObjCursor;			 -- will reopen at each new transaction
     WHILE(@FETCH_STATUS = 0)
            BEGIN
            FETCH NEXT FROM Objcursor
                        INTO @htmID, @objID,  @x, @y, @z
            SET @FETCH_STATUS = @@FETCH_STATUS;
            IF (@FETCH_STATUS != 0) BREAK;
            INSERT INTO Neighbours
                        SELECT  @htmID, @objID, N.objID, N.distance
                        FROM dbo.fGetNearbyObjXYZ(@x, @y, @z ,@r, 1) AS N
			where @objid !=n.objid -- can't be own neighbour
                        SET @MaxDone = @htmID;
	    SET @count = @count + 1;
            IF ((@count % 1000)= 0) BREAK;  -- for debugging
            END;
     COMMIT TRANSACTION;
--     print ' Objects done: ' + cast(@count as varchar(20));
--     IF (@count >10000) break
     END;

--================================================================
-- cleanup, close and deallocate the cursor.   
CLOSE ObjCursor;
DEALLOCATE ObjCursor;
PRINT 'Neighbors Created'
GO
SET IMPLICIT_TRANSACTIONS OFF;
GO

select count(*) from neighbours -- 548,914 in 8:50 

select top 10 * from neighbours 

