Exploring the stars

SkySutra has 9,096 stars. Part 6 is about finding one: by clicking down through facets, by typing into a single smart search bar, by opening a star's own page, and by one search box that looks through stars, constellations and nakshatras at once. Almost all of it is declarative APEX. The work is in one view that gives every star the things people ask about: is it up, how bright, what colour, which nakshatra.

How Part 6 works

Before you start

You need Part 5 done. Download skysutra_explore.sql (the view) and the new skysutra_chart.sql, skysutra-chart.js and skysutra.css (the sky chart learns to ring one star in Lesson 6.3).

Lesson 6.1 — Faceted Search

Outcome: a page where you narrow 9,096 stars to the few you want by clicking, and see how many each click leaves.

The question. SS_STARS has a magnitude, a spectral type and a right ascension. People ask "what's bright and up right now?", "which red stars are in Orion?", "what's in Rohini?". Where do up now, red and Rohini come from?

The decision. A view, SS_STARS_V, that turns catalog numbers into the words people search with. Faceted Search counts every facet value after every click, so the view must be quick over all 9,096 rows.

Run skysutra_explore.sql, then try it:

select display_name, colour, nakshatra, altitude_now, up_now
  from ss_stars_v
 where proper_name in ('Regulus', 'Spica', 'Antares', 'Betelgeuse');

Two things the view taught us

Trigonometry on NUMBER is slow. The first version took 1.8 seconds for the whole catalog: too slow to run on every facet click. Oracle's NUMBER keeps 38 significant digits, and its sin, cos and asin are computed in software to that precision. Converting the angles with to_binary_double hands the same functions to the processor's floating point, and the view runs in 0.08 seconds. Fifteen digits is far more than a star chart needs. Two more changes help: the one-row now_at and home CTEs are marked /*+ materialize */ so today's ayanamsa and sidereal time are worked out once, not once per star; and "up now" uses the J2000 position without precession, which can put a star up to about a degree off (Spica: 45° in the view, 44.2° with Part 3's full calculation). That's fine for "is it up?", and the star's own page uses the full calculation.

Junction stars don't always sit in their own nakshatra. Antares is the junction star (yogatara) of Jyeshtha, but its sidereal longitude, about 225.9°, falls in the slice before, Anuradha (213°20′ to 226°40′). Betelgeuse, Ardra's junction star, falls in Mrigashira. The junction stars were chosen as markers in the sky long before anyone measured longitudes to a tenth of a degree, and Lahiri's ayanamsa is a modern convention. The view says what the longitude says, and labels the facet Nakshatra (by longitude); Lesson 6.3's page also shows which nakshatra a star is the junction star of.

Build: the Stars page

1. Create Page › Faceted Search. Page Number 30, Name Stars, Table / View Name SS_STARS_V, no breadcrumb, a new navigation entry with icon fa-star-o. Next.
2. Display as Report. Tick UP_NOW, CONSTELLATION, BRIGHTNESS, COLOUR and NAKSHATRA. The wizard pre-ticks columns with few distinct values, including CONSTELLATION_ABBR and SPECTRAL_TYPE: untick those. Create Page.
3. In Page Designer, set the facets' order and labels: Search 5; Up now? 10; Brightness 20; Constellation 30; Colour 40; Nakshatra (by longitude) 50. For Constellation and Nakshatra turn on Display Filter Initially: 88 and 27 values are too many to scroll.

Brightness as a range. BRIGHTNESS is text, so its values sort alphabetically. Better to filter the number itself: change the facet to Type Range on Database Column MAGNITUDE, Data Type Number, and give it a static list of values whose return values are ranges, low|high:

STATIC2:Brightest (under 1);|1,Bright (1 to 2);1|2,2 to 3;2|3,3 to 4: a city sky;3|4,
4 to 5: a suburban sky;4|5,5 to 6.5: a dark sky;5|6.5,Fainter: binoculars;6.5|

An empty side means "no limit". STATIC2 keeps the order as written instead of sorting it. Lower magnitude is brighter, which the labels say in words.

4. In Search Results, show Star, Vedic name, Constellation, Magnitude, Colour, Spectral type, Nakshatra, Altitude now and Up now?; make the rest Hidden Column. Set Order By to Static Value MAGNITUDE, DISPLAY_NAME, and Pagination to Row Ranges X to Y of Z, 50 rows.

Click Up now? › Up now and Brightness › Brightest: the list drops to the handful of bright stars above your home this minute, and every other facet's counts change to match.

Lesson 6.2 — Smart Filters

Outcome: the same filters as one search bar with chips, for small screens.

The question. Faceted Search spends a column of the screen on facets. On a phone at the telescope, that's most of the screen. Same data, less room?

The decision. Smart Filters: one search bar, with suggestion chips under it ("Up now", "Orange", "Rohini"), each opening the same kind of filter. It reads the same view and uses the same filter types.

1. Create Page › Smart Filters. Page Number 32, Name Find a Star, view SS_STARS_V, icon fa-search.
2. Tick the same five columns, untick the pre-ticked extras, Create Page.
3. Repeat Lesson 6.1's steps 3 and 4 on the filters (they're called filters here, not facets, but have the same properties), including Brightness as a range on MAGNITUDE.

Type ori in the bar: the suggestions offer Orion as a constellation filter and the stars whose names match. Choose Up now from the chips, then Red: the red stars above you now.

Lesson 6.3 — A star's own page

Outcome: click any star to see its facts in blue, its tradition in saffron, and where it is in your sky right now.

