STILTS-SQLSKYMATCH(1)

📦
version 3.5.4 · 5 versions
, in ubuntu stonking

💖 NAME 💖

okay so welcome to the stilts-sqlskymatch(1) era bestie ✨ this is the Stilts command that does a crossmatch of a table on sky position against an SQL table. no cap.

🌸 SYNOPSIS 🌸

stilts sqlskymatch [ifmt=<in-format>] [istream=true|false] [in=<table>]
                   [icmd=<cmds>] [ocmd=<cmds>]
                   [omode=out|meta|stats|count|checksum|cgi|discard|topcat|samp|tosql|gui]
                   [out=<out-table>] [ofmt=<out-format>] [ra=<expr>]
                   [dec=<expr>] [sr=<expr/deg>] [find=best|all|each]
                   [usefoot=true|false] [footnside=<int-value>]
                   [copycols=<colid-list>] [scorecol=<col-name>]
                   [erract=abort|ignore|retry|retry<n>]
                   [ostream=true|false] [fixcols=none|dups|all]
                   [suffix0=<label>] [suffix1=<label>] [db=<jdbc-url>]
                   [user=<value>] [password=<value>] [dbtable=<table-
                   name>] [dbra=<sql-col>] [dbdec=<sql-col>]
                   [dbunit=deg|rad]
                   [tiling=hpx<K>|healpixnest<K>|healpixring<K>|htm<K>]
                   [dbtile=<sql-col>] [selectcols=<sql-cols>]
                   [where=<sql-condition>] [preparesql=true|false]

✨ DESCRIPTION ✨

so here's the vibe. sqlskymatch is giving very much like coneskymatch, but — and this is the plot twist 🎀 — instead of yeeting an HTTP query off to some remote cone search service for every single match (aka every row of the input table), it just runs an SQL query directly. iconic behaviour, honestly.

That query is a SELECT statement with a WHERE clause that puts restrictions on Right Ascension and Declination columns, and you must tell it the names of those columns as parameters. the end result? it's the effect of a spatial join between a client-side table and a table living in the database. 💅

real talk tho: this command only works if you've got access to an SQL database via JDBC. the whole "how do I configure a JDBC connection" situation is discussed in SUN/256 — and obviously you're gonna need an actual database to connect to, plus appropriate read permissions on it, plus the relevant drivers. no driver, no slay, iykyk 😭

okay and a lil lore drop for the history girlies 🦋: this task used to be called sqlcone back in its experimental form in STILTS v1.3.

