Introduction

This is just a quick summary of MD PV2 into PSPS

• all MDs have been created in dvo, calibrated, and loaded onto the datastore.
• as of 9-26-2014, 2,3,4,5,6 are ready to be queried
• as of Oct 2014, all are ready to be queried.

Summary

This is a quick summary of what is below. I have investigated using 3 different methods the data integrity of the MD fields. Details are given below, but here are the results:

• Method 1 - plots of coverage of random ra / dec
• Method 2 - plots of coverage of random ra / dec / batchid
• Method 3 - comparison of counts in the ipptopsps database to counts in the psps database

MD field	Detection Table	Stack Detection	Object	Comments
	used methods 1 & 3	used methods 1 & 3	used methods 1, 2 & 3
MD01	missing detections	complete	psps is missing batches
MD02	complete	complete	looks complete
MD03	complete	complete	looks complete
MD04	complete	complete	looks complete
MD05	missing detections	complete	psps is missing detections
MD06	psps is missing batches	complete	psps is missing batches
MD07	complete 11-26-2014	complete 11-26-2014	psps is missing batches
MD08	complete	complete	psps is missing batches
MD09	complete	complete	psps is missing batches
MD10	complete	complete	psps is missing batches

bold is used for those parts that are complete (no problems detected)

psps is missing batches - the batches exist on the ipptopsps datastore, but have not been loaded by psps

missing detections - the batch has been partially loaded onto psps

duplicates present - the batch has been loaded onto psps, and there are duplicates (like it loaded into a slice twice)

Plots of coverage

I did a selection of random 100,000 ras and decs from detectionFull, stackDetectionFull, and Object for each field. The results are plotted below, you can click on the image to make it bigger.

the queries I used for each MD (on production cluster):

select top 100000 ra, dec from detectionFull order by newid() 
select top 100000 ra, dec from stackDetectionFull order by newid() 
select top 100000 ra, dec from Object order by newid() 

The results (see images below):

MD01 DetectionFull, MD02 DetectionFull have stripe-like problems MD07 StackDetectionFull has a stripe-like problems

md field	detectionFull	stackDetectionFull	object
md01
md02
md03
md04
md05
md06
md07
md08
md09
md10

Plots with batchid or mag

There is a problem where a number of object batches failed to be made. The black/blue colors show the missing holes for the Object Tables

select top 100000 ra, dec, objid from object order by newid()

MD field	plot of batch id	comments
MD01
MD02		no holes
MD03		no holes
MD04		no holes
MD05
MD06
MD07
MD08
MD09
MD10

Comparisions of ndetections

Another way to check the integrity is to do comparisons of the number of detections for each batch. in the ipptopsps.batch table, there is a column (ndetections) that is the number of detections published (per batch). The following queries make counts of the number of detections for each of stack/detection and grouped by batchid:

select batchid, count(objid) INTO mydb.[md06detcounts2] from Detection join ImageMeta on Detection.imageID = ImageMeta.imageID join FrameMeta on FrameMeta.frameID = imageMeta.frameID group by batchid

select batchid, count(*) INTO mydb.[md01stackdetcounts] from StackDetectionFull join StackMeta on StackDetectionFull.StackMetaID = StackMeta.StackMetaID group by batchid

I wrote a perl script to compare the numbers between these 2 databases. I find the following

MD field	Detections	Stacks	Objects
MD01	1565 out of 3797 batches are missing detections	all good	9 batches missing
MD02	all good	all good	all good
MD03	all good	all good	all good
MD04	all good	all good	all good
MD05	2236 out of 3836 batches are missing detections	all good	8 batches (partial slices) missing
MD06	21 batches are not loaded at all	all good	16 batches missing
MD07	all good (11-26)	all good (11-26)	9 batches missing
MD08	all good	all good	5 batches missing
MD09	all good	all good	16 batches missing
MD10	all good	all good	1 batch missing

• extra detections - means that psps has extra detections compared to what we expect from ipptopsps
• missing detections - means that psps has fewer detections compared to what we expect from ipptopsps

So far, MD02/MD03/MD04/MD06 are consistent (for the Stack/Detection tables)

MD01, MD05 has problems with Detections (missing)

MD07 has problems with Stack Detections and Detections (duplicates) - md07 stacks/detections fixed 11-26

Missing Objects

