Geocoding Addresses

Geocoding an address identifies its geographic coordinates.

The Geocoded Address page item type lets you do geocoding in any page in the browser. If your application runs on Autonomous Database, you can also do server-side geocoding using a dedicated page process or workflow activity.

• Geocoding with User Confirmation
  Use a Geocoded Address page item to show a location and get coordinates for a user-entered address.
• Geocoding in the Background
  

Use a Geocoded Address page item to show a location and get coordinates for a user-entered address.

It appears as a map on the page, and works with one or more address-related text items for data entry. If the user changes the address, they must confirm it from a list of likely matches when they submit the page. Under the covers, the address they select includes the latitude and longitude coordinates. The Geocoded Address item automatically matches addresses and coordinates using an Oracle-managed service.

• Producing GeoJSON from Coordinates
  The Geocoded Address page item works with GeoJSON, the web standard format for mapping information.
• Avoiding SQL Expression Update Error
  When you include a SQL Expression column like GEO_JSON_POINT, remember to also enable the Query Only switch on it in the Property Editor.
• Identifying Address Text Fields
  Associate a Geocoded Address page item with one or more related fields that display the address as text.
• Confirming an Address Interactively
  Follow the user experience for entering or editing a geocoded address.
• Extracting Coordinates for Storage
  To extract the geocoded address's longitude and latitude into page items, define a dynamic action event handler on the Result Selection [Geocoded Address] event.
• Unprotecting Client-Set Hidden Items
  Disable Value Protected when client-side logic sets a hidden page item's value.
• Exploring Additional Page Features
  Explore several page techniques used by the Geocoded Address example.

• Selecting Items for Server-side Geocoding
  
• Referencing Coordinates Item Values
  
• Processing Collection of Matches
  
• Configuring Match Mode
  When doing server-side geocoding with a structure address, your choice of match mode influences how strict to be.

The Geocoded Address page item works with GeoJSON, the web standard format for mapping information.

If you store address coordinates in an SDO_GEOMETRY column, or as GeoJSON in a VARCHAR2 or CLOB, your Geocoded Address item can read and write address coordinates directly. Sometimes, however, you store LONGITUDE and LATITUDE in separate number columns.

create or replace view emp_and_address as
select   empno,
       e.ename,
       a.address,
       a.latitude,
       a.longitude
  from emp e
  join emp_addresses a
  using (empno);

{"type":"Point","coordinates":[longitude,latitude]}

Starting with a Form region based on the EMP_AND_ADDRESS view, you can switch it to SQL Query type instead. Then, add the SQL Expression below as one additional SELECT list column. After doing this, your Geocoded Address page item can reference the GEO_JSON_POINT column. If the latitude and longitude values are both provided, then the expression returns the GeoJSON document representing a point with those coordinates. Otherwise, the CASE statement returns null.

select EMPNO,
       ENAME,
       ADDRESS,
       LATITUDE,
       LONGITUDE,
       case
         when longitude is not null and latitude is not null then
           json_object(
             'type'        value 'Point',
             'coordinates' value json_array(longitude, latitude))
       end as geo_json_point
  from EMP_AND_ADDRESS

After adding this column to the Form region's data source query, it will appear in the region as a P60_GEO_JSON_POINT page item whose type you can set to Geocoded Address.

sdo_util.to_geojson(make_point(longitude, latitude)) as geo_json_point

–- NOTE: This is not locale-safe
apex_string.format(
   '{"type":"Point","coordinates":[%s,%s]}',
   longitude,latitude)

When you include a SQL Expression column like GEO_JSON_POINT, remember to also enable the Query Only switch on it in the Property Editor.

ORA-01733: virtual column not allowed here

that results when the user submits a form page that attempts to update a computed column.

Associate a Geocoded Address page item with one or more related fields that display the address as text.

3130 Pacific Ave, San Francisco, CA

Alternatively, use separate page items to handle each part of a structured address. By default, the Trigger Geocoding is set to Automatic. This means the item preempts the page submit to get user confirmation of a modified address. The Oracle-managed geolocation service requires the context of the country name in which to locate the address. You can select a static country value from a list, or configure a Country page item to let the end user decide.

Follow the user experience for entering or editing a geocoded address.

Assume employee KING has moved recently, and a user needs to change her address. As shown below, your page can pair a Cards region on the EMP_WITH_ADDRESS view with a map region showing all their addresses using the technique explained in Using Filtered Data Primary Keys.The map tooltip uses the HTML expression below to include the address information if available.