The question. One page for every one of 9,096 stars: how many pages do we build? (One, page 31, with the star's id in a page item.)

Build: the Star page

1. Create Page › Blank Page: 31, Star, no breadcrumb, no navigation entry (you reach it from a star, not the menu).

2. Two hidden items, P31_ID and P31_NAME. A Computation Before Header sets P31_NAME:

   select coalesce(proper_name, bayer, 'HR ' || hr_number) from ss_stars where id = :P31_ID
   

   and the page Title is &P31_NAME., so the browser tab says "Aldebaran".

3. Facts: a Classic Report, Value Attribute Pairs - Row, CSS class ss-astro, column span 6. Its query unpivots one row of SS_STARS_V with a lateral subquery, as in Lesson 5.3: constellation, magnitude, colour and spectral type, right ascension in hours and minutes, declination in degrees and arcminutes, whether it's up from your home, and its catalog numbers.

4. Tradition: the same, CSS class ss-lore, column span 6, beside Facts. Vedic name, nakshatra by longitude, and, if the star is a junction star, which nakshatra, its symbol and description:

   select l.label, l.value
     from ss_stars_v v
     join ss_nakshatras n on n.name = v.nakshatra
     left join ss_nakshatras j on j.junction_star_id = v.id,
          lateral (select 1 seq, 'Vedic name' label, v.vedic_name value from dual union all
                   select 2, 'Nakshatra by longitude', n.name || ' (' || n.english_name || ')' from dual union all
                   select 3, 'Junction star of', j.name || ' (' || j.english_name || ')' from dual where j.id is not null union all
                   select 4, 'Symbol', j.symbol from dual union all
                   select 5, 'About ' || j.name, j.description from dual) l
    where v.id = :P31_ID and l.value is not null
    order by l.seq
   

   Antares shows both answers side by side: Nakshatra by longitude, Anuradha; Junction star of, Jyeshtha.

5. Where it is now: a Sky Chart region (Part 5's plugin).

6. On pages 30 and 32, make the Star column a Link to page 31, setting P31_ID to #ID# and clearing page 31's cache.

The sky chart rings one star

The plugin learns one thing. ss_chart.sky_json takes a fourth argument, p_mark_star, and adds that star's position as "mark", even below the horizon. The plugin gets a second custom attribute:

render_region reads it with p_region.attributes.get_varchar2('mark_item') and passes v(that item). The script draws a saffron ring and the star's name, or adds "Aldebaran is below the horizon right now" to the caption. On page 31, set Star to ring to P31_ID. The home page leaves it empty and its chart is unchanged.

A plugin attribute of type Page Item is how one plugin serves many pages: the page says which of its items to read, the plugin doesn't need to know.

Lesson 6.4 — Search everything

Outcome: one search box that finds stars, constellations and nakshatras, each result linking to the right page.

The question. Someone types "rohini". Is that a star (Aldebaran's Vedic name), a nakshatra, or both? Should they have to know which page to search?

The decision. Search Configurations: each one describes one searchable source, what to show for a hit, and where a hit links. A Search Page searches several at once and groups the results.

1. Shared Components › Search Configurations › Create. Name Stars, Search Type Standard (a plain LIKE search; Part 11 adds a vector search). Source SQL Query:

   select id, display_name, proper_name, bayer, vedic_name, constellation, nakshatra,
          'Magnitude ' || to_char(magnitude, 'fm990.00') || ' · ' || colour || ' · ' || constellation as details
     from ss_stars_v
   

   Primary Key ID, Title DISPLAY_NAME, Description DETAILS, Icon static fa-star. Create.

2. Edit it: Subtitle VEDIC_NAME; Link Redirect to Page 31, P31_ID = &ID.; Maximum Results 25.

3. The same for Constellations (icon fa-object-group) and Nakshatras (icon fa-moon-o). Their links go to the Stars page with a facet already set: a facet is a page item, so P30_CONSTELLATION in the URL selects that constellation, and the clear-cache value RP,30 resets the other facets first.

4. Create Page › Search Page: 33, Search, icon fa-binoculars, tick all three configurations.

Search for rohini: under Stars, Aldebaran (its Vedic name is Rohini); under Nakshatras, Rohini, "the red one". Search for orion: the constellation, which opens Stars filtered to Orion's 225 stars; and under Stars, Orion's stars themselves (the first 25), because the constellation is one of the searched columns.

Parts 8 and 9 add Observations and Lore as two more configurations; the Search page shows them with one tick each.

Checkpoint

Every star is now findable four ways: clicking facets, a search bar, a link from anywhere, and one search over the whole sky.

Test it

1. select count(*) from ss_stars_v returns 9,096 in well under a second.
2. Stars: Up now? › Up now, then Brightness › Brightest: the bright stars above you; the counts on every facet change with each click.
3. Find a Star: chips, a range for brightness, and the same results as Stars for the same choices.
4. Click Antares: Facts in a blue-edged card, Tradition in a saffron one (Anuradha by longitude, junction star of Jyeshtha), and a saffron ring on the chart if it's up.
5. Search rohini and orion: grouped results, each linking to the right page.

Files

Exercises

• Add a facet for stars visible tonight (up at any time while it's dark), using dark_begins_at and dark_ends_at from SS_RISE_SET.
• Show the Stars page as Cards instead of a report, with the colour as the card's accent.
• On page 31, list the other stars in the same constellation figure (SS_CONSTELLATION_LINES), each linking to its own page.
• Stretch: make "up now" exact by adding precession to the view, and measure what it costs.

Review questions

Try each one before you open it. The graded quiz is at the end of the page.