💅 OPTIONS 💅

  • ifmt=<in-format> — tells everyone what format the input table (the one from parameter in) is in. the known formats are all listed in SUN/256. use this flag when you already know your table's format and you're not playing guessing games. if it's set to the special value (auto) — which, fun fact, is the default — then stilts will try to detect the format all by itself 🧚. lowkey this doesn't always work out, and when it flops the program exits with an error explaining which formats were attempted. also: this parameter is ignored for scheme-specified tables.
  • istream=true|false — set it true and the input table from the in parameter gets read as a stream 🌊. heads up: you have to give the ifmt parameter in that case. depending on what operations you're asking for and which processing mode you're in, this might make the read fail — sometimes a table just has to be read more than once, it's giving main character ✋. normally you don't need to set this flag at all; most of the time the data streams automatically when that's the best move. BUT it can sometimes use less resources when you're processing big files in certain formats (VOTable, i'm looking at you 👀). and again: ignored for scheme-specified tables.
  • in=<table> — where your input table lives. it can be any of these forms, bestie:
    • a filename.
    • a URL.
    • the special value -, meaning standard input ⌨️. in this case the input format must be given explicitly with the ifmt parameter. note that not all formats can be streamed this way, so don't come for me.
    • a scheme specification of the form :<scheme-name>:<scheme-args>.
    • a system command line with either a "<" character at the start, or a "|" character at the end ("<syscmd" or "syscmd|"). this runs the given pipeline and reads from its standard output. it'll probably only work on unix-like systems.

in any case, compressed data in one of the supported compression formats (gzip, Unix compress or bzip2) gets decompressed transparently. free of charge. we love that for you 💖

  • icmd=<cmds> — extra processing to do on the input table from in, and it happens before any other processing takes place. the value is one or more of the filter commands described in SUN/256. got more than one? separate them with semicolon characters (";"). you can repeat this parameter multiple times on the same command line to stack up a whole list of processing steps 🌈 — the sequence you give defines the processing pipeline that gets performed on the table.

you can also supply commands from an external file using the indirection character '@'. so a value of "@filename" makes the file filename get read for a list of filter commands to execute. commands in the file may be separated by newline characters and/or semicolons, and lines that are blank or that start with a '#' character are ignored 🙈. a backslash character '\fR' at the end of a line joins it with the following line.

  • ocmd=<cmds> — same energy, but for the output table, and it happens after all other processing has taken place. value is one or more of the filter commands described in SUN/256. more than one? separate them with semicolons (";"). repeat this parameter multiple times on the same command line to build up your list of steps — that sequence is the processing pipeline performed on the table. ✨

and yes, commands can alternatively come from an external file via the indirection character '@': a value of "@filename" makes the file filename get read for a list of filter commands to execute. commands in the file may be separated by newline characters and/or semicolons, and blank lines or lines starting with '#' are ignored. a backslash character '\fR' at the end of a line joins it with the following line.

  • omode=out|meta|stats|count|checksum|cgi|discard|topcat|samp|tosql|gui — the mode your result table gets output in 👑. default is out, meaning the result gets written as a new table to disk or wherever, as determined by the out and ofmt parameters. but there are other possibilities, corresponding to all the other things you might wanna do with a table besides outputting it — displaying metadata, calculating statistics, populating a table in an SQL database, etc etc. for some values of this parameter you'll need extra parameters (<mode-args>) to pin down the exact behaviour.

the possible values are:

  • out
  • meta
  • stats
  • count
  • checksum
  • cgi
  • discard
  • topcat
  • samp
  • tosql
  • gui

use the help=omode flag or go read SUN/256 for the deets, bestie 🫶

  • out=<out-table> — where the output table goes. usually a filename to write to. if it equals the special value "-" (which is the default) the output table gets written to standard output 🌸.
this parameter must only be given if omode has its default value of "out". don't be chaotic.
  • ofmt=<out-format> — the format the output table gets written in (one of the ones in SUN/256 — matching is case-insensitive and you can get away with just the first few letters, we're chill like that). if it has the special value (auto) (the default), the output filename gets examined to try and guess what sort of file is needed, usually by peeking at the extension 👀. if it's not obvious from the filename what output format you meant, you get an error. sorry babes, rules are rules.
same deal: this parameter must only be given if omode has its default value of "out".
  • ra=<expr> — Right Ascension in degrees, in the coordinate system for the position of each row of the input table 🌈. this can straight up just be a column name, or it can be an algebraic expression calculated from columns, as explained in SUN/256. leave it blank and an attempt gets made to guess what expression to use from UCDs, column names and unit annotations 🧚. she guesses!
  • dec=<expr> — Declination in degrees, in the coordinate system for the position of each row of the input table 💫. same deal: a column name, or an algebraic expression calculated from columns as explained in SUN/256. leave it blank and an attempt gets made to guess from UCDs, column names and unit annotations. twin behaviour fr fr.
  • sr=<expr/deg> — an expression that evaluates to the search radius in degrees for the request at each row of the input table 🔍. this will often be a constant numerical value, but it may also be the name or ID of a column in the input table, or a function involving one. flexible queen.
  • find=best|all|each — decides which matches get kept. the drama of it all:
    • best: only the matching query table row closest to the input table row gets output. input table rows with no matches get omitted entirely. (note: this corresponds to the best1 option in the pair matching commands, and best1 is a permitted alias ✅)
    • all: all query table rows that match the input table row get output. input table rows with no matches get omitted.
    • each: there's one output table row for each input table row. if matches are found, the closest one from the query table gets output; if there are no matches, the query table columns come out blank 🫥.
  • usefoot=true|false — determines whether an attempt gets made to restrict searches in accordance with available footprint information 👣. if this is set true, then before any of the per-row queries are performed, an attempt may be made to acquire footprint information about the service. if that info can be obtained, then queries falling outside the footprint — and therefore known to yield zero results — get skipped entirely. this can speed up the search considerably 🔥. obsessed.

Okay bestie, we're still deep in the options, so let's keep scrolling 💅

Currently, the only footprints available are those provided by the CDS MOC (Multi-Order Coverage map) service, which covers VizieR and a few other cone search services.

  • footnside=<int-value> — this sets the HEALPix Nside parameter for the MOC footprint service. It's a tuning knob that decides the resolution of the footprint (if one's available). Bigger values = better resolution = better odds of skipping queries you didn't need, but processing them takes longer and fetching/storing them costs more. 😮‍💨

The value has to be a power of 2, and as of right now the MOC service won't hand out footprints at resolutions greater than nside=512, so it should be <=512. Also, it's only used if usefoot=true.

  • copycols=<colid-list> — a list of columns from the input table that get copied over to the output table. Each column you name here gets prepended to the columns of the combined output table, and its value for each row comes from the input table row that supplied the parameters for the query that produced it. Peep SUN/256 for the list syntax. Default is "*", which means every column from the input table lands in the output. ✨
  • scorecol=<col-name> — names a column in the output table that holds the distance between the requested central position and the actual position of the returned row. That distance is an angular distance in degrees. If you pick a null value, no distance column shows up at all. Lowkey handy, ngl.
  • erract=abort|ignore|retry|retry<n> — this decides what happens when any individual cone search request fails. By default the task aborts. That might genuinely be the best move, but for unreliable or badly implemented services you might find some searches fail while others succeed — so continuing through a few failures can be the move. Your options:
    • abort: failure of any query terminates the task.
    • ignore: a failed query is treated exactly the same as a query that returns no rows.
    • retry: failed queries get retried until they succeed; an increasing delay gets introduced for each failure. ⚠️ Use with care — if the failure is for a good, or at least reproducible, reason, this could stop the task from ever completing.
    • retry<n>: failed queries are retried at most a fixed number <n> of times; an increasing delay gets introduced for each failure. If the failures keep coming, the task terminates.
bestie, that retry one is giving infinite loop energy. be careful. 💀
  • ostream=true|false — if true, the operation streams on output, so the output table gets built up as results come in from the cone search service. The catch? Some output modes and formats need multiple passes through the data to work, so depending on the output destination, the operation may just fail if you set this. Use with care (or at least be prepared for it to fail). 🙈
  • fixcols=none|dups|all — controls how input columns get renamed before they're used in the output table. The choices:
    • none: columns are not renamed
    • dups: columns that would otherwise have duplicate names in the output get renamed to show which table they came from
    • all: all columns get renamed to show which table they came from

If columns do get renamed, the new names are determined by the suffix* parameters.

  • suffix0=<label> — if fixcols is set so input columns get renamed for insertion into the output table, this parameter decides how that renaming goes down. It gives a suffix that's appended to all renamed columns from the input table.
  • suffix1=<label> — same deal, but this one gives a suffix appended to all renamed columns from the cone result table. 🎀
  • db=<jdbc-url> — a URL that defines a connection to a database. It has the form jdbc:<subprotocol>:<subname> — the details are database- and driver-dependent. Go consult Sun's JDBC documentation plus the docs for whichever JDBC driver you're using. Heads up: the relevant driver class needs to be on your classpath and referenced in the jdbc.drivers system property for the connection to actually happen. no cap.
  • user=<value> — user name for logging into the SQL database. Defaults to the current username.
  • password=<value> — password for logging into the SQL database. 🤫
  • dbtable=<table-name> — the name of the table in the SQL database that provides the remote data.
  • dbra=<sql-col> — the name of a column in the SQL database table dbtable that gives the right ascension. Units come from dbunit.
  • dbdec=<sql-col> — the name of a column in the SQL database table dbtable that gives the declination. Units come from dbunit.
  • dbunit=deg|rad — units of the right ascension and declination columns identified in the database table. Can be either deg[rees] (that's the default) or rad[ians].
  • tiling=hpx<K>|healpixnest<K>|healpixring<K>|htm<K> — describes the sky tiling scheme in use. You can use one of these values:
    • hpxK: alias for healpixnestK
    • healpixnestK: HEALPix using the Nest scheme at order K
    • healpixringK: HEALPix using the Ring scheme at order K
    • htmK: Hierarchical Triangular Mesh at level K

So for instance hpx5 or healpixnest5 would both indicate the HEALPix NEST tiling scheme at order 5. ✨

At level K, there are 12*4^K HEALPix pixels, or 8*4^K HTM pixels on the sky. More info about these tiling schemes lives at the HEALPix and HTM web sites.

  • dbtile=<sql-col> — the name of a column in the SQL database table dbtable that contains a sky tiling pixel index. The tiling scheme comes from the tiling parameter. Using a tiling column is optional, but if it's there (and if the column is indexed in the database table) it may speed up searches. Set it to null if the database table has no tiling column, or if you just don't wanna use one.
  • selectcols=<sql-cols> — an SQL expression for the list of columns to select from the table in the database. A value of "*" retrieves all columns. 👑
  • where=<sql-condition> — an SQL expression that further limits the rows selected from the database. This gets combined with the position constraints implied by the cone search centres and radii. The value here should just be a condition — do not include the WHERE keyword. A null value means no extra criteria.
  • preparesql=true|false — if true, the JDBC connection uses PreparedStatements for the SQL SELECTs; otherwise it uses simple Statements. This is a tuning parameter and affects only performance. On some database/driver combos it's a lot faster to set it false (which is the default); on others it might be faster. who knows? 🤷‍♀️

🌈 SEE ALSO 🌈

  • stilts(1)
  • If the package stilts-doc is installed, the full documentation SUN/256 is available in HTML format at: file:///usr/share/doc/stilts/sun256/index.html

💖 VERSION 💖

STILTS version 3.5.4-debian

This is the Debian version of Stilts, which lacks the support of some file formats and network protocols. For differences, see file:///usr/share/doc/stilts/README.Debian.

👑 AUTHOR 👑

Mark Taylor (Bristol University)

okay bestie that's the whole page, you absolutely ate that 🫶 This edition of the manual page is from Mar 2017 and it's the STILTS-SQLSKYMATCH(1) page — go forth and slay your cone searches, mwah 💅💖✨

📦 where this page comes from