<strong>&ENAME.</strong>{if ADDRESS/}<br>&ADDRESS.{endif/}

The figure shows a cards region on the left half of the page displaying employees ENAME on small tiles. On the right side a map displays employee's addresses as map markers. The user's mouse hovers over the map marker for employee KING, to see that she currently lives at 3130 Pacific Avenue, San Francisco, California.

You create a Full Card action to open the modal drawer page shown below, passing in the value of the current employee's EMPNO as the primary key page item P60_EMPNO. So, as shown below, clicking on KING's card opens the modal drawer dialog. The P60_ADDRESS text field display the unstructured address. The Geocoded Address item P60_GEO_JSON_POINT shows the address coordinates your GeoJSON SQL expression produced for the point using the LONGITUDE and LATITUDE values in KING's row in the EMP_AND_ADDRESS view.

The user enters a new address and clicks the (Apply Changes) button to save. The Automatic setting of Trigger Geocoding on the P60_GEO_JSON_POINT Geocoded Address item causes it to preempt the page submit and validate the changed address. As shown below, matching addresses appear in a modal Geocoding Results dialog.

Once the user confirms the address, as shown below, she can click (Apply Changes) again to save the change.

To extract the geocoded address's longitude and latitude into page items, define a dynamic action event handler on the Result Selection [Geocoded Address] event.

An Execute JavaScript Code dynamic action step with the following two lines handles the task. The first line uses the shortcut syntax to set page item P60_LONGITUDE to the result of evaluating the this.data.longitude expression. The second line shows the explicit syntax for setting the value of the P60_LATITUDE to the equivalent expression for that coordinate value.

Both assignment approaches accomplish the same result. $s('item',value) is less to type, but offers no code completion. Using the explicit syntax, as you type each dot in the expression, the Code Editor lets you pick from a list of available choices. Just remember to include the .value at the end of the expression after the page item name.

$s('P60_LONGITUDE', this.data.longitude);
apex.items.P60_LATITUDE.value = this.data.latitude;

The figure shows the Set Longitude and Latitude dynamic action step in Page Designer that fires when the user selects a result in the Geocoded Address item's confirmation dialog.

The this.data object available to a Result Selection event's actions also contains other properties. The JSON below shows all the names your this.data.propName expressions can reference.

{
  "sequence": 0,
  "latitude": 37.77112,
  "longitude": -122.45304,
  "matchCode": 1,
  "matchVector": "??010101010??004?",
  "matchVectorScore": 100,
  "houseNumber": "2045",
  "street": "Oak St",
  "settlement": "San Francisco",
  "municipality": "SAN FRANCISCO",
  "region": "CA",
  "country": "US",
  "language": "ENG",
  "postalCode": "94117",
  "side": "R",
  "percent": 0.24,
  "edgeId": 120887751
}

Disable Value Protected when client-side logic sets a hidden page item's value.

Session state protection violation:
This may be caused by manual alteration of protected page item P60_LATITUDE.

Explore several page techniques used by the Geocoded Address example.

• Clicking Map Point to Open Edit Dialog
  Configure a drill down on a map layer item by setting up its link target.
• Refreshing Region on Dialog Close
  Use a dynamic action event handler to refresh a calling page's region when the user closes an edit dialog.
• Stretching to Fill Vertical Space
  The 12-column grid system simplifies filling the available horizontal space. However, sometimes you want a component to stretch vertically to use available height.
• Preventing Enter Key from Submitting
  Prevent the Enter key from submitting a single-field dialog by adding a visually hidden text field.
• Triggering Geocoding with Enter Key
  Use a native Trigger Geocoding dynamic action step to do address confirmation on-demand.
• Including Context Info in Dialog Title
  Use JavaScript to set a modal dialog's title to include context information from the calling page.
• Providing App-Wide Helper Functions
  Put JavaScript helper functions used on multiple pages in an application-wide library.
• Using Translatable Text in JavaScript
  Consider using a translatable Text Message when setting a dynamic dialog title from JavaScript.

If your Coordinates Item is named COORDINATES, then you can access the longitude and latitude values in the following ways.

declare
   l_coords    sdo_geometry := sdo_util.from_geojson(:COORDINATES);
   l_longitude number       := l_coords.sdo_point.x;
   l_latitude  number       := l_coords.sdo_point.y;
