Where you are

Every answer SkySutra gives depends on where you stand: which stars are up, when the Sun sets, whether the ISS passes overhead. Part 7 is about places. You let people add their own with the browser's help, put them on a map with the shared observing sites and other people's observations, measure distances with Oracle Spatial, and follow the ISS around the Earth.

How Part 7 works

Before you start

You need Part 6 done. Download skysutra_places.sql and run it in SQL Scripts. It creates two object types, the SS_PLACE package and two views.

Lesson 7.1 — Your location from the browser

Outcome: a form that fills in your latitude, longitude and time zone without you looking them up.

The question. Everyone starts at Wesley Chapel, the default home from Lesson 1.3. How does someone in Pune or Perth get their own sky without typing coordinates?

The decision. Ask the browser, politely, and only when they click. Browsers know where they are (from GPS, Wi-Fi or the network) and will tell a page after asking the person. APEX wraps that in a dynamic action, Get Current Position. The time zone needs no permission at all: Intl.DateTimeFormat().resolvedOptions().timeZone returns the browser's, such as America/New_York or Asia/Kolkata.

Never ask for location when a page loads. A permission prompt nobody asked for gets a "Block", and the browser remembers.

The package: SS_PLACE

ss_place.save inserts or updates one of your places and can make it home:

-- updating: only your own place
update ss_places
   set name = p_name, latitude = round(p_latitude, 5), ...
 where id = l_id and user_id = l_user_id;
if sql%rowcount = 0 then
    raise_application_error(-20201, 'That place isn''t yours to change.');
end if;

The user_id in the WHERE clause is the security. A page item such as P41_ID can be changed by anyone with the browser's developer tools; the database still only updates rows that belong to the person signed in. l_user_id is looked up once at the start, before any update, for the reason Lesson 5.1 found the hard way (ORA-04091).

ss_place.remove won't delete your home. Coordinates are rounded to five decimals, about a metre: more digits would only pretend to be precise.

Build: the Place page

1. Create Page › Blank Page: 41, Place, Page Mode Modal Dialog, no navigation entry.

2. A Static Content region, Place, with items P41_ID (hidden), P41_NAME, P41_LATITUDE and P41_LONGITUDE (Number Field, side by side), P41_ELEVATION_M, P41_TIME_ZONE (Select List), P41_BORTLE (Select List) and P41_MAKE_HOME (Switch). Name, latitude, longitude and time zone are required.

3. P41_TIME_ZONE's list of values comes from the database's own list:

   select distinct tzname d, tzname r
     from v$timezone_names
    where tzname like '%/%' and tzname not like 'Etc/%'
    order by 1
   

   P41_BORTLE is a static list from "1 Excellent dark site" to "9 Inner city".

4. Buttons: Cancel (dynamic action Cancel Dialog), Delete (Submit, shown only when P41_ID has a value, with a confirmation), Use my location (icon fa-location-arrow), Save (Submit, hot).

5. On Use my location, a dynamic action: Click › Get Current Position, Return Type Latitude and Longitude, Latitude Item P41_LATITUDE, Longitude Item P41_LONGITUDE.

6. The page's Execute when Page Loads:

   if (!$v('P41_ID')) {
       $s('P41_TIME_ZONE', Intl.DateTimeFormat().resolvedOptions().timeZone);
   }
   

7. Processes: Load the place (Before Header: a select … into :P41_… that also checks user_id), Save the place (:P41_ID := ss_place.save(...), when Save), Delete the place (ss_place.remove(:P41_ID), when Delete), and Close Dialog for both requests.

The sky, in the background

A new place has no sunset times and no ISS passes yet. Working them out (ss_place.refresh_sky: a week of rise and set times, three days of passes) took 17 seconds on our run. Nobody should stare at a spinner for that.

An Execution Chain with Run in Background on runs its child processes as a database job after the page has answered:

1. A process Work out its sky, Type Execution Chain, Run in Background on, Context Value Item P41_ID, when Save, after Save the place (so P41_ID has the new id).
2. A child process in that chain: ss_place.refresh_sky(:P41_ID);

The dialog closes at once with "Place saved. Its sky is being worked out and will be ready in a minute." The chain sees a copy of the session state, including P41_ID. APEX_APPL_PAGE_BG_PROC_STATUS shows each run: queued, executing, succeeded or failed.

Lesson 7.2 — The Map region

