HMpTy.mysql.conesearch module ∞
Perform a conesearch on a database table with existing HTMId columns pre-populated
- Author
David Young
-
class
conesearch
(log, dbConn, tableName, columns, ra, dec, radiusArcsec, sqlWhere=False, raCol='raDeg', decCol='decDeg', separations=False, distinct=False, closest=False)[source] ∞ Bases:
object
The worker class for the conesearch module
Key Arguments
log
– loggerdbConn
– a database connectiontableName
– the name of the database table to perform the conesearch on.columns
– the columns requested from the database tablera
– the right ascension of the conesearch centre, can be single value or list of valuesdec
– the declination of the conesearch centre, can be single value or list of valuesradiusArcsec
– radius of the conesearch to be performed in arcsecssqlWhere
– clause to add after “where” in the initial sql query of the conesearch. Default FalseraCol
– the database table ra column name. Default * raDeg*decCol
– the database table dec column name. Default decDegseparations
– include the separations in the final output. Default Falsedistinct
– request distinct columns from the database table (i.e. select DISTINCT …). Default Falseclosest
– return the closest match only. Default False
Usage
Say we have 5 locations we wish to search a database table called transientBucket to see if it contains sources at those locations. Add the coordinates to those locations to RA and DEC lists like so:
raList = ["23:25:53.56", "02:10:08.16", "13:20:00.00", 1.47329, 35.34279] decList = ["+26:54:23.9", "-48:38:24.3", "+24:18:00.00", 8.43016, -42.34428]
Note coorinates can be in decimal degrees or sexegesimal format (or both).
To initialise a 10 arcsec conesearch to return the transientBucketId and spectralType values from any resulting match use the code:
from HMpTy.mysql import conesearch cs = conesearch( log=log, dbConn=dbConn, tableName="transientBucket", columns="transientBucketId, spectralType", ra=raList, dec=decList, radiusArcsec=10, separations=False, distinct=False, sqlWhere=False )
Using the
query
property of the coneseach object you can inspect the initial sql query to be run on the database:print(cs.query)
select transientBucketId, spectralType, raDeg, decDeg from transientBucket where htm16ID in (51985593986,51985593989,51985593993,51985593994,51985593995,51985593996,51985593997,51985593998,51985593999,51985594037, ... ,38488627914,38488627916,38488627918,38488627919,38488627956,38488627957,38488627959)
To execute the query and return the results:
matchIndies, matches = cs.search()
The
matchIndies
are the indices of the coordinate in the originalraList
anddecList
lists and thematches
the matched rows from the database table.To constrain your results a little more define the
distinct
and orsqlWhere
attributes of the conesearch:from HMpTy.mysql import conesearch cs = conesearch( log=log, dbConn=dbConn, tableName="transientBucket", columns="transientBucketId, spectralType", ra=raList1, dec=decList1, radiusArcsec=10, separations=True, distinct=True, sqlWhere="spectralType is not null" ) matchIndies, matches = cs.search() for row in matches.list: print(row)
```text {'raDeg': 351.473208333, 'cmSepArcsec': 0.13379807128325164, 'decDeg': 26.9066388889, 'spectralType': u'SN Ia', 'transientBucketId': 1375799L} {'raDeg': 32.534, 'cmSepArcsec': 0.031941633602975743, 'decDeg': -48.6400888889, 'spectralType': u'II', 'transientBucketId': 1328883L} {'raDeg': 1.47329166667, 'cmSepArcsec': 0.0068727452774991196, 'decDeg': 8.43016111111, 'spectralType': u'SN Ia', 'transientBucketId': 1321322L} {'raDeg': 35.3427916667, 'cmSepArcsec': 0.0043467057710126393, 'decDeg': -42.3442805556, 'spectralType': u'Ia', 'transientBucketId': 1307226L} ``` Note that by adding ``separations=True`` the matched source seperations from the original coordinate lists have been injected into the results. It is possible to render the results in csv, json, markdown, yaml or ascii table format. For example: ```python print(matches.table()) ``` ```text +-----------+---------------+-----------+--------------+--------------------+ | raDeg | spectralType | decDeg | cmSepArcsec | transientBucketId | +-----------+---------------+-----------+--------------+--------------------+ | 351.4732 | SN Ia | 26.9066 | 0.1338 | 1375799 | | 32.5340 | II | -48.6401 | 0.0319 | 1328883 | | 1.4733 | SN Ia | 8.4302 | 0.0069 | 1321322 | | 35.3428 | Ia | -42.3443 | 0.0043 | 1307226 | +-----------+---------------+-----------+--------------+--------------------+ ``` To save the results to file: ```python matches.table(filepath="/path/to/my/results.dat") ``` To instead render as csv, json, markdown or yaml use: ```python matches.csv(filepath="/path/to/my/results.csv") matches.json(filepath="/path/to/my/results.json") matches.markdown(filepath="/path/to/my/results.md") matches.markdown(filepath="/path/to/my/results.yaml") ``` Finally, to render the results as mysql inserts, use the following code: ```python matches.mysql(tableName="mysql_table", filepath=None, createStatement=False) ``` ```text INSERT INTO `mysql_table` (cmSepArcsec,decDeg,raDeg,spectralType,transientBucketId) VALUES ("0.133798071283" ,"26.9066388889" ,"351.473208333" ,"SN Ia" ,"1375799") ON DUPLICATE KEY UPDATE cmSepArcsec="0.133798071283", decDeg="26.9066388889", raDeg="351.473208333", spectralType="SN Ia", transientBucketId="1375799", updated=IF( cmSepArcsec="0.133798071283" AND decDeg="26.9066388889" AND raDeg="351.473208333" AND spectralType="SN Ia" AND transientBucketId="1375799", 0, 1), dateLastModified=IF( cmSepArcsec="0.133798071283" AND decDeg="26.9066388889" AND raDeg="351.473208333" AND spectralType="SN Ia" AND transientBucketId="1375799", dateLastModified, NOW()) ; INSERT INTO `mysql_table` (cmSepArcsec,decDeg,raDeg,spectralType,transientBucketId) VALUES ("0.031941633603" ,"-48.6400888889" ,"32.534" ,"II" ,"1328883") ON DUPLICATE KEY UPDATE cmSepArcsec="0.031941633603", decDeg="-48.6400888889", raDeg="32.534", spectralType="II", transientBucketId="1328883", updated=IF( cmSepArcsec="0.031941633603" AND decDeg="-48.6400888889" AND raDeg="32.534" AND spectralType="II" AND transientBucketId="1328883", 0, 1), dateLastModified=IF( cmSepArcsec="0.031941633603" AND decDeg="-48.6400888889" AND raDeg="32.534" AND spectralType="II" AND transientBucketId="1328883", dateLastModified, NOW()) ; INSERT INTO `mysql_table` (cmSepArcsec,decDeg,raDeg,spectralType,transientBucketId) VALUES ("0.0068727452775" ,"8.43016111111" ,"1.47329166667" ,"SN Ia" ,"1321322") ON DUPLICATE KEY UPDATE cmSepArcsec="0.0068727452775", decDeg="8.43016111111", raDeg="1.47329166667", spectralType="SN Ia", transientBucketId="1321322", updated=IF( cmSepArcsec="0.0068727452775" AND decDeg="8.43016111111" AND raDeg="1.47329166667" AND spectralType="SN Ia" AND transientBucketId="1321322", 0, 1), dateLastModified=IF( cmSepArcsec="0.0068727452775" AND decDeg="8.43016111111" AND raDeg="1.47329166667" AND spectralType="SN Ia" AND transientBucketId="1321322", dateLastModified, NOW()) ; INSERT INTO `mysql_table` (cmSepArcsec,decDeg,raDeg,spectralType,transientBucketId) VALUES ("0.00434670577101" ,"-42.3442805556" ,"35.3427916667" ,"Ia" ,"1307226") ON DUPLICATE KEY UPDATE cmSepArcsec="0.00434670577101", decDeg="-42.3442805556", raDeg="35.3427916667", spectralType="Ia", transientBucketId="1307226", updated=IF( cmSepArcsec="0.00434670577101" AND decDeg="-42.3442805556" AND raDeg="35.3427916667" AND spectralType="Ia" AND transientBucketId="1307226", 0, 1), dateLastModified=IF( cmSepArcsec="0.00434670577101" AND decDeg="-42.3442805556" AND raDeg="35.3427916667" AND spectralType="Ia" AND transientBucketId="1307226", dateLastModified, NOW()) ; ```
-
search
()[source] ∞ Return the results of the database conesearch
Return
conesearch
Usage
See class usage.
-
_get_trixel_ids_that_overlap_conesearch_circles
()[source] ∞ Get an array of all of the trixels IDs that overlap the conesearch circles(s)
Return
trixelArray
– an array of all the overlapping trixel ids
-
_execute_query
(sqlQuery)[source] ∞ execute query and trim results*
Key Arguments
sqlQuery
– the sql database query to grab low-resolution results.
Return
databaseRows
– the database rows found on HTM trixles with requested IDs
-
_list_crossmatch
(dbRows)[source] ∞ to a finer grain crossmatch of the input coordinates and the database results.
Key Arguments
dbRows
– the rows return from the database on first crossmatch pass.
Return
matchIndices1
– indices of the coordinate in the original ra and dec listsmatches
– the matched database rows