begin
   -- Do something with l_longitude & l_latitude here
end;

for j in (select coords.sdo_point.x as longitude,
                 coords.sdo_point.y as latitude
            from (
               select sdo_util.from_geojson(:COORDINATES) as coords
                 from dual))
loop
   -- Do something with j.longitude & j.latitude here
end loop;

declare
   l_longitude number;
   l_latitude  number;
begin
   l_longitude := json_value(:COORDINATES,'$.coordinates[0]');
   l_latitude  := json_value(:COORDINATES,'$.coordinates[1]');
   -- Do something with l_longitude & l_latitude here
end;

for j in (select longitude, latitude
            from json_table(:COORDINATES,
                    '$.coordinates'
                    columns (
                       longitude number path '$[0]',
                       latitude  number path '$[1]')))
loop
   -- Do something with j.longitude & j.latitude here
end loop;

When doing server-side geocoding with a structure address, your choice of match mode influences how strict to be.

As shown below, you configure it in the Shared Components > Component Settings page for the Server Side Geocoding component. This setting has no effect when geocoding an unstructured address.

Configure a drill down on a map layer item by setting up its link target.

In the example shown below, notice the map region shows employee addresses on the calling page. While exploring the map by hovering over its points, a user might click a point to edit that employee's address. Rather than expecting them to click on a card, you can anticipate this user instinct to improve their experience.

As shown below, after selecting the Addresses map layer, you can directly configure the Link > Target using the Link Builder dialog you're familiar with for configuring buttons and links. By choosing page 60 and configuring the value of P60_EMPNO to come from the EMPNO column, you let the user open the modal Employee Address edit dialog using whichever way feels right to them.

Use a dynamic action event handler to refresh a calling page's region when the user closes an edit dialog.

The user clicks a card or a map point. Page 60 opens as a modal drawer to edit that employee's address. When the user cancels the dialog by clicking the "x" icon or pressing the [Esc] key, the address remains unchanged. However, if they close the dialog by clicking the (Apply Changes) button, they might have updated the employee's address.

End users expect their change to immediately reflect on the map in the calling page. Just use a Refresh action for the map region in a Dialog Closed event handler. This ensures that if you update KING's address again, as shown below, the map instantly shows her new location.

To define a Dynamic Action event handler on the Dialog Closed event, select the Dynamic Actions tab in Page Designer, right-click on the Dialog Closed event heading in the tree, and choose Create Dynamic Action.

In the When section of the Property Editor, specify which dialog's closing should trigger this action using Selection Type. For example, you can target a dialog opened by a particular button, by a component in a particular region, or by a particular element using a jQuery Selector.

To react to the closing of a dialog opened by any element in the page, target the page's <body> element that contains all other elements. As shown below, choose Selection Type of jQuery Selector with a Selector of body to do this. This ensures the map refresh occurs whether the user opened the dialog by clicking on a card or a map point.

The 12-column grid system simplifies filling the available horizontal space. However, sometimes you want a component to stretch vertically to use available height.

• Stretching Vertically Using CSS
  In Cascading Style Sheets (CSS), the viewport is the visible browser area. One vertical stretching strategy sizes a component to the remaining vertical space after accounting for other page elements.
• Inspecting Potential Stretch Targets
  Vertical stretching requires inspecting the web page to determine an appropriate container element to target in your CSS rules.
• Calculating Height in Inline CSS
  CSS rules can use calculated expressions.
• Applying Vertical Stretch in a Dialog
  Use CSS to stretch a component vertically in a modal dialog.

Prevent the Enter key from submitting a single-field dialog by adding a visually hidden text field.

When a user presses the [Enter] key in a web page, for historical reasons, all browsers submit the page when it contains just a single input field. In contrast, if the page has two or more input fields, then the [Enter] key does not do this.

The Employee Address modal drawer is a "single input field" page. Technically, its only input field is P60_ADDRESS since the Geocoded Address page item P60_GEO_JSON_POINT renders as a map. In this situation, the simplest solution to avoid [Enter]'s submitting the page is to add a second input field. By hiding this second field as described, the end user does not even know it's there.

