Fix for duplicated rows in cross-match between SSSource and diaSource for tutorial notebook 201_8

maxmahlke · July 24, 2025, 3:26pm

Hi,

I noticed two issues in the 201_8_SSSource_table notebook.

The text states “Check if any visits had more than one source detected” but
the following cell actually prints the number of sources (i.e. detections)
for a given ssObjectID.

lsst/tutorial-notebooks/blob/74a3ae7523b54b8cd0bf9b4cf1fb28d902420f80/DP1/200_Data_Products/201_Catalogs/201_8_SSSource_table.ipynb#L452

      
        
             "outputs": [],
             "source": [
              "# results"
             ]
            },
            {
             "cell_type": "markdown",
             "id": "955b9722-43b9-4c3d-b35f-14d72826f776",
             "metadata": {},
             "source": [
              "Check if any visits had more than one source detected (with the option to print the results)."
             ]
            },
            {
             "cell_type": "code",
             "execution_count": null,
             "id": "f44c87ad-8aca-4681-a411-0d7ebfa2ce07",
             "metadata": {},
             "outputs": [],
             "source": [
              "values, counts = np.unique(results['ssObjectId'], return_counts=True)\n",

The cross-match between the DiaSource and the SSSource tables is joined on
the ssObjectId colum, which leads to duplicated rows as a given ssObjectId
appears multiple times (the number of detections) in DiaSource. For example,
ssObjectId 20890932542191434 has three sources but appears nine times in the
cross-match. The resulting table is thus 142640 rows (sum of squared number
of sources per SSO) when it should be 5988 (sum of SSO sources). Joining on
diaSourceId instead of ssObjectId fixes this issue.

github.com

lsst/tutorial-notebooks/blob/74a3ae7523b54b8cd0bf9b4cf1fb28d902420f80/DP1/200_Data_Products/201_Catalogs/201_8_SSSource_table.ipynb#L543

      
        
             "cell_type": "code",
             "execution_count": null,
             "id": "d0874767-dcd8-40db-9663-905a64bcb853",
             "metadata": {},
             "outputs": [],
             "source": [
              "query = \"SELECT sss.ssObjectId, sss.heliocentricDist, \"\\\n",
              "        \"sss.topocentricDist, sss.phaseAngle, \"\\\n",
              "        \"dia.midpointMjdTai, dia.band \"\\\n",
              "        \"FROM dp1.SSSource AS sss \" \\\n",
              "        \"JOIN dp1.DiaSource AS dia ON sss.ssObjectId = dia.ssObjectId \" \\\n",
              "        \"WHERE dia.ssObjectId > 0\"\n",
              "job = service.submit_job(query)\n",
              "job.run()\n",
              "job.wait(phases=['COMPLETED', 'ERROR'])\n",
              "print('Job phase is', job.phase)\n",
              "if job.phase == 'ERROR':\n",
              "    job.raise_if_error()"
             ]
            },
            {

I’d be happy to contribute a PR if you’d like.

Max

sgreenstreet · July 24, 2025, 10:08pm

Hi @maxmahlke, thank you for your post.

For #2 above, you are correct that the join statement used in the screenshot shown will return query results with all ssObjectId sources getting duplicated for each entry of that ssObjectId in the DiaSource table, which is not the desired result. We are implenting the necessary fix to change the JOIN statement here to read
“JOIN dp1.DiaSource AS dia ON sss.diaSourceId = dia.diaSourceId” in tutorial notebook 201_8_SSSource_table.ipynb as you correctly suggested will fix the issue. Thank you for bringing this to our attention.

For #1 above, there was a small typo in the text description for this cell. The text should say “objects” instead of “visits”. We have implemented a change so that the text now reads:
“Check if any SS objects had more than one SS source detected…”
This cell is, indeed, returning the SS objects that had more than one source detection. Running this cell returns a count of 417 Solar System objects with >1 detection out of the 431 Solar System objects included in the DP1 tables. This means that 14 Solar System objects had only 1 source detection.

Again, thank you for bringing these issues to our attention. We are in the process of implementing the changes and will report back when the new version of the notebook is available.

sgreenstreet · July 24, 2025, 11:22pm

The updated notebook is now available at: GitHub - lsst/tutorial-notebooks: Tutorial Jupyter Notebooks maintained by the Rubin Observatory Community Science Team. as well as on the RSP.