datastore       batchID    expected  actual
PSPS_MD05_PV2   1456102    99     49 
PSPS_MD05_PV2   1456162    1799412     1308744 
PSPS_MD05_PV2   1456403    1997374     1412886 
PSPS_MD05_PV2   1456485    2042818     1438106 
PSPS_MD05_PV2   1456840    2005540     1415084 
PSPS_MD05_PV2   1457167    1985096     1388370 
PSPS_MD05_PV2   1457379    122883     104795 
PSPS_MD05_PV2   1457385    767183     595352 
PSPS_MD09_PV2   1472702 is missing (not loaded) on psps
PSPS_MD01_PV2   1472843 is missing (not loaded) on psps
PSPS_MD09_PV2   1472564 is missing (not loaded) on psps
PSPS_MD06_PV2   1451985 is missing (not loaded) on psps
PSPS_MD09_PV2   1472797 is missing (not loaded) on psps
PSPS_MD06_PV2   1452062 is missing (not loaded) on psps
PSPS_MD06_PV2   1452103 is missing (not loaded) on psps
PSPS_MD06_PV2   1452041 is missing (not loaded) on psps
PSPS_MD09_PV2   1472519 is missing (not loaded) on psps
PSPS_MD07_PV2   1477840 is missing (not loaded) on psps
PSPS_MD06_PV2   1452017 is missing (not loaded) on psps
PSPS_MD08_PV2   1485856 is missing (not loaded) on psps
PSPS_MD06_PV2   1452018 is missing (not loaded) on psps
PSPS_MD01_PV2   1472561 is missing (not loaded) on psps
PSPS_MD01_PV2   1472520 is missing (not loaded) on psps
PSPS_MD06_PV2   1452104 is missing (not loaded) on psps
PSPS_MD09_PV2   1472863 is missing (not loaded) on psps
PSPS_MD06_PV2   1452261 is missing (not loaded) on psps
PSPS_MD01_PV2   1472725 is missing (not loaded) on psps
PSPS_MD06_PV2   1451948 is missing (not loaded) on psps
PSPS_MD08_PV2   1485921 is missing (not loaded) on psps
PSPS_MD07_PV2   1477695 is missing (not loaded) on psps
PSPS_MD09_PV2   1472648 is missing (not loaded) on psps
PSPS_MD09_PV2   1472144 is missing (not loaded) on psps
PSPS_MD06_PV2   1452102 is missing (not loaded) on psps
PSPS_MD07_PV2   1477873 is missing (not loaded) on psps
PSPS_MD01_PV2   1472778 is missing (not loaded) on psps
PSPS_MD07_PV2   1477930 is missing (not loaded) on psps
PSPS_MD07_PV2   1477533 is missing (not loaded) on psps
PSPS_MD07_PV2   1478103 is missing (not loaded) on psps
PSPS_MD09_PV2   1472755 is missing (not loaded) on psps
PSPS_MD07_PV2   1477603 is missing (not loaded) on psps
PSPS_MD09_PV2   1473031 is missing (not loaded) on psps
PSPS_MD09_PV2   1472464 is missing (not loaded) on psps
PSPS_MD09_PV2   1472363 is missing (not loaded) on psps
PSPS_MD06_PV2   1452061 is missing (not loaded) on psps
PSPS_MD09_PV2   1472081 is missing (not loaded) on psps
PSPS_MD06_PV2   1452060 is missing (not loaded) on psps
PSPS_MD10_PV2   1495720 is missing (not loaded) on psps
PSPS_MD06_PV2   1452040 is missing (not loaded) on psps
PSPS_MD01_PV2   1471707 is missing (not loaded) on psps
PSPS_MD09_PV2   1472418 is missing (not loaded) on psps
PSPS_MD06_PV2   1452214 is missing (not loaded) on psps
-----------
expected number of batch: 387 
number of batches seeon on psps: 354 
number of batches which are bad (missing more than 1% of detections): 8 
bad ones, count where there are more expected: 0 
bad ones, count where there are less than expected: 8 
missing ones, where 0 are loaded into psps: 43

---

Load missing OB batches

---

The query to get all in WMD:

