If you’re in North America, it’s safe to say you’re aware that we’re having a solar eclipse next month. Maybe you’re even lucky enough to live close enough to the path of totality to view it. But how to do you decide where to go? Let’s create an APEX application that will help us answer a few questions. 1. Where exactly is the totality path? 2. What cities are in the path with a population high enough that you will have access to a few amenities like a lunch spot or a public bathroom. 3. Maybe more importantly, what’s the weather going to be like? Let’s get started. 

Accounts creation and dataset download

Let’s get our prep work out of the way. The first thing we will need is an OCI account. If you don’t already have one, go here for instructions. Then we’ll need some datasets. Download those here. The last thing we’re going to need is an account from OpenWeatherMap.org. This will allow us to import the weather information for our cities we’re interested in visiting to make sure the cloud coverage won’t be an issue. 

Create your APEX Instance

These instructions are adapted from Get Started with Maps and Spatial in APEX. If you’re interested in learning more about these features, this is a great resource. 

1. From within your Oracle Cloud environment, you will create an instance of the Oracle APEX Application Development Service.
   Click the Navigation Menu in the upper left, navigate to Developer Services, and select APEX Instances.
2. Select a Compartment and Click Create APEX Service.

3. Enable the Always Free option. Select 19c for the database version. Enter a password for the ADMIN password then click Create APEX Service.

4. After clicking Create APEX Service, you will be redirected to the APEX Application Development Details page for the new instance.
5. Within your new APEX Application Development Service, APEX Workspace is not yet configured. Therefore, when you first access APEX, you will need to log in as an APEX Instance Administrator to create a workspace. Click Launch APEX
   Note: APEX Application Development Service is a paid service. You will be able to use APEX Service trial only if you have a new Free Cloud account or you are within the trial period. To Stop the APEX Service from consuming your trial credit, you need to stop the Autonomous Database behind it. Click on the Database name under APEX Instance Information tab in the APEX Instance Details page: Click More Actions and then Stop
6. Enter the password for the Administration Services and click Sign In to Administration.
   The password is the same as the one you entered for the ADMIN user when creating the APEX instance.
7. Click Create Workspace.

Load and Configure Your Data

1. In the Create Workspace dialog, enter the following:
   

   Property	Value
   Workspace Name	eclipse
   Database user	eclipse
   Password	<your password>

   Click Create Workspace.

   • In the APEX Instance Administration page, click the ECLIPSE link in the success message.
     Note: This will log you out of APEX Administration so that you can log into your new workspace.
   • On the APEX Workspace log in page, enter the password, check the Remember workspace and username checkbox, and then click Sign In.
2. Once logged into APEX you need to upload the data we need for this application
3. Click on SQL Workshop -> Utilities -> Data Workshop -> Load Data
   • Upload the files one at a time and change their table names
     

     File Name	Table Name
     geonames-usa-cities.geojson	US_CITIES
     umbra_lo.geojson	UMBRA
     upath_lo.geojson	UPATH
     center.geojson	CENTER

   • Click Load Data after uploading each file
   • Use X button in top right of screen to return to data loading page. Once all 4 files are uploaded refresh page to verify all four tables are loaded
4. Click SQL Workshop in header
5. Click SQL Commands
6. Add a column called sdo_geometry to each table alter table umbra add (geom sdo_geometry) ; alter table upath add (geom sdo_geometry) ; alter table center add (geom sdo_geometry) ; alter table us_cities add (geom sdo_geometry) ;
   • Explanation: Geometry is stored as JSON object. We need to use a utility to turn it into a SDO geometry
     Note: If you did not include the table names when uploading the datasets your table names will be different here. Change your sql statements to your table names.
7. Add spatial metadata entries for the newly created GEOM columns
   • For UPATH table

     begin
     apex_spatial.insert_geom_metadata (
                p_table_name => 'UPATH',
                p_column_name => 'GEOM',
                p_diminfo => SDO_DIM_ARRAY (
                         SDO_DIM_ELEMENT('X', -180, 180, 0.5),
                         SDO_DIM_ELEMENT('Y', -90, 90, 0.5) ),
                p_srid => apex_spatial.c_wgs_84 );
     end;
     /

   • For UMBRA table

     begin 
     apex_spatial.insert_geom_metadata ( 
          p_table_name => 'UMBRA', 
          p_column_name => 'GEOM', 
          p_diminfo => SDO_DIM_ARRAY ( 
                 SDO_DIM_ELEMENT('X', -180, 180, 0.5), 
                 SDO_DIM_ELEMENT('Y', -90, 90, 0.5) ), 
          p_srid => apex_spatial.c_wgs_84 ); 
     end; 
     /

   • For CENTER table

     begin
     apex_spatial.insert_geom_metadata (
                p_table_name => 'CENTER',
                p_column_name => 'GEOM',
                p_diminfo => SDO_DIM_ARRAY (
                         SDO_DIM_ELEMENT('X', -180, 180, 0.5),
                         SDO_DIM_ELEMENT('Y', -90, 90, 0.5) ),
                p_srid => apex_spatial.c_wgs_84 );
     end;
     /

   • For US_CITIES table

     begin 
     apex_spatial.insert_geom_metadata (
                p_table_name => 'US_CITIES',
                p_column_name => 'GEOM',
                p_diminfo => SDO_DIM_ARRAY (
                         SDO_DIM_ELEMENT('X', -180, 180, 0.5),
                         SDO_DIM_ELEMENT('Y', -90, 90, 0.5) ),
                p_srid => apex_spatial.c_wgs_84 );
     end;
     /