Outcome: one map with your places, the shared observing sites and observations, each with its own symbol and a pop-up.

The question. A list of places with latitudes is accurate and useless. What does someone want to see? (Where the dark sites are compared with home, and where people have been observing.)

The decision. A Map region with three layers, each its own query, colour and shape. Layers can be switched on and off in the map's legend.

SS_MAP_PLACES_V returns the signed-in person's places and the shared sites (those with no user_id); SS_MAP_OBSERVATIONS_V returns public observations and your own. Both include GEOM, the place's point, and KM_FROM_HOME.

Build: the Places page

1. Create Page › Map: 40, Places, icon fa-map-marker, SQL Query source:

   select id, name, geom,
          kind || nvl2(bortle, ' · Bortle ' || bortle, null)
               || case when kind <> 'Home' then ' · ' || km_from_home || ' km from home' end as details
     from ss_map_places_v
    where is_mine = 'Y'
   

   Geometry Column Data Type SDO_GEOMETRY, Geometry Column GEOM, tooltip NAME. No faceted search.

2. Rename the layer My places: Primary Key ID, Point Objects › Shape Home, Fill Color #7FB2FF, Info Window title NAME, body DETAILS.

3. Add the layers Observing sites (Flag Pennant, #F2A33A) and Observations (Circle, #F5F3EA, Clustering on). Clustering groups nearby points into one numbered circle until you zoom in: with a thousand observations around Tampa, that's the difference between a map and a smear.

4. The map's Height 480. Initial Position and Zoom Type Query Results fits the map around every point.

5. Below the map, a Classic Report My places with the name linking to page 41 (P41_ID = #ID#) and a button Add a place (to page 41 with nothing set).

6. Page 41 is a dialog, so page 40 doesn't know it closed. A dynamic action on page 40: event Dialog Closed, Selection Type JavaScript Expression document, action Submit Page. The map and reports reload with the change.

Lesson 7.3 — Spatial queries

Outcome: distances between places measured on the Earth, not on a flat grid.

The question. Hillsborough River State Park is 0.093° north and 0.093° east of Wesley Chapel. How far is that? (Not 0.13° of anything: a degree of longitude shrinks towards the poles. At 28° north it's 98 km, not 111.)

The decision. Let Oracle Spatial do it. Since Part 1, a trigger on SS_PLACES keeps a point geometry in GEOM:

:new.geom := sdo_geometry(2001, 4326, sdo_point_type(:new.longitude, :new.latitude, null), null, null);

2001 means "a two-dimensional point" and 4326 is WGS 84, the coordinate system GPS uses. Given that, SDO_GEOM.SDO_DISTANCE measures along the Earth's surface:

round(sdo_geom.sdo_distance(p.geom, h.geom, 0.005, 'unit=KM'), 1) as km_from_home

(0.005 is the tolerance, in metres for geodetic data.) From Wesley Chapel: Hillsborough River State Park 13.7 km, Withlacoochee State Forest 39.6 km, Fort De Soto Park 79.5 km, Kissimmee Prairie Preserve 146.1 km. A haversine by hand agrees to the tenth.

Two reports

Darkest sites near home: what someone asks before a clear night.

select name, bortle, km_from_home
  from ss_map_places_v
 where is_mine = 'N' and bortle <= 4
 order by km_from_home

Observations within 50 km: what other people saw nearby.

select target, observer, place, to_char(observed_at, 'DD Mon YYYY') as observed, km_from_home
  from ss_map_observations_v
 where km_from_home <= 50
 order by km_from_home, observed_at desc

When you need a spatial index

These queries compute the distance to every row. With a few hundred places that takes milliseconds. With a hundred thousand observations, "within 50 km" should use a spatial index and SDO_WITHIN_DISTANCE, which uses the index to skip everything far away:

insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid)
values ('SS_PLACES', 'GEOM',
        sdo_dim_array(sdo_dim_element('Longitude', -180, 180, 0.5),
                      sdo_dim_element('Latitude',   -90,  90, 0.5)), 4326);

create index ss_places_geom_six on ss_places (geom) indextype is mdsys.spatial_index_v2;

-- then
select o.*
  from ss_map_observations_v o
 where sdo_within_distance(o.geom, :home_geom, 'distance=50 unit=KM') = 'TRUE';

On apex.oracle.com, SQL Commands runs as APEX_PUBLIC_USER, which can't create the index (ORA-01031). Run those two statements from SQL Developer Web (Database Actions), which connects as your schema itself. Lesson 1.6 decided SkySutra doesn't need the index yet; this is how you add it the day it does, and the map layer's Use Spatial Index switch then lets APEX use it too.

Lesson 7.4 — Where's the ISS?

Outcome: the ISS on the map now, its path for the next orbit, and its passes over your home.

The question. Part 3's SS_ISS knows where the ISS is at any moment. How does a map layer, which wants a query, show a path that is calculated, not stored?

The decision. A pipelined table function: PL/SQL that returns rows one at a time, which SQL reads with table(...) as if it were a table.

create type if not exists ss_track_point as object (
    at_utc timestamp, latitude number, longitude number, height_km number);
create type if not exists ss_track as table of ss_track_point;

function iss_track (p_from timestamp with time zone default systimestamp,
                    p_minutes number default 93,
                    p_step_minutes number default 1) return ss_track pipelined is
begin
    for i in 0 .. trunc(p_minutes / p_step_minutes) loop
        l_at := p_from + numtodsinterval(i * p_step_minutes, 'MINUTE');
        ss_iss.subpoint(l_at, l_lat, l_lon, l_h);
        pipe row (ss_track_point(sys_extract_utc(l_at), round(l_lat, 3), round(l_lon, 3), round(l_h)));
    end loop;
    return;
end;

The types are made with create type if not exists, not create or replace. Once SS_PLACE uses them, Oracle refuses to replace them (ORA-02303), and the script would stop the second time you ran it.

select * from table(ss_place.iss_track(timestamp '2026-10-25 00:30:00 UTC', 10, 5));

About 800 km of ground every two minutes, and over the date line between the first two rows. That's why the path is drawn as dots, not a line: a line from 175.9° east to 169.3° west would be drawn the long way round, across the whole map.

Build: the ISS page

1. Create Page › Map: 42, ISS, icon fa-space-shuttle, layer Its path for the next orbit:

   select to_char(from_tz(t.at_utc, 'UTC') at time zone h.time_zone, 'HH24:MI') as at_time,
          round(t.height_km) || ' km up' as height,
          sdo_geometry(2001, 4326, sdo_point_type(t.longitude, t.latitude, null), null, null) as geom
     from table(ss_place.iss_track(systimestamp, 92, 2)) t
    cross join (select p.time_zone from ss_places p join ss_users u on u.home_place_id = p.id
                 where u.id = ss_auth.current_user_id) h
   

   Small blue circles (Shape Scale 0.5), tooltip AT_TIME: hover a dot to see when the ISS is there, in your time.

2. A layer The ISS now (iss_track(systimestamp, 0, 1): one point), saffron pin; and Your home, a home shape.

3. A Classic Report Passes over your home from SS_ISS_PASSES: start (local time), how long, highest altitude, from which compass point to which, and whether you can see it ("Yes: sunlit, in a dark sky" or "No: daylight or Earth's shadow"). Lesson 7.1's background chain recomputes these whenever a place is saved; Part 10's nightly job will keep them fresh.

Checkpoint

SkySutra now knows where you are, lets you add places in two clicks, measures distances on the Earth, and shows the ISS going round.

Test it

1. Places: three layers, each with its own symbol; your home, four observing sites, and two public observations around Tampa. Click a flag for its distance from home.
2. Add a place › Use my location: the browser asks, then fills in latitude and longitude; the time zone is already yours. Save: the dialog closes, the map shows the new place, and within a minute SS_RISE_SET has rows for it.
3. Try deleting your home: "You can delete your own places, but not your home."
4. Darkest sites near home lists Hillsborough River State Park (13.7 km) and Withlacoochee State Forest (39.6 km), but not Fort De Soto (Bortle 5).
5. ISS: 47 dots around the world, a saffron pin where it is now, and the next three days of passes.

Files

Exercises

• On the Place page, add a small Map region that shows the latitude and longitude as you type them, so people can check the pin before saving.
• Show the ISS's footprint: the circle on the ground from which it's above the horizon (about 2,200 km across at 420 km up).
• Make the Observations layer's colour depend on how long ago the observation was (a colour scheme on a numeric column).
• Stretch: in SQL Developer Web, create the spatial index and rewrite "Observations within 50 km" with SDO_WITHIN_DISTANCE. Compare the plans.

Review questions

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