select D.* from WMD.StateReg.Batch B, WMD.StateReg.DbObject D, WMD.StateReg.[Load] L 
where L.pkeyBatch = B.pkey AND L.pkeyDbObject = D.pkey AND
B.BatchName in ( 
'M09_OB_B01472702',
'M01_OB_B01472843',
'M09_OB_B01472564',
'M06_OB_B01451985',
'M09_OB_B01472797',
'M06_OB_B01452062',
'M06_OB_B01452103',
'M06_OB_B01452041',
'M09_OB_B01472519',
'M07_OB_B01477840',
'M06_OB_B01452017',
'M08_OB_B01485856',
'M06_OB_B01452018',
'M01_OB_B01472561',
'M01_OB_B01472520',
'M06_OB_B01452104',
'M09_OB_B01472863',
'M06_OB_B01452261',
'M01_OB_B01472725',
'M06_OB_B01451948',
'M08_OB_B01485921',
'M07_OB_B01477695',
'M09_OB_B01472648',
'M09_OB_B01472144',
'M06_OB_B01452102',
'M07_OB_B01477873',
'M01_OB_B01472778',
'M07_OB_B01477930',
'M07_OB_B01477533',
'M07_OB_B01478103',
'M09_OB_B01472755',
'M07_OB_B01477603',
'M09_OB_B01473031',
'M09_OB_B01472464',
'M09_OB_B01472363',
'M06_OB_B01452061',
'M09_OB_B01472081',
'M06_OB_B01452060',
'M10_OB_B01495720',
'M06_OB_B01452040',
'M01_OB_B01471707',
'M09_OB_B01472418',
'M06_OB_B01452214'
)

As of 11/24/2014, there are 3 with issues:

42440	LM03	LM03	Load_PS1_M07_0102	M07_OB_B01477533	1073741825	0	M07	LoadStarted OnHold	Load
42433	LM04	LM04	Load_PS1_M09_0304	M09_OB_B01473031	1073741825	0	M09	LoadStarted OnHold	Load
39569	LM07	LM07	Load_PS1_M10_0102	M10_OB_B01495720	51	0	M10	LoadStarted LoadCompleted Merge1Started Merge1Completed	Load

Note M10_OB_B01495720 was not loaded properly (the table Load_PS1_M10_0102.M10_OB_B01495720.[Object] was empty). So I delete all entries of it from WMD (load, batch, dbobject) and load db Load_PS1_M10_0102.

These 3 batches should be reloaded.

Batches with Extra Stack Detections

None! Duplicates problem has been resolved (11-26-2014)

Missing Detections / Duplicate Detections

(see the file ​https://svn.pan-starrs.ifa.hawaii.edu/trac/ipp/attachment/wiki/Psps_MD_PV2/p2list.txt for more details)

The ones listed below are the ones that have not been loaded at all.

MD01/MD05 are missing detections on some batches

MD07 has duplicates on all batches - duplicates fixed 11-26-2014

PSPS_MD06_PV2   1452079 is missing (not loaded) on psps
PSPS_MD06_PV2   1452042 is missing (not loaded) on psps
PSPS_MD06_PV2   1452045 is missing (not loaded) on psps
PSPS_MD06_PV2   1452047 is missing (not loaded) on psps
PSPS_MD06_PV2   1452049 is missing (not loaded) on psps
PSPS_MD06_PV2   1452044 is missing (not loaded) on psps
PSPS_MD09_PV2   1491049 is missing (not loaded) on psps
PSPS_MD06_PV2   1452050 is missing (not loaded) on psps
PSPS_MD06_PV2   1452078 is missing (not loaded) on psps
PSPS_MD06_PV2   1452077 is missing (not loaded) on psps
PSPS_MD06_PV2   1452048 is missing (not loaded) on psps
PSPS_MD01_PV2   1482881 is missing (not loaded) on psps
PSPS_MD06_PV2   1452071 is missing (not loaded) on psps
PSPS_MD06_PV2   1452043 is missing (not loaded) on psps
PSPS_MD06_PV2   1452080 is missing (not loaded) on psps
PSPS_MD06_PV2   1452075 is missing (not loaded) on psps
PSPS_MD06_PV2   1452072 is missing (not loaded) on psps
PSPS_MD06_PV2   1452073 is missing (not loaded) on psps
PSPS_MD06_PV2   1452074 is missing (not loaded) on psps
PSPS_MD06_PV2   1452046 is missing (not loaded) on psps
PSPS_MD06_PV2   1452076 is missing (not loaded) on psps
-----------
expected number of batch: 38854 
number of batches seeon on psps: 38833 
number of batches which are bad (missing more than 1% of detections): 7867 
bad ones, count where there are more expected: 4066 
bad ones, count where there are less than expected: 3801 
missing ones, where 0 are loaded into psps: 21