• Populate the geom columns from the geojson text in the GEOMETRY column of each table

  update center set geom = sdo_util.from_geojson(geometry) ;
  update umbra set geom = sdo_util.from_geojson(geometry) ;
  update us_cities set geom = sdo_util.from_geojson(geometry) ;
  update upath set geom = sdo_util.from_geojson(geometry) ;

• Verify that they were created

  select id, sdo_util.getnumvertices(geom) as Num_Points from upath ; 
  select id, sdo_util.getnumvertices(geom) as Num_Points from center ; 
  select id, sdo_util.getnumvertices(geom) as Num_Points from umbra ; 
  select id, sdo_util.getnumvertices(geom) as Num_Points from us_cities ;

• A Long/Lat point is stored in a attribute of sdo_geometry called sdo_point. We use that to get the longitude and latitude in subsequent queries

  select id, name, c.geom.sdo_point.y as Latitude, c.geom.sdo_point.x as Longitude 
  from us_cities c fetch first 10 rows only ;

• Now let’s find cities with a population > 10,000 that are within 10 miles of the center line representing the path of the total eclipse over the course of the day.

  select c.name, c.admin1_code State , c.geom.sdo_point.x Longitude, c.geom.sdo_point.y Latitude 
  from us_cities c, center l 
  where c.population > 10000 and 
  sdo_within_distance(c.geom, l.geom, 'unit=mile distance=10') = 'TRUE';

• Let’s find the ones in NH ME or VT and their distance from the center

  select c.name City, c.admin1_code State, 
  sdo_geom.sdo_distance(c.geom, l.geom, 0.5, 'unit=mile') Dist_From_Center_In_Miles,  
  c.geom.sdo_point.x Longitude, c.geom.sdo_point.y Latitude from us_cities c, center l 
  where c.population > 5000 and c.admin1_code in ('NH', 'ME', 'VT') and 
  sdo_within_distance(c.geom, l.geom, 'unit=mile distance=10') = 'TRUE';

• Change the dist to 25 miles and the population to > 5000 to get some more options select c.name City, c.admin1_code State,

  sdo_geom.sdo_distance(c.geom, l.geom, 0.5, 'unit=mile') Dist_From_Center_In_Miles,  
  c.geom.sdo_point.x Longitude, c.geom.sdo_point.y Latitude from us_cities c, center l 
  where c.population > 5000 and c.admin1_code in ('NH', 'ME', 'VT') and 
  sdo_within_distance(c.geom, l.geom, 'unit=mile distance=25') = 'TRUE';

• This last query can be used with a Map Region to display the cities on a map in APEX. It simply adds the GEOM column of the cities to the query.

  select c.name City, c.admin1_code State,  c.geom Geom,
  sdo_geom.sdo_distance(c.geom, l.geom, 0.5, 'unit=mile') Dist_From_Center_In_Miles,  
   c.geom.sdo_point.x Longitude, c.geom.sdo_point.y Latitude from us_cities c,  center l 
   where c.population > 5000 and c.admin1_code in ('NH', 'ME', 'VT') and 
   sdo_within_distance(c.geom, l.geom, 'unit=mile distance=25') = 'TRUE';

Create the webpage

Okay, now that we’ve identified some queries to use, let’s create a webpage so it’s easier to explore our datasets. The last query we wrote will give us the cities in New Hampshire, Maine, and Vermont within 25 miles of the center line with a population greater than 5000. If you’re feeling adventurous, you can change the states or the populuation to see how the results change. We’ll use this query to populate our page.

1. Click the App Builder to go back to the app builder home screen and then click on your application
2. Click Home to open the Page Designer
3. In the left side section, create region under the body
4. In the right side menu, change the 
   • Title to “Cities Nearby”
   • Type to Interactive Report
   • Location to Local Database
   • Type to SQL Query
   • Paste the last query in the SQL Query field box

     select c.name City, c.admin1_code State, c.geom GEOM,
     sdo_geom.sdo_distance(c.geom, l.geom, 0.5, 'unit=mile') Dist_From_Center_In_Miles,  
     c.geom.sdo_point.x Longitude, c.geom.sdo_point.y Latitude from us_cities c, center l 
     where c.population > 5000 and c.admin1_code in ('NH', 'ME', 'VT') and 
     sdo_within_distance(c.geom, l.geom, 'unit=mile distance=25') = 'TRUE';

   • Click run to view the resulting page
   • Sign in with the credentials you created above
