--/T
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
--/T
--/T
select dbo.fGetNearbyObjEq(185,0)
--/T
--/T
-------------------------------------------------------------
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 returned table:
--/T
objID bigint PRIMARY KEY, -- Photo object identifier
--/T htmID bigint, -- Hierarchical Trangular Mesh id of this object
--/T cx float NOT NULL, -- x,y,z of unit vector to this object
--/T cy float NOT NULL,
--/T cz float NOT NULL,
--/T distance float -- distance in arc minutes to this object from the ra,dec.
--/T
--/T Sample call to find all the Galaxies within 5 arcminutes of ra,dec 185,0
--/T
--/T
select *
--/T
from Galaxy as G,
--/T
dbo.fGetNearbyObjEq(185,0,5,1) as N
--/T
where G.objID = N.objID
--/T
--/T
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
For the Navigator. Returns the nearest object to a given point
--/T
returns run (int), objId (bigint),
--/T
distance (in arcminutes rounded to two decimal places)
--/T
(ra,dec) given in degrees. The radius r is measured in arcmins.
--/T
EXEC spNearestObjEq 180.0 -0.5 1.2 1
------------------------------------------------------------------
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 returned table:
--/T
objID bigint PRIMARY KEY, -- Photo object identifier
--/T cx float NOT NULL, -- x,y,z of unit vector to this object
--/T cy float NOT NULL,
--/T cz float NOT NULL,
--/T htmID bigint, -- Hierarchical Trangular Mesh id of this object
--/T distance float -- distance in arc minutes to this object from the ra,dec.
--/T
--/T
select N.*
--/T
from dbo.fGetNearestObjEq(185,0,2,1)
--/T
--/T
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 fGetNearestObjEq(@ra,@dec,@r), and selects
--/T the objId (a bigint). This can be called by a single SELECT from an uploaded
--/T (ra,dec) table.
--/T
An example:
--/T
--/T
SELECT id, ra,dec, dbo.fGetNearestObjIdEq(ra,dec,3.0,1) as objId
--/T
FROM #upload
--/T
WHERE dbo.fGetNearestObjIdEq(ra,dec,3.0,1) IS NOT NULL
--/T
-------------------------------------------------
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
returned table:
--/T
objID bigint PRIMARY KEY, -- Photo object identifier
--/T cx float NOT NULL, -- x,y,z of unit vector to this object
--/T cy float NOT NULL,
--/T cz float NOT NULL,
--/T htmID bigint, -- Hierarchical Trangular Mesh id of this object
--/T distance float -- distance in arc minutes to this object from the ra,dec.
--/T
Sample call to find @table objects within 5 arcminutes of xyz -.0996,-.1,0
--/T
--/T
select *
--/T
from dbo.fGetNearbyObjXYZ(-.996,-.1,0,5,1)
--/T
--/T
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 returned table:
--/T
objID bigint PRIMARY KEY, -- Photo object identifier
--/T cx float NOT NULL, -- x,y,z of unit vector to this object
--/T cy float NOT NULL,
--/T cz float NOT NULL,
--/T htmID bigint, -- Hierarchical Trangular Mesh id of this object
--/T distance float -- distance in arc minutes to this object from the ra,dec.
--/T
--/T Sample call to find the nearest @table object within 5 arcminutes of xyz -.0996,-.1,0
--/T
--/T
select *
--/T
from dbo.fGetNearestObjXYZ(-.996,-.1,0,5,@table)
--/T
--/T
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 clock (output): current 64bit wallclock datetime
--/T cpu (output): an int of cpu milliseconds (wraps frequently so gives bogus answers)
--/T physical_Io (output): count of disk reads and writes
--/T
--/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
--/T
declare @clock datetime, @cpu int, @physical_io int, @elapsed int;
--/T
exec dbo.InitTimeX @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT
--/T
.... do some SQL work....
--/T
exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
--/T
'This is a TimeX test', 1, 1, @@RowCount
--/T
--/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
parameters are (inputs should be set with InitTimeX as shown in example):
--/T
clock (input) : current 64bit wallclock datetime
--/T elapsed (output) : elapsed milliseconds of wall clock time
--/T cpu (input, output) : an int of milliseconds of cpu time (wraps frequently so gives bogus answers)
--/T physical_Io (input, output): count of disk reads and writes
--/T commment (input) : text string describing the experiment
--/T print (input) : flag, if true prints the output statistics on the console (default =no)
--/T table (input) : flag, if true inserts the statistics in the QueryResults table (default = no)
--/T row_Count(input) : passed in @@RowCount for statistics
--/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
--/T
declare @clock datetime, @cpu int, @physical_io int, @elapsed int;
--/T
exec dbo.InitTimeX @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT
--/T
.... do some SQL work....
--/T
exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
--/T
'This is a TimeX test', 1, 1, @@RowCount
--/T
--/T
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