When adding this hidden field, give it a name that clarifies its special purpose. As shown below, leave its page item type as Text Field instead of Hidden. Then add the Universal Theme CSS class u‑hidden to its Advanced > CSS Classes property to hide it instead. Notice the label is blank and the Appearance > Template is set to Hidden as well. Despite its not being visible to the user, the browser counts this second field which avoids the [Enter] key from submitting the page.

Use a native Trigger Geocoding dynamic action step to do address confirmation on-demand.

this.browserEvent.key === "Enter"

In the Client-side Condition of the Trigger Geocoding action step, use this JavaScript expression to ensure it only happens if the user has changed the address before pressing [Enter].

apex.items.P60_ADDRESS.isChanged()

With these configurations in place, now the user gets immediate geocoding feedback if they press this [Enter]. When they later click (Apply Changes), the Automatic geocode trigger is smart enough to avoid repeating the process if it was already done on-demand.

Use JavaScript to set a modal dialog's title to include context information from the calling page.

As shown below, when opening a modal dialog, it can be useful to include some context information in the dialog title like the name of the employee whose address the user is editing.

The Form region in the modal drawer page 60 includes the P60_ENAME page item, and the Form Initialization page process automatically retrieves the EMP_AND_ADDRESS view row based on the P60_EMPNO primary key value you pass in. So you already have the ENAME value on hand. Your instinctive first attempt shown below is to include &P60_ENAME. in the Title.

However, when you run the page, the &P60_ENAME. comes up puzzlingly empty as shown below.

// JavaScript in Page Load event action to set dialog title
apex.util.getTopApex()
         .jQuery(".ui-dialog-content")
         .dialog("option", "title",
                 "Address for Employee " + $v('P60_ENAME'));

Put JavaScript helper functions used on multiple pages in an application-wide library.

• Adding Static Application JavaScript File
  Use a Static Application File shared component to include files of any kind with your app.
• Including Helper Functions in All Pages
  Once you add JavaScript file to Static Application Files, cite the script's reference URL in application settings to load it on all pages.

Consider using a translatable Text Message when setting a dynamic dialog title from JavaScript.

app.setDialogTitle('Address for Employee ' + $v('P60_ENAME'));

However, if your application may ever need to be translated, rather than hard-coding the dialog title text, use a shared component Text Message instead. For the dialog title in this example, navigate to Shared Components > Text Messages to create a translatable text message as shown below with name ADDRESS_FOR_EMPLOYEE. Notice its corresponding Text for the English (en) language is the following. It includes the numbered parameter %0 as a placeholder.

Address for Employee %0

Importantly, you enable the Used in JavaScript switch since your call to set the dynamic dialog title happens in JavaScript. The figure shows the Create/Edit Text Message dialog for the ADDRESS_FOR_EMPLOYEE text message with this switch enabled.

app.setDialogTitle(
    apex.lang.formatMessage(
        'ADDRESS_FOR_EMPLOYEE',
        apex.items.P60_ENAME.value));

In Cascading Style Sheets (CSS), the viewport is the visible browser area. One vertical stretching strategy sizes a component to the remaining vertical space after accounting for other page elements.

As shown below, the CSS expression 100vh represents 100% of the viewport height. One rem unit is the height of a character in the current page's root font. It's a relative unit so it works correctly even when the user zooms or shrinks the font size at runtime. For example, you might estimate the height of the navigation bar to be about five (5) lines of text in the root font size (5rem). So the expression below represents the vertical height remaining after accounting for the fixed navigation bar.

100vh - 5rem

The figure illustrates the height of the viewport, the height of the navigation bar, and the difference between the two representing the remaining vertical space.

Vertical stretching requires inspecting the web page to determine an appropriate container element to target in your CSS rules.

Right click the page and choose the Inspect context menu option to activate your browser's Developer Tools. As shown below, use the Elements tab to identify which element sets the component's height. For example, here the map region's <div> element with class a-MapRegion-container explicitly sets the height to 400 pixels (400px).

CSS rules can use calculated expressions.

The following CSS rule targets the a-MapRegion-container class and uses the calc() function to set the height of elements with that class to expression 100vh - 5rem. Notice the class name has a dot before it in the rule. The additional !important flag causes this rule to take precedence over any default height specification in the page. Like a spreadsheet formula, as the user resizes the browser the CSS height updates instantly based on the new viewport size.

.a-MapRegion-container {
   height : calc(100vh - 5rem) !important;
}

After selecting the page in the component tree, you can add this rule to the page-level CSS > Inline section as shown below.