5. Return to App Builder and click the breadcrumbs to return to the home screen (likely Application 101).

REST Data Sources

Let’s add a REST Data Source using the OpenWeatherMap account we created earlier.

1. Click Share Components the REST Data Sources
2. Click Create
3. Select From scratch then click Next
4. Fill in: 
   • Name: Weather app
   • URL Endpoint
5. Return to the OpenWeatherMap account. Click on API and then scroll down to 5 Day / 3 Hour Forecast. Click API doc. Copy the API call.
6. Remove ?lat={lat}&lon={lon}&appid={API key} 
   and append with https://
   so your URL endpoint is
   https://api.openweathermap.org/data/2.5/forcast
7. Click Next
8. On the Remote Server page, click Next
9. On the pagination page, click Next
10. On the Authentication page, change:
    • Authentication type: URL Query String
    • Name: appid
    • Value: Return to the OpenWeatherApp page. Click the API keys in the user dropdown menu and copy your key
11. Click Advanced
12. In Parameters, create the following:
    

    Parameter Type	Parameter Name	Value	Is Static
    URL Query String	lat	44.55	Off
    URL Query String	lon	-73.13	Off
    URL Query String	cnt	15	On
    URL Query String	units	imperial	On

    Click Discover and review your results

13. Click Create REST Data Source

    

Configure the REST Data Source Profile

1. Click Data Profile then Edit Data Profile
2. Click edit to edit the column settings
3. Make only DT_TXT, Temp, Temp_Max, Temp_Min, Feels_like, and All_ visible. Change DT_TXT’s data type to VARCAR2. Apply changes and go back to Application 101.

Create Page Items

1. Right click on Cities Nearby and select “Create Page item” twice, giving you two page items under Region Body
2. For the first page item, change the following settings
   • Name: P1_LAT
   • Label: Latitude
   • Toggle on “submit when enter pressed”
   • Default type: static
   • Static value: 44.55
3. For the first page item, change the following settings
   • Name: P1_LON
   • Label: Longitude
   • Toggle on “submit when enter pressed”
   • Default type: static
   • Static value: -73.17

Create Weather Region

1. Right click on Body and select Create Region
2. In the right side menu, change the: 
   • Title: Weather
   • Type: Classic report
   • Source: REST Source
   • REST Source: <name of your weather REST source>
3. Expand Parameters
4. Click lat.  
   • Under Value change the type to item and select P1_lat as the item
5. Click lon
   • Under Value change the type to item and select P1_lon as the item
6. Expand Columns
   • Click DT_TXT. Change heading to “Forecast Date”
   • Click Temp. Change heading to “Current Temp (F)”
   • Click Temp_MAX. Change heading to “Temp Mac (F)”
   • Click Temp_MIN. Change heading to “Temp Min (F)”
   • Click Feels_Like. Change heading to “Feels like (F)”
   • Click All_. Change heading to “Cloud Cover (%)”
7. Save these changes and Run
8. Select which city you would choose to have your watch party in and tell us why!

Bonus

Create a second page to visualize a map showing the path of the eclipse and denote where the cities are near the path of totality. 

1. Return to Application 101 and click Create page.
2. Select Map
3. Under Create Map, Change Table / View Name to CENTER. Click Next
4. Under Map Attributes, change Geometry Column to GEOM(SDO_Geometry). Change Tooltip Column to NAME.
5. Click Create page
6. In the Page designer, right click on Layers, and click New Layer
   • Name: Swath
   • Layer Type: Polygons
   • Table Name: UPATH
   • Geometry Column: GEOM
   • Primary Key Column: ID
   • Fill Color: pick a light gray
   • Fill Opacity: 0.3
   • Stroke color: pick a light gray
7. Save and view your new map
8. Return to Page Designer to add the nearby cities
9. Create a new Layer
   • Name: pop>5000
   • Type: SQL Query
   • SQL Query: 

     select c.name City, c.admin1_code State, c.geom Geom, 
     sdo_geom.sdo_distance(c.geom, l.geom, 0.5, 'unit=mile') Dist_From_Center_In_Miles,  
     c.geom.sdo_point.x Longitude, c.geom.sdo_point.y Latitude from us_cities c, center l 
     where c.population > 5000 and c.admin1_code in ('OH', 'PA', 'NY', 'VT') and 
     sdo_within_distance(c.geom, l.geom, 'unit=mile distance=25') = 'TRUE';
     

   • Column Mapping-> Geometry Column: GEOM
   • Primary Key Column: City
10. Save and view the map and see if you would choose a different city!