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	psps is missing batches
MD03	complete	complete	psps is missing batches
MD04	complete	complete	looks complete
MD05	missing detections	complete	psps is missing batches
MD06	psps is missing batches	complete	psps is missing batches
MD07	duplicates present	duplicates present	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	1 batch missing
MD03	all good	all good	1 batch missing
MD04	all good	all good	all good
MD05	2236 out of 3836 batches are missing detections	all good	3 batches missing, 8 batches (partial slices) missing
MD06	21 batches are not loaded at all	all good	16 batches missing
MD07	4066 out of 4066 batches have extra detections	164 out of 321 batches have extra detections	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)

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

Batches with Extra Stack Detections

datastore       batchID    expected  actual
PSPS_MD07_PV2   1486292    10116     16520
PSPS_MD07_PV2   1486307    34752     37672
PSPS_MD07_PV2   1486310    16727     17832
PSPS_MD07_PV2   1486311    33936     67389
PSPS_MD07_PV2   1486313    15365     30190
PSPS_MD07_PV2   1486314    31541     63082
PSPS_MD07_PV2   1486316    4532     9064
PSPS_MD07_PV2   1486337    31288     41568
PSPS_MD07_PV2   1486347    32897     36070
PSPS_MD07_PV2   1486348    33057     66096
PSPS_MD07_PV2   1486350    34353     68706
PSPS_MD07_PV2   1486375    34647     44970
PSPS_MD07_PV2   1486378    33334     43296
PSPS_MD07_PV2   1486513    34402     38091
PSPS_MD07_PV2   1486514    34594     69188
PSPS_MD07_PV2   1486515    33430     66860
PSPS_MD07_PV2   1486516    32536     65072
PSPS_MD07_PV2   1486519    34130     68260
PSPS_MD07_PV2   1486553    32872     42744
PSPS_MD07_PV2   1486560    36720     40303
PSPS_MD07_PV2   1486562    36151     72284
PSPS_MD07_PV2   1486563    33734     67468
PSPS_MD07_PV2   1486576    4354     7834
PSPS_MD07_PV2   1486577    26328     36811
PSPS_MD07_PV2   1486579    36127     38256
PSPS_MD07_PV2   1486580    36375     39221
PSPS_MD07_PV2   1486581    33534     65638
PSPS_MD07_PV2   1486582    36231     72015
PSPS_MD07_PV2   1486584    24087     48174
PSPS_MD07_PV2   1486585    34790     69580
PSPS_MD07_PV2   1486626    9730     10087
PSPS_MD07_PV2   1486966    33480     37081
PSPS_MD07_PV2   1486991    6567     12589
PSPS_MD07_PV2   1487081    4288     7706
PSPS_MD07_PV2   1487094    10516     17216
PSPS_MD07_PV2   1487375    11997     19483
PSPS_MD07_PV2   1487381    15053     29579
PSPS_MD07_PV2   1487383    31762     63524
PSPS_MD07_PV2   1487384    5660     11320
PSPS_MD07_PV2   1487386    5707     11414
PSPS_MD07_PV2   1487402    31546     41920
PSPS_MD07_PV2   1487413    34916     69832
PSPS_MD07_PV2   1487424    35081     45557
PSPS_MD07_PV2   1487427    33605     43669
PSPS_MD07_PV2   1487537    35425     70850
PSPS_MD07_PV2   1487539    33960     67920
PSPS_MD07_PV2   1487542    34701     69402
PSPS_MD07_PV2   1487565    33274     43304
PSPS_MD07_PV2   1487574    37192     40855
PSPS_MD07_PV2   1487609    4209     7532
PSPS_MD07_PV2   1487618    36428     38578
PSPS_MD07_PV2   1487622    36773     39659
PSPS_MD07_PV2   1487625    33939     66440
PSPS_MD07_PV2   1487628    37036     73621
PSPS_MD07_PV2   1487631    24233     48466
PSPS_MD07_PV2   1487669    9562     9910
PSPS_MD07_PV2   1487679    34975     37911
PSPS_MD07_PV2   1487681    17014     18144
PSPS_MD07_PV2   1487682    34268     68053
PSPS_MD07_PV2   1487684    37957     41134
PSPS_MD07_PV2   1487685    18596     19834
PSPS_MD07_PV2   1487687    37093     73670
PSPS_MD07_PV2   1487689    15507     30475
PSPS_MD07_PV2   1487691    34495     68990
PSPS_MD07_PV2   1487720    34539     45717
PSPS_MD07_PV2   1487737    33411     36622
PSPS_MD07_PV2   1487740    33410     66802
PSPS_MD07_PV2   1487742    36237     39681
PSPS_MD07_PV2   1487744    36188     72353
PSPS_MD07_PV2   1487746    37799     75598
PSPS_MD07_PV2   1487786    38508     50047
PSPS_MD07_PV2   1487788    36770     47757
PSPS_MD07_PV2   1487892    34141     37806
PSPS_MD07_PV2   1487894    34960     38695
PSPS_MD07_PV2   1487895    33007     66014
PSPS_MD07_PV2   1487896    38351     42461
PSPS_MD07_PV2   1487897    39316     78632
PSPS_MD07_PV2   1487898    37464     74928
PSPS_MD07_PV2   1487899    36641     73282
PSPS_MD07_PV2   1487900    37905     75810
PSPS_MD07_PV2   1487930    36664     47738
PSPS_MD07_PV2   1487933    36893     73768
PSPS_MD07_PV2   1487934    34172     68344
PSPS_MD07_PV2   1487935    40977     44989
PSPS_MD07_PV2   1487936    40415     80811
PSPS_MD07_PV2   1487939    37516     75032
PSPS_MD07_PV2   1487957    26566     37150
PSPS_MD07_PV2   1487959    29510     41157
PSPS_MD07_PV2   1487961    35301     70602
PSPS_MD07_PV2   1487962    39887     42240
PSPS_MD07_PV2   1487963    39978     43128
PSPS_MD07_PV2   1487965    36977     72408
PSPS_MD07_PV2   1487967    40334     80161
PSPS_MD07_PV2   1487971    26782     53564
PSPS_MD07_PV2   1487974    38997     77994
PSPS_MD07_PV2   1487996    9777     10141
PSPS_MD07_PV2   1488082    36999     40990
PSPS_MD07_PV2   1488091    4552     8097
PSPS_MD07_PV2   1488092    4603     8229
PSPS_MD07_PV2   1488093    25803     36069
PSPS_MD07_PV2   1488095    23495     46990
PSPS_MD07_PV2   1488098    10642     11029
PSPS_MD07_PV2   1488099    7642     14639
PSPS_MD07_PV2   1488100    6942     13304
PSPS_MD07_PV2   1488101    6395     12253
PSPS_MD07_PV2   1488108    30812     61624
PSPS_MD07_PV2   1488110    3468     6936
PSPS_MD07_PV2   1488114    30581     40608
PSPS_MD07_PV2   1488119    33942     44063
PSPS_MD07_PV2   1488120    32573     42315
PSPS_MD07_PV2   1488197    33007     36570
PSPS_MD07_PV2   1488206    35393     37476
PSPS_MD07_PV2   1488216    6583     12620
PSPS_MD07_PV2   1488222    9914     16172
PSPS_MD07_PV2   1488225    33854     36686
PSPS_MD07_PV2   1488227    16375     17457
PSPS_MD07_PV2   1488228    33060     65650
PSPS_MD07_PV2   1488230    14399     28283
PSPS_MD07_PV2   1488243    32229     35323
PSPS_MD07_PV2   1488244    32298     64578
PSPS_MD07_PV2   1488245    33649     67298
PSPS_MD07_PV2   1488351    33879     37503
PSPS_MD07_PV2   1488353    32931     65862
PSPS_MD07_PV2   1488355    31964     63928
PSPS_MD07_PV2   1488356    33449     66898
PSPS_MD07_PV2   1488380    32227     41917
PSPS_MD07_PV2   1488381    36122     39648
PSPS_MD07_PV2   1488383    35563     71108
PSPS_MD07_PV2   1488384    32973     65946
PSPS_MD07_PV2   1488399    35555     38337
PSPS_MD07_PV2   1488401    32791     64188
PSPS_MD07_PV2   1488403    35524     70611
PSPS_MD07_PV2   1488405    34179     68358
PSPS_MD07_PV2   1488425    10255     16695
PSPS_MD07_PV2   1488429    35186     38127
PSPS_MD07_PV2   1488431    16784     17888
PSPS_MD07_PV2   1488432    34285     68075
PSPS_MD07_PV2   1488434    16075     31584
PSPS_MD07_PV2   1488435    31922     63844
PSPS_MD07_PV2   1488439    31708     42082
PSPS_MD07_PV2   1488441    33589     36851
PSPS_MD07_PV2   1488443    33651     67283
PSPS_MD07_PV2   1488445    34719     69438
PSPS_MD07_PV2   1488457    34978     45395
PSPS_MD07_PV2   1488459    33653     43715
PSPS_MD07_PV2   1488524    34908     38668
PSPS_MD07_PV2   1488525    35200     38963
PSPS_MD07_PV2   1488526    35652     71304
PSPS_MD07_PV2   1488528    34377     68754
PSPS_MD07_PV2   1488529    33152     66304
PSPS_MD07_PV2   1488530    34608     69216
PSPS_MD07_PV2   1488531    34152     68304
PSPS_MD07_PV2   1488547    33426     43475
PSPS_MD07_PV2   1488552    37687     41385
PSPS_MD07_PV2   1488553    36982     73943
PSPS_MD07_PV2   1488554    34164     68328
PSPS_MD07_PV2   1488573    26823     37476
PSPS_MD07_PV2   1488578    36647     38795
PSPS_MD07_PV2   1488580    36761     39659
PSPS_MD07_PV2   1488582    34029     66612
PSPS_MD07_PV2   1488583    36909     73367
PSPS_MD07_PV2   1488585    24703     49406
PSPS_MD07_PV2   1488586    35421     70842
PSPS_MD07_PV2   1488605    10178     10552
-----------
expected: 2999    all: 2999     bad: 164 

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

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