-
Notifications
You must be signed in to change notification settings - Fork 4
/
Copy pathindex.html
2468 lines (2321 loc) · 96.8 KB
/
index.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>sciSQL 0.3 Documentation</title>
<link href="docs.css" type="text/css" rel="stylesheet" />
<link href="prettify/prettify.css" type="text/css" rel="stylesheet" />
<script type="text/javascript" src="prettify/prettify.js"></script>
<script type="text/javascript" src="jquery-1.6.1.min.js"></script>
</head>
<body>
<div id="header">sciSQL 0.3: Science Tools for MySQL</div>
<div id="index">Index</div>
<div id="title"></div>
<div id="nav">
<ul>
<li>
<h3><a href="#overview">Overview</a></h3>
</li>
<li>
<h3><a href="#install">Build, Installation, and Deployment</a></h3>
</li>
<li>
<h3><a href="#s2">Spherical Geometry</a></h3>
<h4>UDFs</h4>
<ul class="section_nav">
<li><a href="#s2-scisql_angSep" title="Returns the angular separation in degrees between two positions on the unit sphere.">scisql_angSep</a></li>
<li><a href="#s2-scisql_s2CPolyHtmRanges" title="Returns a binary-string representation of HTM ID ranges overlapping a spherical convex polygon.">scisql_s2CPolyHtmRanges</a></li>
<li><a href="#s2-scisql_s2CPolyToBin" title="Returns a binary-string representation of a spherical convex polygon.">scisql_s2CPolyToBin</a></li>
<li><a href="#s2-scisql_s2CircleHtmRanges" title="Returns a binary-string representation of HTM ID ranges overlapping a circle on the unit sphere.">scisql_s2CircleHtmRanges</a></li>
<li><a href="#s2-scisql_s2HtmId" title="Returns the HTM ID of a point at the given subdivision level.">scisql_s2HtmId</a></li>
<li><a href="#s2-scisql_s2HtmLevel" title="Returns the subdivision level of the given HTM ID.">scisql_s2HtmLevel</a></li>
<li><a href="#s2-scisql_s2PtInBox" title="Returns 1 if the point (lon, lat) lies inside the given longitude/latitude angle box, and 0 otherwise.">scisql_s2PtInBox</a></li>
<li><a href="#s2-scisql_s2PtInCPoly" title="Returns 1 if the point (lon, lat) lies inside the given spherical convex polygon and 0 otherwise.">scisql_s2PtInCPoly</a></li>
<li><a href="#s2-scisql_s2PtInCircle" title="Returns 1 if the point (lon, lat) lies inside the given spherical circle and 0 otherwise.">scisql_s2PtInCircle</a></li>
<li><a href="#s2-scisql_s2PtInEllipse" title="Returns 1 if the point (lon, lat) lies inside the given spherical ellipse and 0 otherwise.">scisql_s2PtInEllipse</a></li>
</ul>
<h4>Stored Procedures</h4>
<ul class="section_nav">
<li><a href="#s2-scisql_s2CPolyRegion" title="Creates a temporary table `scisql.">scisql_s2CPolyRegion</a></li>
<li><a href="#s2-scisql_s2CircleRegion" title="Creates a temporary table `scisql.">scisql_s2CircleRegion</a></li>
</ul>
</li>
<li>
<h3><a href="#photometry">Photometry</a></h3>
<ul class="section_nav">
<li><a href="#photometry-scisql_abMagToDn" title="Converts an AB magnitude to a raw flux in DN.">scisql_abMagToDn</a></li>
<li><a href="#photometry-scisql_abMagToDnSigma" title="Converts an AB magnitude error to a raw flux error in DN.">scisql_abMagToDnSigma</a></li>
<li><a href="#photometry-scisql_abMagToFlux" title="Converts an AB magnitude to a calibrated flux.">scisql_abMagToFlux</a></li>
<li><a href="#photometry-scisql_abMagToFluxSigma" title="Converts an AB magnitude error to a calibrated flux error.">scisql_abMagToFluxSigma</a></li>
<li><a href="#photometry-scisql_abMagToNanojansky" title="Converts an AB magnitude to a calibrated flux.">scisql_abMagToNanojansky</a></li>
<li><a href="#photometry-scisql_abMagToNanojanskySigma" title="Converts an AB magnitude error to a calibrated flux error.">scisql_abMagToNanojanskySigma</a></li>
<li><a href="#photometry-scisql_dnToAbMag" title="Converts a raw flux in DN to an AB magnitude.">scisql_dnToAbMag</a></li>
<li><a href="#photometry-scisql_dnToAbMagSigma" title="Converts a raw flux error to an AB magnitude error.">scisql_dnToAbMagSigma</a></li>
<li><a href="#photometry-scisql_dnToFlux" title="Converts a raw flux in DN to a calibrated (AB) flux.">scisql_dnToFlux</a></li>
<li><a href="#photometry-scisql_dnToFluxSigma" title="Converts a raw flux error to a calibrated (AB) flux error.">scisql_dnToFluxSigma</a></li>
<li><a href="#photometry-scisql_fluxToAbMag" title="Converts a calibrated (AB) flux to an AB magnitude.">scisql_fluxToAbMag</a></li>
<li><a href="#photometry-scisql_fluxToAbMagSigma" title="Converts a calibrated (AB) flux error to an AB magnitude error.">scisql_fluxToAbMagSigma</a></li>
<li><a href="#photometry-scisql_fluxToDn" title="Converts a calibrated (AB) flux to a raw DN value.">scisql_fluxToDn</a></li>
<li><a href="#photometry-scisql_fluxToDnSigma" title="Converts a calibrated (AB) flux error to raw flux error in DN.">scisql_fluxToDnSigma</a></li>
<li><a href="#photometry-scisql_nanojanskyToAbMag" title="Converts a calibrated (AB) flux to an AB magnitude.">scisql_nanojanskyToAbMag</a></li>
<li><a href="#photometry-scisql_nanojanskyToAbMagSigma" title="Converts a calibrated (AB) flux error to an AB magnitude error.">scisql_nanojanskyToAbMagSigma</a></li>
</ul>
</li>
<li>
<h3><a href="#statistics">Statistics</a></h3>
<ul class="section_nav">
<li><a href="#statistics-scisql_median" title="Returns the median of a GROUP of values.">scisql_median</a></li>
<li><a href="#statistics-scisql_percentile" title="Returns the desired percentile of a GROUP of values.">scisql_percentile</a></li>
</ul>
</li>
<li>
<h3><a href="#misc">Miscellaneous</a></h3>
<h4>UDFs</h4>
<ul class="section_nav">
<li><a href="#misc-scisql_extractInt64" title="Extracts a 64-bit integer stored in host byte order from a binary string.">scisql_extractInt64</a></li>
<li><a href="#misc-scisql_getVersion" title="Returns the version of the sciSQL library in use.">scisql_getVersion</a></li>
<li><a href="#misc-scisql_raiseError" title="Fails with an optional error message.">scisql_raiseError</a></li>
</ul>
<h4>Stored Procedures</h4>
<ul class="section_nav">
<li><a href="#misc-scisql_grantPermissions" title="Gives a user connecting from the specified host permission to call sciSQL stored procedures and to create/use temporary tables in the scisql database.">scisql_grantPermissions</a></li>
</ul>
</li>
</ul>
</div> <!-- end of #nav -->
<div id="content">
<!-- section anchors -->
<a name="overview"></a>
<a name="install"></a>
<a name="s2"></a>
<a name="photometry"></a>
<a name="statistics"></a>
<a name="misc"></a>
<div id="section-overview" class="section">
<div class="section-docs">
<p>
sciSQL provides science-specific tools and extensions for SQL. Currently, the project contains user
defined functions (UDFs) and stored procedures for MySQL or MariaDB in the areas of spherical
geometry, statistics, and photometry. The project was motivated by the needs of the <a href="http://www.lsst.org/">Rubin Observatory Legacy Survey of Space and Time</a> (LSST) and has been
sponsored by LSST and <a href="http://slac.stanford.edu/">SLAC</a> /
<a href="http://www.energy.gov/">DOE</a>. sciSQL is distributed under the terms of the
<a href="http://www.apache.org/licenses/LICENSE-2.0">Apache License version 2.0</a>.
</p>
<ul>
<li><a href="https://github.com/smonkewitz/scisql">Source code</a></li>
<li><a href="https://github.com/smonkewitz/scisql/issues/new">Report a bug</a></li>
</ul>
<p>
sciSQL is also distributed with a pair of Javascript libraries. These are:
</p>
<ul>
<li>
<a href="http://jquery.com/">jQuery</a>, copyright John Resig and
dual-licensed under the <a href="http://jquery.org/license/">MIT
and GPL version 2 licenses</a>
</li>
<li>
<a href="http://code.google.com/p/google-code-prettify/">Google prettify</a>,
distributed under the <a href="http://www.apache.org/licenses/LICENSE-2.0">
Apache License version 2.0</a>
</li>
</ul>
</div>
</div> <!-- end of #section-overview -->
<div id="section-install" class="section">
<div class="section-docs">
<p>
Read on for instructions on how to configure, build, test, install and uninstall the
sciSQL software.
</p>
<h3>Prerequisites</h3>
<dl>
<dt><a href="http://www.python.org/download/">Python 2.5.x or later</a></dt>
<dt><a href="http://python-future.org/index.html">Python future 0.16 or later</a></dt>
<dt><a href="https://dev.mysql.com/downloads/mysql/">MySQL server 8.x</a> -or-
<a href="https://mariadb.com/downloads/">MariaDB server 10.x</a></dt>
<dt><a href="https://github.com/PyMySQL/mysqlclient">mysqlclient 2.1.x or later</a></dt>
<dd>
This is a Python DB API 2.0 implementation for MySQL/MariaDB, and is
required when running the unit tests and uninstalling sciSQL.
</dd>
<dt><a href="http://www.makotemplates.org/download.html">Mako 0.4 or later</a></dt>
<dd>
This Python templating library is required when
rebuilding release documentation.
</dd>
</dl>
<p>
In order to install the UDFs, you will need write permission to the MySQL/MariaDB server
plug-in directory, as well as a MySQL/MariaDB account with admin priviledges.
</p>
<h3 class="warning">Databases reserved for sciSQL use</h3>
<p>
The following database names are reserved for use by sciSQL:
</p>
<dl>
<dt>scisql</dt>
<dd>Contains sciSQL stored procedures.</dd>
<dt>scisql_test</dt>
<dd>Used by sciSQL unit tests.</dd>
<dt>scisql_demo</dt>
<dd>Contains sample data that can be used to exercise the sciSQL UDFs.</dd>
</dl>
<p>
The scisql_demo database is dropped and re-created during installation. If you
are using it for other things, you must migrate its contents to a different
database prior to installing sciSQL. If you do not,
<strong>YOU WILL LOSE DATA</strong>.
</p>
<p>
Even though the scisql and scisql_test databases are never automatically
dropped, their use is <strong>STRONGLY DISCOURAGED</strong>.
</p>
<h3>Build configuration</h3>
<p>
Run <tt>./configure</tt> from the top-level sciSQL directory. Passing <tt>--help</tt>
will yield a list of configuration options. Here are the ones most likely to require
tweaking if <tt>./configure</tt> doesn't work straight out of the box on your system:
</p>
<dl>
<dt><tt>--mysql-dir</tt></dt>
<dd>Set this to the top-level of the MySQL/MariaDB server install tree</dd>
<dt><tt>--mysql-config</tt></dt>
<dd>Point to <tt>mysql_config</tt> or <tt>mariadb_config</tt> configuration tool</dd>
<dt><tt>--mysql-includes</tt></dt>
<dd>Point to MySQL/MariaDB headers (<tt>mysql.h</tt> and dependents)</dd>
<dt><tt>--scisql-prefix</tt></dt>
<dd>
This string will be used as a prefix for all sciSQL UDF and stored procedure
names. You can specify an empty string, which will result in unprefixed names.
The default is "scisql_".
</dd>
</dl>
<p>
If you wish to build/install only the sciSQL client utilities and documentation,
run configure with the <tt>--client-only</tt> option. In this case, a MySQL/MariaDB server or
client install is not required, the only executable generated is scisql_index
(a utility which generates HTM indexes for tables of circles or polygons stored
in tab-separated-value format).
</p>
<h3>Build</h3>
<p>
sciSQL is built and staged with the usual <tt>make</tt> and <tt>make install</tt>
commands.
</p>
<p>
You may wish to regenerate the HTML documentation if you've chosen a
non-default value for <tt>--scisql-prefix</tt>, as the HTML distributed with release
tar-balls is built under the assumption that <tt>--scisql-prefix="scisql_"</tt>.
To do this, run <tt>make html_docs</tt>.
</p>
<h3>Deploying sciSQL in a MySQL/MariaDB instance</h3>
<p> Assuming you've installed scisql in <tt>$PREFIX</tt>, update your <tt>PATH</tt> and
<tt>PYTHONPATH</tt> as described below:
<pre class="prettyprint lang-bash linenums">
export PATH="$PREFIX/bin:$PATH"
export PYTHONPATH="$PREFIX/python:$PATH"</pre>
Check that you have access to a local server instance and run <tt>scisql-deploy.py</tt>. Passing
<tt>--help</tt> will yield a list of configuration options. Here are the ones most likely to require
tweaking:
</p>
<dl>
<dt><tt>--mysql-user</tt></dt>
<dd>Set this to the name of a MySQL/MariaDB admin user; defaults to <tt>root</tt></dd>
<dt><tt>--mysql-bin</tt></dt>
<dd>Point to the <tt>mysql</tt> or <tt>mariadb</tt> command-line client</dd>
<dt><tt>--mysql-socket</tt></dt>
<dd>Point to the MySQL/MariaDB server UNIX socket file</dd>
<dt><tt>--verbose</tt></dt>
<dd>Verbosity level; possible value are FATAL, ERROR, WARN, INFO, DEBUG</dd>
</dl>
<p>
You will be prompted for the MySQL/MariaDB admin user password during configuration.
If you run <tt>scisql-deploy.py</tt> in a script, you can
use standard input, for example via a pipe, for providing this password.
Connection details, including this password, are stored in a temporary directory in a file named
<tt>my-client.cnf</tt> using the MySQL options file format. This temporary file is removed at the end
of the process, unless you set the verbose level to DEBUG.
</p>
<p>
The <tt>scisql-deploy.py</tt> command will CREATE the sciSQL UDFs, stored procedures, and
databases (including the scisql_demo database). It will also automatically
run the sciSQL integration tests, which check that sciSQL is correctly deployed.
You can re-run the tests anytime by invoking <tt>scisql-deploy.py</tt> with the
<tt>--test</tt> option.
</p>
<p>
Finally, note that after installation each UDF will be available under two
names: one including a version number and one without. For example, assuming
that <tt>--scisql-prefix="foo_"</tt> and that the sciSQL version number is
<tt>1.2.3</tt>, a hypothetical UDF named <tt>bar</tt> would be available as:
</p>
<ul><li>foo_bar</li><li>foo_bar_1_2_3</li></ul>
<p>
Invoking <tt>scisql-deploy.py</tt> with the <tt>--undeploy</tt> option
will drop the versioned sciSQL UDFs and stored
procedures. It will also drop the unversioned UDFs/procedures, but only
if the unversioned UDF/procedure was created by the version of
sciSQL being uninstalled. As a consequence, it is possible to have
multiple versions of sciSQL installed at the same time, and the behavior
of two versions can be compared from within a single MySQL/MariaDB instance.
An unversioned name will resolve to the most recently installed versioned
name.
</p>
<p>
An updeploy will also drop the scisql_demo database.
</p>
<h3>Rebuilding sciSQL</h3>
<p>
If you've already installed sciSQL, say using a UDF/procedure name
prefix of "foo_", and then decide you'd like to change the prefix to "bar_",
do the following from the top-level sciSQL directory:
</p>
<dl>
<dt><tt>scisql-deploy.py ... --undeploy</tt></dt><dd>Uninstalls the UDFs and stored procedures named <tt>foo_*</tt>.</dd>
<dt><tt>make distclean</tt></dt><dd>Removes all build products and configuration files.</dd>
<dt><tt>configure --scisql-prefix=bar_ ...</tt></dt><dd>Reconfigures sciSQL (sets new name prefix).</dd>
<dt><tt>make</tt></dt><dd>Rebuilds sciSQL.</dd>
<dt><tt>make install</tt></dt><dd>Restages sciSQL.</dd>
<dt><tt>scisql-deploy.py ...</tt></dt><dd>Redeploys sciSQL</dd>
</dl>
<p>
No MySQL/MariaDB restart is required. Note that multiple installations of the same
version of sciSQL with different UDF/procedure name prefixes can coexist on
a single MySQL/MariaDB instance.
</p>
<h3>MySQL/MariaDB server restarts</h3>
<p>
Installing different versions of sciSQL, or multiple copies of the same
version with different UDF/procedure name prefixes, does not require a
server restart.
</p>
<p>
Only sciSQL developers should need to perform restarts. They are
required when changing the name of a UDF without changing the name of the
shared library installed into the server plugin directory. In this case,
an attempt to install the updated shared library will sometimes result in
MySQL/MariaDB reporting that it cannot find symbol names that are actually
present. This is presumably due to server and/or OS level caching
effects, and restarting the server resolves the problem.
</p>
<h3>Reporting bugs and getting help</h3>
<p>
If you encounter test-case failures, or think you've identified a
bug in the sciSQL code, or would just like to ask a question, please
<a href="https://github.com/smonkewitz/scisql/issues">submit an issue</a>.
</p>
</div>
</div> <!-- end of #section-install -->
<div id="section-s2" class="section">
<div class="section-docs">
<p>
The aim of the spherical geometry UDFs and stored procedures is to
allow quick answers to the following sorts of questions:
</p>
<ol>
<li>
<em>Which points in a table lie inside a region on the sphere?</em> For example,
an astronomer might wish to know which stars and galaxies lie inside the
region of the sky observed by a single camera CCD.
</li>
<li>
<em>Which spherical regions in a table contain a particular point?</em> For
example, an astronomer might with to know which telescope images overlap
the position of interesting object X.
</li>
</ol>
<h3>HTM indexing</h3>
<p>
To accelerate these types of queries, sciSQL maps points/regions
to the integer ID(s) of their containing/overlapping triangles in a
Hierarchical Triangular Mesh (HTM). This is a decomposition of the
unit sphere defined by A. Szalay, T. Budavari, G. Fekete at the
Johns Hopkins University, and Jim Gray, Microsoft Research. See
the following links for more information:
</p>
<ul>
<li><a href="http://voservices.net/spherical/">http://voservices.net/spherical/</a></li>
<li><a href="http://adsabs.harvard.edu/abs/2010PASP..122.1375B">http://adsabs.harvard.edu/abs/2010PASP..122.1375B</a></li>
</ul>
<p>
To accelerate spatial queries, standard B-tree indexes are created
on the point/region HTM IDs and spatial constraints are expressed
in terms of those IDs. This allows the database optimizer to restrict
the rows that must be considered by a spatial query.
</p>
<p>
Read on to learn how to create and take advantage of HTM indexes on
tables containing spatial data. The examples below can be run in the
scisql_demo database, which contains all of the referenced tables
and a tiny amount of sample data.
</p>
<h3>Supported region types</h3>
<p>
sciSQL supports 4 kinds of regions: longitude/latitude angle boxes,
spherical circles (defined by a center and opening angle), spherical
ellipses (the orthographic projection of a standard 2-d ellipse onto
the sphere, where the 2-d ellipse is on a plane tangent to the unit
sphere at the ellipse center), and spherical convex polygons (where
polygon edges are great circles). Note also that spherical convex
polygons have a binary representation, produced by
scisql_s2CPolyToBin(), allowing them to be stored as values
in a BINARY table column.
</p>
<h3>Points-in-region queries</h3>
<p>
sciSQL contains several UDFs for checking whether a point lies inside
a region. These are: scisql_s2PtInBox(),
scisql_s2PtInCircle(), scisql_s2PtInCPoly() and
scisql_s2PtInEllipse(). They return 1 if the input point is
inside the input region and 0 otherwise.
</p>
<p>
Given these UDFs, a simple way to answer question 1 is illustrated by
the following example:
</p>
<pre class="prettyprint lang-sql linenums">
SELECT objectId
FROM Object
WHERE scisql_s2PtInCircle(ra_PS, decl_PS, 0, 0, 0.01) = 1;</pre>
<p>
This query returns all the objects within 0.01 degrees of
(RA, Dec) = (0, 0). It is inefficient for small search regions
because the scisql_s2PtInCircle() UDF must be called for
every row in the <tt>Object</tt> table.
</p>
<p>
Lets assume that <tt>Object</tt> contains an indexed BIGINT column
named <tt>htmId20</tt>. If it does not, the column and index can be
added with ALTER TABLE. <tt>htmId20</tt> can be populated with the
subdivision-level 20 HTM IDs of object positions as follows:
</p>
<pre class="prettyprint lang-sql linenums">
ALTER TABLE Object DISABLE KEYS;
UPDATE Object
SET htmId20 = scisql_s2HtmId(ra_PS, decl_PS, 20);
ALTER TABLE Object ENABLE KEYS;</pre>
<p>
The HTM subdivision level must be between 0 and 24. At subdivision
level N, there are 8*4<sup>N</sup> triangles in the mesh, so the
higher subdivision levels correspond to finer tesselations of the
unit sphere.
</p>
<p>
Now that HTM IDs for object positions are available and indexed,
the query above can be made more efficient:
</p>
<pre class="prettyprint lang-sql linenums">
CALL scisql.scisql_s2CircleRegion(0, 0, 0.01, 20);
SELECT o.objectId
FROM Object AS o INNER JOIN scisql.Region AS r
ON (o.htmId20 BETWEEN r.htmMin AND r.htmMax)
WHERE scisql_s2PtInCircle(o.ra_PS, o.decl_PS, 0, 0, 0.01) = 1;</pre>
<p>
What's going on here? The first line in the example calls the
scisql_s2CircleRegion() stored procedure. This procedure
creates a temporary table called <tt>scisql.Region</tt> with two
BIGINT NOT NULL columns named htmMin and htmMax. It then stores
the HTM IDs overlapping the search region in <tt>scisql.Region</tt>
(as ranges).
</p>
<p>
Next, the original query is augmented with a join against
<tt>scisql.Region</tt>. This limits the objects considered by
scisql_s2PtInCircle() to those within the HTM triangles
overlapping the search region; the index on htmId20 allows MySQL to
retrieve these objects very quickly when the search region is small.
Note that if the search region is large (meaning that a large fraction
of the table being searched is inside the search region), then the
original query may actually be faster.
</p>
<p>
Here is another example, this time with a search region taken from
a table called <tt>Science_Ccd_Exposure</tt>. This table includes a
a column named <tt>poly</tt> that contains polygonal approximations
to the regions of the sphere observed by CCD exposures.
</p>
<pre class="prettyprint lang-sql linenums">
SELECT poly FROM Science_Ccd_Exposure
WHERE scienceCcdExposureId = 43856062009
INTO @poly;
CALL scisql.scisql_s2CPolyRegion(@poly, 20);
SELECT o.objectId
FROM Object AS o INNER JOIN scisql.Region AS r
ON (o.htmId20 BETWEEN r.htmMin AND r.htmMax)
WHERE scisql_s2PtInCPoly(o.ra_PS, o.decl_PS, @poly) = 1;</pre>
<p>
The first statement stores the polygonal boundary of a particular CCD
exposure into the user variable <tt>@poly</tt>, the second computes
overlapping HTM IDs, and the third performs the points-in-region
query as before.
</p>
<h3>Regions-containing-point queries</h3>
<p>
An example for this type of query is:
</p>
<pre class="prettyprint lang-sql linenums">
SELECT scienceCcdExposureId FROM Science_Ccd_Exposure
WHERE scisql_s2PtInCPoly(0, 0, poly) = 1;</pre>
<p>
This query returns all the CCD exposures containing the point
(RA, Dec) = (0, 0). To accelerate it using HTM indexing, an
auxiliary table is introduced:
</p>
<pre class="prettyprint lang-sql linenums">
CREATE TABLE Science_Ccd_Exposure_HtmId10 (
scienceCcdExposureId BIGINT NOT NULL,
htmId10 INTEGER NOT NULL,
PRIMARY KEY (htmId10, scienceCcdExposureId),
KEY (scienceCcdExposureId)
);</pre>
<p>
<tt>Science_Ccd_Exposure_HtmId10</tt> will store the level 10 HTM ID
of every triangle overlapping a CCD exposure. To populate it, start
by dumping the primary key and polygon vertex colunms from
<tt>Science_Ccd_Exposure</tt>:
</p>
<pre class="prettyprint lang-bash linenums">
rm -f /tmp/scisql_demo_ccds.tsv</pre>
<pre class="prettyprint lang-sql linenums">
SELECT scienceCcdExposureId,
llcRa, llcDecl,
ulcRa, ulcDecl,
urcRa, urcDecl,
lrcRa, lrcDecl
FROM Science_Ccd_Exposure
INTO OUTFILE '/tmp/scisql_demo_ccds.tsv';</pre>
<p>
Then, run the sciSQL region indexing utility:
</p>
<pre class="prettyprint lang-bash linenums">
sudo chmod a+r /tmp/scisql_demo_ccds.tsv
scisql_index -l 10 /tmp/scisql_demo_htmid10.tsv /tmp/scisql_demo_ccds.tsv</pre>
<p>
and load the results:
</p>
<pre class="prettyprint lang-sql linenums">
TRUNCATE TABLE Science_Ccd_Exposure_HtmId10;
LOAD DATA LOCAL INFILE '/tmp/scisql_demo_htmid10.tsv' INTO TABLE Science_Ccd_Exposure_HtmId10;</pre>
<p>
The example regions-containing-point query can now be expressed
more efficiently as:
</p>
<pre class="prettyprint lang-sql linenums">
SELECT sce.scienceCcdExposureId
FROM Science_Ccd_Exposure AS sce, (
SELECT scienceCcdExposureId
FROM Science_Ccd_Exposure_HtmId10
WHERE htmId10 = scisql_s2HtmId(0, 0, 10)
) AS h
WHERE sce.scienceCcdExposureId = h.scienceCcdExposureId AND
scisql_s2PtInCPoly(0, 0, sce.poly) = 1;</pre>
</div>
<h2>User Defined Functions</h2>
<div id="udf-s2-scisql_angSep" class="udf">
<h3><a name="s2-scisql_angSep"></a>scisql_angSep</h3>
<table class="signature">
<tr><td class="decl" colspan="5">FUNCTION scisql_angSep (
</td></tr>
<tr>
<td class="argkind"> </td>
<td class="argname">lon1</td>
<td class="argtype">DOUBLE PRECISION,</td>
<td class="argunits">deg</td>
<td class="argdesc">Longitude angle of first position.</td>
</tr>
<tr>
<td class="argkind"> </td>
<td class="argname">lat1</td>
<td class="argtype">DOUBLE PRECISION,</td>
<td class="argunits">deg</td>
<td class="argdesc">Latitude angle of first position.</td>
</tr>
<tr>
<td class="argkind"> </td>
<td class="argname">lon2</td>
<td class="argtype">DOUBLE PRECISION,</td>
<td class="argunits">deg</td>
<td class="argdesc">Longitude angle of second position.</td>
</tr>
<tr>
<td class="argkind"> </td>
<td class="argname">lat2</td>
<td class="argtype">DOUBLE PRECISION</td>
<td class="argunits">deg</td>
<td class="argdesc">Latitude angle of second position.</td>
</tr>
<tr><td class="decl" colspan="5">) RETURNS DOUBLE PRECISION</td></tr>
</table>
<table class="signature">
<tr><td class="decl" colspan="5">FUNCTION scisql_angSep (
</td></tr>
<tr>
<td class="argkind"> </td>
<td class="argname">x1</td>
<td class="argtype">DOUBLE PRECISION,</td>
<td class="argunits"></td>
<td class="argdesc">X coordinate of first position.</td>
</tr>
<tr>
<td class="argkind"> </td>
<td class="argname">y1</td>
<td class="argtype">DOUBLE PRECISION,</td>
<td class="argunits"></td>
<td class="argdesc">Y coordinate of first position.</td>
</tr>
<tr>
<td class="argkind"> </td>
<td class="argname">z1</td>
<td class="argtype">DOUBLE PRECISION,</td>
<td class="argunits"></td>
<td class="argdesc">Z coordinate of first position.</td>
</tr>
<tr>
<td class="argkind"> </td>
<td class="argname">x2</td>
<td class="argtype">DOUBLE PRECISION,</td>
<td class="argunits"></td>
<td class="argdesc">X coordinate of second position.</td>
</tr>
<tr>
<td class="argkind"> </td>
<td class="argname">y2</td>
<td class="argtype">DOUBLE PRECISION,</td>
<td class="argunits"></td>
<td class="argdesc">Y coordinate of second position.</td>
</tr>
<tr>
<td class="argkind"> </td>
<td class="argname">z2</td>
<td class="argtype">DOUBLE PRECISION</td>
<td class="argunits"></td>
<td class="argdesc">Z coordinate of second position.</td>
</tr>
<tr><td class="decl" colspan="5">) RETURNS DOUBLE PRECISION</td></tr>
</table>
<div class="description">
Returns the angular separation in degrees between two
positions on the unit sphere.
Positions may be specified either as spherical coordinate pairs
(lon1, lat1) and (lon2, lat2), or as 3-vectors (x1, y1, z1) and
(x2, y2, z2) with arbitrary norm. If spherical coordinates are used,
all arguments are assumed to be in units of degrees.
</div>
<h5>Notes</h5>
<ul class="notes">
<li class="">All arguments must be convertible to type DOUBLE PRECISION.</li>
<li class="">If any argument is NULL, NaN, or +/-Inf, NULL is returned. MySQL
does not currently support storage of IEEE special values. However,
their presence is checked for to ensure reasonable behavior if a
future MySQL release does end up supporting them.</li>
<li class="">If spherical coordinates are passed in and either latitude
angle is not in the [-90, 90] degree range, NULL is returned.</li>
</ul>
<h5>Examples</h5>
<pre class="prettyprint lang-sql linenums">
SELECT scisql_angSep(0, 0, 0, 90);
SELECT scisql_angSep(1, 0, 0, 0, 0, 1);
SELECT scisql_angSep(ra_PS, decl_PS, ra_SG, decl_SG) FROM Object LIMIT 10;
</pre>
</div>
<div id="udf-s2-scisql_s2CPolyHtmRanges" class="udf internal">
<h3><a name="s2-scisql_s2CPolyHtmRanges"></a>[internal] scisql_s2CPolyHtmRanges</h3>
<table class="signature">
<tr><td class="decl" colspan="5">FUNCTION scisql_s2CPolyHtmRanges (
</td></tr>
<tr>
<td class="argkind"> </td>
<td class="argname">poly</td>
<td class="argtype">BINARY,</td>
<td class="argunits"></td>
<td class="argdesc">Binary string representation of a polygon.</td>
</tr>
<tr>
<td class="argkind"> </td>
<td class="argname">level</td>
<td class="argtype">INTEGER,</td>
<td class="argunits"></td>
<td class="argdesc">HTM subdivision level, must be in range [0, 24].</td>
</tr>
<tr>
<td class="argkind"> </td>
<td class="argname">maxranges</td>
<td class="argtype">INTEGER</td>
<td class="argunits"></td>
<td class="argdesc">Maximum number of ranges to report.</td>
</tr>
<tr><td class="decl" colspan="5">) RETURNS MEDIUMBLOB</td></tr>
</table>
<div class="description">
Returns a binary-string representation of HTM ID ranges
overlapping a spherical convex polygon. The polygon must
be specified in binary-string form (as produced by
scisql_s2CPolyToBin()).
</div>
<h5>Notes</h5>
<ul class="notes">
<li class="">If any parameter is NULL, this is an error
and NULL is returned.</li>
<li class="">If poly does not correspond to a valid binary serialization
of a spherical convex polygon, this is an error and NULL
is returned.</li>
<li class="">If level does not lie in the range [0, 24], this is an
error and NULL is returned.</li>
<li class="">maxranges can be set to any value. In practice, its value
is clamped such that the binary return string has size at
most 16MB (fits in a MEDIUMBLOB). Negative values are
interpreted to mean: "return as many ranges as possible
subject to the 16MB output size limit".</li>
</ul>
</div>
<div id="udf-s2-scisql_s2CPolyToBin" class="udf">
<h3><a name="s2-scisql_s2CPolyToBin"></a>scisql_s2CPolyToBin</h3>
<table class="signature">
<tr><td class="decl" colspan="5">FUNCTION scisql_s2CPolyToBin (
</td></tr>
<tr>
<td class="argkind"> </td>
<td class="argname">v1Lon</td>
<td class="argtype">DOUBLE PRECISION,</td>
<td class="argunits">deg</td>
<td class="argdesc">Longitude angle of first polygon vertex.</td>
</tr>
<tr>
<td class="argkind"> </td>
<td class="argname">v1Lat</td>
<td class="argtype">DOUBLE PRECISION,</td>
<td class="argunits">deg</td>
<td class="argdesc">Latitude angle of first polygon vertex.</td>
</tr>
<tr>
<td class="argkind"> </td>
<td class="argname">v2Lon</td>
<td class="argtype">DOUBLE PRECISION,</td>
<td class="argunits">deg</td>
<td class="argdesc">Longitude angle of second polygon vertex.</td>
</tr>
<tr>
<td class="argkind"> </td>
<td class="argname">v2Lat</td>
<td class="argtype">DOUBLE PRECISION,</td>
<td class="argunits">deg</td>
<td class="argdesc">Latitude angle of second polygon vertex.</td>
</tr>
<tr><td class="argkind"> </td><td class="argname">...</td><td class="decl" colspan="3"></td></tr>
<tr><td class="decl" colspan="5">) RETURNS BINARY</td></tr>
</table>
<div class="description">
Returns a binary-string representation of a spherical convex
polygon. The polygon must be specified as a sequence of at least
3 and at most 20 vertices. An N vertex input will result in a
binary string of length exactly 24*(N + 1).
</div>
<h5>Notes</h5>
<ul class="notes">
<li class="">If any parameter is NULL, NaN or +/-Inf, this is an error
and NULL is returned.</li>
<li class="">If any latitude angle lies outside of [-90, 90] degrees,
this is an error and NULL is returned.</li>
<li class="">Polygon vertices can be specified in either clockwise or
counter-clockwise order. However, the vertices are assumed to be
hemispherical, to define edges that do not intersect except at
vertices, and to define edges that form a convex polygon.</li>
<li class="">Input coordinate must be convertible to type DOUBLE PRECISION.
If their actual type is BIGINT or DECIMAL, then the conversion
can result in loss of precision and hence an inaccurate result.
Loss of precision will not occur so long as the inputs are values of
type DOUBLE PRECISION, FLOAT, REAL, INTEGER, SMALLINT, or TINYINT.</li>
</ul>
<h5>Examples</h5>
<pre class="prettyprint lang-sql linenums">
CREATE TEMPORARY TABLE Poly (
ra1 DOUBLE PRECISION NOT NULL,
dec1 DOUBLE PRECISION NOT NULL,
ra2 DOUBLE PRECISION NOT NULL,
dec2 DOUBLE PRECISION NOT NULL,
ra3 DOUBLE PRECISION NOT NULL,
dec3 DOUBLE PRECISION NOT NULL,
poly BINARY(96) DEFAULT NULL
);
INSERT INTO Poly VALUES (-10, 0,
10, 0,
0, 10,
NULL);
UPDATE Poly
SET poly = scisql_s2CPolyToBin(
ra1, dec1,
ra2, dec2,
ra3, dec3);
</pre>
</div>
<div id="udf-s2-scisql_s2CircleHtmRanges" class="udf internal">
<h3><a name="s2-scisql_s2CircleHtmRanges"></a>[internal] scisql_s2CircleHtmRanges</h3>
<table class="signature">
<tr><td class="decl" colspan="5">FUNCTION scisql_s2CircleHtmRanges (
</td></tr>
<tr>
<td class="argkind"> </td>
<td class="argname">centerLon</td>
<td class="argtype">DOUBLE PRECISION,</td>
<td class="argunits">deg</td>
<td class="argdesc">Longitude angle of circle center.</td>
</tr>
<tr>
<td class="argkind"> </td>
<td class="argname">centerLat</td>
<td class="argtype">DOUBLE PRECISION,</td>
<td class="argunits">deg</td>
<td class="argdesc">Latitude angle of circle center.</td>
</tr>
<tr>
<td class="argkind"> </td>
<td class="argname">radius</td>
<td class="argtype">DOUBLE PRECISION,</td>
<td class="argunits">deg</td>
<td class="argdesc">Circle radius.</td>
</tr>
<tr>
<td class="argkind"> </td>
<td class="argname">level</td>
<td class="argtype">INTEGER,</td>
<td class="argunits"></td>
<td class="argdesc">HTM subdivision level, must be in range [0, 24].</td>
</tr>
<tr>
<td class="argkind"> </td>
<td class="argname">maxranges</td>
<td class="argtype">INTEGER</td>
<td class="argunits"></td>
<td class="argdesc">Maximum number of ranges to report.</td>
</tr>
<tr><td class="decl" colspan="5">) RETURNS MEDIUMBLOB</td></tr>
</table>
<div class="description">
Returns a binary-string representation of HTM ID ranges
overlapping a circle on the unit sphere. This string will be
at most 16MB long, i.e. it will fit in a MEDIUMBLOB.
</div>
<h5>Notes</h5>
<ul class="notes">
<li class="">The centerLon, centerLat, and radius arguments must be
convertible to type DOUBLE PRECISION. If they are of type
BIGINT or DECIMAL, then the conversion can result in loss
of precision and hence an inaccurate result. Loss of
precision will not occur so long as the inputs are values
of type DOUBLE PRECISION, FLOAT, REAL, INTEGER, SMALLINT,
or TINYINT.</li>
<li class="">The level and maxranges arguments must be integers.</li>
<li class="">If any parameter is NULL, NaN or +/-Inf, this is an error
and NULL is returned.</li>
<li class="">If centerLat is not in the [-90, 90] degree range,
this is an error and NULL is returned.</li>
<li class="">If radius is negative or greater than 180, this is
an error and NULL is returned.</li>
<li class="">If level does not lie in the range [0, 24], this is an
error and NULL is returned.</li>
<li class="">maxranges can be set to any value. In practice, its value
is clamped such that the binary return string has size at
most 16MB (fits in a MEDIUMBLOB). Negative values are
interpreted to mean: "return as many ranges as possible
subject to the 16MB output size limit".</li>
</ul>
</div>
<div id="udf-s2-scisql_s2HtmId" class="udf">
<h3><a name="s2-scisql_s2HtmId"></a>scisql_s2HtmId</h3>
<table class="signature">
<tr><td class="decl" colspan="5">FUNCTION scisql_s2HtmId (
</td></tr>
<tr>
<td class="argkind"> </td>
<td class="argname">lon</td>
<td class="argtype">DOUBLE PRECISION,</td>
<td class="argunits">deg</td>
<td class="argdesc">Longitude angle of the point to index.</td>
</tr>
<tr>
<td class="argkind"> </td>
<td class="argname">lat</td>
<td class="argtype">DOUBLE PRECISION,</td>
<td class="argunits">deg</td>
<td class="argdesc">Latitude angle of the point to index.</td>
</tr>
<tr>
<td class="argkind"> </td>
<td class="argname">level</td>
<td class="argtype">INTEGER</td>
<td class="argunits"></td>
<td class="argdesc">HTM subdivision level, required to lie in the range [0, 24].</td>
</tr>
<tr><td class="decl" colspan="5">) RETURNS BIGINT</td></tr>
</table>
<div class="description">
Returns the HTM ID of a point at the given
subdivision level.
</div>
<h5>Notes</h5>
<ul class="notes">
<li class="">If any parameter is NULL, NULL is returned.</li>
<li class="">If lon or lat is NaN or +/-Inf, this is an error and NULL is
returned (IEEE specials are not currently supported by MySQL).</li>
<li class="">If lat lies outside of [-90, 90] degrees, this is an error
and NULL is returned.</li>
<li class="">If level is not in the range [0, 24], this is an error
and NULL is returned.</li>
<li class="">The lon and lat arguments must be convertible to type DOUBLE
PRECISION. If their actual type is BIGINT or DECIMAL, then the
conversion can result in loss of precision and hence an inaccurate
result. Loss of precision will not occur so long as the inputs are
values of type DOUBLE PRECISION, FLOAT, REAL, INTEGER, SMALLINT or
TINYINT.</li>
</ul>
<h5>Examples</h5>
<pre class="prettyprint lang-sql linenums">
SELECT objectId, ra_PS, decl_PS, scisql_s2HtmId(ra_PS, decl_PS, 20)
FROM Object LIMIT 10;
</pre>
</div>
<div id="udf-s2-scisql_s2HtmLevel" class="udf">
<h3><a name="s2-scisql_s2HtmLevel"></a>scisql_s2HtmLevel</h3>
<table class="signature">
<tr><td class="decl" colspan="5">FUNCTION scisql_s2HtmLevel (
</td></tr>
<tr>
<td class="argkind"> </td>
<td class="argname">id</td>
<td class="argtype">BIGINT</td>
<td class="argunits"></td>
<td class="argdesc">HTM ID.</td>
</tr>
<tr><td class="decl" colspan="5">) RETURNS INTEGER</td></tr>
</table>
<div class="description">
Returns the subdivision level of the given HTM ID.
</div>
<h5>Notes</h5>
<ul class="notes">
<li class="">If id is NULL or an invalid HTM ID, NULL is returned.</li>
</ul>
<h5>Examples</h5>
<pre class="prettyprint lang-sql linenums">
SELECT scisql_s2HtmLevel(32);
</pre>
</div>
<div id="udf-s2-scisql_s2PtInBox" class="udf">
<h3><a name="s2-scisql_s2PtInBox"></a>scisql_s2PtInBox</h3>
<table class="signature">
<tr><td class="decl" colspan="5">FUNCTION scisql_s2PtInBox (
</td></tr>
<tr>
<td class="argkind"> </td>
<td class="argname">lon</td>
<td class="argtype">DOUBLE PRECISION,</td>
<td class="argunits">deg</td>
<td class="argdesc">Longitude angle of point to test.</td>
</tr>
<tr>
<td class="argkind"> </td>
<td class="argname">lat</td>
<td class="argtype">DOUBLE PRECISION,</td>
<td class="argunits">deg</td>
<td class="argdesc">Latitude angle of point to test.</td>
</tr>
<tr>
<td class="argkind"> </td>