smonkewitz
commented
1 year ago If you mean for the non-spatial functions, then no, I don't recall ever porting any of them.
If you mean for the spatial UDFs, then I remember considering it, but thinking that we should try to make one of the preexisting solutions work. In particular:
- PostGIS, making sure to always pass
use_spheroid=falseso that the functions assume a perfectly spherical ~cow~ earth, at which point you can map between that an the celestial sphere with a scaling factor. As a bonus, this is likely to be easy to port to systems like BigQuery and would be directly supported by AlloyDB. - Q3C
We were using PostGIS to support spatial image search in IRSA's query server (Judith should know if we still are). I have a vague feeling that it might not do so well on large tables containing just points, but I don't actually remember if we evaluated its performance for that use case - sorry!
Anyway, if the options above are not suitable, note that Postgres is extremely extensible and you probably wouldn't want to port what scisql does for MySQL/Maria as is. For example, Postgres supports table-valued UDFs (see e.g. unnest), and so one could imagine that scisql_s2CircleRegion could return HTM ID ranges directly rather than via a temporary table, enabling ID ranges to be produced in the queries that need them, possibly in a way that the user never even sees. I think Q3C is probably doing something along those lines (I remember that its query syntax was nicer than what I managed for MySQL) - its implementation is likely worth a look.
@smonkewitz We might have a medium-term need to get these functions working on PostgreSQL as well. Since IPAC uses PostgreSQL extensively, I was wondering: did you ever play with implementing similar functions there?