This produces the outcome below. Notice in the figure that map uses all the remaining vertical height available to give users a better view.

Since you use the relative CSS rem units, as shown below the calculated expression works correctly whether the user zooms the font smaller or larger. The figure shows two versions of the same cards and map page of employees. The first is zoomed to 67% and the map uses all the remaining vertical space as expected. The second is zoomed to 175% and the map has adjusted appropriately in that case as well.

Use CSS to stretch a component vertically in a modal dialog.

As shown below, to stretch the Geocoded Address map in the modal drawer page, the fixed amount of page contents includes the title bar and Address field at the top as well as the dialog footer containing the buttons at the bottom. After some experimentation you settle on 100vh - 14rem as the expression that best approximates the remaining vertical space. This example highlights the approach: the actual amount to subtract for a specific dialog you build depends on your use case. The figure illustrates the empirical heights in CSS rem units of the dialog footer (5rem), the viewport (100vh), and the header and other contents above the map (9rem), along with the calculated height of the map component to stretch vertically.

.a-GeoCoder-map  {
   height : calc(100vh - 14rem) !important;
}

In no time, as shown below, your modal dialog's Geocoded Address map is now also efficiently using the remaining vertical space. The figure shows the Geocoded Address map occupying the entire space below the Address text field and above the dialog footer.

Use a Static Application File shared component to include files of any kind with your app.

From the Shared Components > Static Application Files page in the App Builder, add a file named app.js to include application-wide helper functions.

• Using JavaScript Namespace Template
  Use an app.js file to define a single global namespace for reusable JavaScript helper functions.
• Comparing Namespace to PL/SQL Package
  Use an immediately invoked function expression (IIFE) to create an app namespace for reusable JavaScript helper functions.

Once you add JavaScript file to Static Application Files, cite the script's reference URL in application settings to load it on all pages.

In the Shared Components > Static Application Files list page, as shown below, you can find the Reference URL for any file. When useful, since the list is an Interactive Report, use its filtering features to show only JavaScript files. Notice the Reference column value for JavaScript files includes both the #APP_FILES# and #MIN# substitutions. Copy the Reference value for the app.js file to the clipboard to use in the next step.

To include a static application JavaScript file in every page, start by copying its Reference path from the page above. Then, as shown below, paste that value into the File URLs list field on the JavaScript tab of the Shared Component > User Interface Attributes page. If you include multiple files, list one per line.

Use an app.js file to define a single global namespace for reusable JavaScript helper functions.

This approach creates a single global symbol app instead of adding many separate function names into the global JavaScript scope. That keeps things clean and avoids naming conflicts.

// Export single global symbol "app" to keep code clean
const app = (function($) {

    // --- Private Functions ---

    // function myPrivateFunction(itemName) {
    //   ⋮
    // }

    // --- Public Functions (returned below!) ---

    //----------------------------------------------------
    // Set the title of the current dialog window
    //----------------------------------------------------
    function setDialogTitle(title) {
        apex.util.getTopApex()
                 .jQuery(".ui-dialog-content")
                 .dialog("option", "title", title);
    }

    //----------------------------------------------------
    // Return true if x and y are different, treating null
    // and empty string as equivalent in the comparison
    //----------------------------------------------------
    function areDifferent(x, y) {
        return (x || '') !== (y || '');
    }

    // Only these functions are exposed to JavaScript code
    // in APEX pages; everything else stays private.
    return {
        setDialogTitle,
        areDifferent
    };
    // Ensure $ in app namespace resolves to correct jQuery
})(apex.jQuery);

Use an immediately invoked function expression (IIFE) to create an app namespace for reusable JavaScript helper functions.

// Define an "app" namespace with exported functions
const app = (function($) {

    // --- Private Functions ---
    function myPrivateFunction(itemName) {…}

    // --- Public Functions (returned below!) ---
    function setDialogTitle(title) {…}
    function areDifferent(x, y) {…}

    return {
        setDialogTitle,
        areDifferent
    };
})(apex.jQuery);

So when you later write the following line of code in an APEX page, it calls the setDialogTitle function inside the app function library.

app.setDialogTitle("Hello");

Since app defines a unique name to unambiguously qualify its exported function names, JavaScript developers call it a namespace.

This is very similar to how you use packages in PL/SQL.

if (typeof yourParam === "undefined") {
  // yourParam was not passed
}