Right now, the examples focus on single field tiles, like "What is the probability contained within each field?"

This is great, however, for example for ZTF, which has many quadrants within one field and therefore many FieldTiles for a single field, we are more interested in the sum of probabilities within a given set of FieldTiles. I expect a combination of the "What is the combined area of fields 1000 through 2000?" example (instead getting the FieldTile id's corresponding to a given field) and then summing the probabilities calculated in the "What is the probability contained within each field?" example, would do it, but would require a loop over fields. Is there a faster way?

Sometimes, ra and dec are set to None in Skyportal. Since x, y, and z are computed automatically in the latest release, these functions will fail, i.e.

**dfl(lambda ra, dec: cos(rad(ra)) * cos(rad(dec))))
E TypeError: must be real number, not NoneType

Could we modify the functions to be like:

cos(rad(ra)) * cos(rad(dec) if ra is not None else None

Or similar? Or what do you think @lpsinger ?

Running the steps according to the instructions in README, pytest interrupts itself and errors out, saying:

E DeprecationWarning: distutils Version classes are deprecated. Use packaging.version instead.

This error is the same regardless of whether I install the venv using Poetry with python 3.7, 3.8 or 3.9 and prevents running the test suite.

PostreSQL 14 multiranges are a promising solution for #18. Experimentally verify the computational complexity of the following functions and operations introduced in PostgreSQL 13:

1. range_agg over N rows of an INT8RANGE column with a GIST index: O(N)
2. take result of range_agg from query 1, find intersection with M rows of an INT8 column with a GIST index: O((N + M) log (N + M))
3. take result of range_agg from query 1, find intersection with M rows of an INT8RANGE column with a GIST index: O((N + M) log (N + M))

I am wondering if some convenience code or doc example of how to go from the healpix range sets to a MOC might be useful.

Currently the RA and Dec columns in Point are allowed to be null. It's good to have this option, but it forces subclasses that inherit from Point to have nullable RA and Dec columns. In skyportal, our API validators and schema documentation are autogenerated off the SQLa model, so we need to be able to sometimes have RA and Dec be nonnull (and sometimes null). Is there a way we can make this configurable at the healpix alchemy level ?

With sqlalchemy 1.3.24, I get for the second line of the percentile query:

        subquery1 = (
            sa.select(LocalizationTile.probdensity, cum_prob).filter(
                LocalizationTile.localization_id == localization.id
            )
        ).subquery()

The below. Should this already be compatible?

Traceback (most recent call last):
  File "/Users/mcoughlin/opt/anaconda3/envs/skyportal/lib/python3.8/site-packages/tornado/web.py", line 1702, in _execute
    result = method(*self.path_args, **self.path_kwargs)
  File "/Users/mcoughlin/Code/ZTF/skyportal-branches/crossmatch2/skyportal/baselayer/app/access.py", line 37, in wrapper
    return tornado.web.authenticated(method)(self, *args, **kwargs)
  File "/Users/mcoughlin/opt/anaconda3/envs/skyportal/lib/python3.8/site-packages/tornado/web.py", line 3173, in wrapper
    return method(self, *args, **kwargs)
  File "/Users/mcoughlin/Code/ZTF/skyportal-branches/crossmatch2/skyportal/skyportal/handlers/api/source.py", line 1097, in get
    sa.select(LocalizationTile.probdensity, cum_prob).filter(
  File "<string>", line 2, in select
  File "<string>", line 2, in __init__
  File "/Users/mcoughlin/opt/anaconda3/envs/skyportal/lib/python3.8/site-packages/sqlalchemy/util/deprecations.py", line 139, in warned
    return fn(*args, **kwargs)
  File "/Users/mcoughlin/opt/anaconda3/envs/skyportal/lib/python3.8/site-packages/sqlalchemy/sql/selectable.py", line 3117, in __init__
    for c in columns:
  File "/Users/mcoughlin/opt/anaconda3/envs/skyportal/lib/python3.8/site-packages/sqlalchemy/sql/operators.py", line 432, in __getitem__
    return self.operate(getitem, index)
  File "/Users/mcoughlin/opt/anaconda3/envs/skyportal/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 226, in operate
    return op(self.comparator, *other, **kwargs)
  File "/Users/mcoughlin/opt/anaconda3/envs/skyportal/lib/python3.8/site-packages/sqlalchemy/sql/operators.py", line 432, in __getitem__
    return self.operate(getitem, index)
  File "/Users/mcoughlin/opt/anaconda3/envs/skyportal/lib/python3.8/site-packages/sqlalchemy/orm/properties.py", line 367, in operate
    return op(self.__clause_element__(), *other, **kwargs)
  File "/Users/mcoughlin/opt/anaconda3/envs/skyportal/lib/python3.8/site-packages/sqlalchemy/sql/operators.py", line 432, in __getitem__
    return self.operate(getitem, index)
  File "/Users/mcoughlin/opt/anaconda3/envs/skyportal/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 762, in operate
    return op(self.comparator, *other, **kwargs)
  File "/Users/mcoughlin/opt/anaconda3/envs/skyportal/lib/python3.8/site-packages/sqlalchemy/sql/operators.py", line 432, in __getitem__
    return self.operate(getitem, index)
  File "<string>", line 1, in <lambda>
  File "/Users/mcoughlin/opt/anaconda3/envs/skyportal/lib/python3.8/site-packages/sqlalchemy/sql/type_api.py", line 68, in operate
    return o[0](self.expr, op, *(other + o[1:]), **kwargs)
  File "/Users/mcoughlin/opt/anaconda3/envs/skyportal/lib/python3.8/site-packages/sqlalchemy/sql/default_comparator.py", line 237, in _getitem_impl
    _unsupported_impl(expr, op, other, **kw)
  File "/Users/mcoughlin/opt/anaconda3/envs/skyportal/lib/python3.8/site-packages/sqlalchemy/sql/default_comparator.py", line 241, in _unsupported_impl
    raise NotImplementedError(
NotImplementedError: Operator 'getitem' is not supported on this expression

We need to be able to rapidly calculate the area or probability contained within the union of a set of ZTF fields. Each ZTF field is represented as a Region, which in turn is represented as rows in the Tiles table.

A Tile is a multi-resolution HEALPix pixel. HEALPix is a tree data structure. At the lowest level (level 0) it has 12 nodes; at all nodes beneath that, it is a quadtree. A multi-resolution HEALPix pixel has several equivalent representations:

1. A level (a nonnegative integer) and a pixel number between 0 and 12 (2^level)^2 - 1 inclusive
2. An interval (start and end) of pixel indices at a common very high level in the tree (level 29 for 64-bit data)

The Tile table currently uses the second representation. If there are N tiles that are each stored as lists sorted by starting pixel index, then finding the union of tiles simply amounts to a merge of N sorted lists followed by a de-duplication step.

Similar algorithms are commonly used for manipulating time interval data (e.g. https://lscsoft.docs.ligo.org/ligo-segments/) and genomics (https://pyranges.readthedocs.io/en/latest/autoapi/pyranges/pyranges/index.html).

This algorithm is not that complicated to implement in Python, but it needs to be done within the database. I have struggled to figure out how to do this in basic SQL. A solution might involve CTEs, window functions, or procedural SQL.

cds-astro/mocpy#93