Creating a mobile app with APEX - Part 10: Use your location

Your phone is packed with all kind of sensors. Many of them you can not use in JavaScript, but you can access the GPS sensor to retrieve your location. In this blogpost we will access the GPS location and use it in our application.

On the Activity page the GPS Location will be retrieved. When the record is entered when the user is still on the location of the activity she can check Use location. With this item checked the GPS location is stored with the activity.
When the page is opened to enter a new activity the GPS location is retrieved. It is compared to the GPS locations in the database and if possible it will provide a default value for the Location item.

We will:

• update the database to add columns to store the location
• import a plug-in to retrieve the GPS location
• add items on the Activity page to store the location
• add Dynamic Actions on the Activity page to act on the GPS location
• use the stored location to provide a default value for the column Location

Update the database objects

There are some changes needed in the database to accommodate the location data. 

The columns act_lattitude and act_longitude are added to the table ttm_activity. We also add a column act_sdo_location which is of the type sdo_geometry to be able to use Oracle Spatial functions. 

The trigger on ttm_activity is changed to construct the column act_sdo_location from act_lattitude and act_longitude.

The view ttm_activity_vw and it's instead of trigger are changed to support the new columns. 

The package ttm_alg is expanded with a function to yield a default value for location. 

Download the change script from here and execute it. 

Import the Store Location plug-in

Download the Store Location plug-in from APEX World. (Go to the Plug-ins page and search for store location)

Load into the application plug-ins:

• Go to Shared Components > Plug-ins
• Press Import
• Select the plug-in file
• Hit Next
• Press Next again
• Hit the button Install Plug-in

The plug-in is installed and ready for use. 

Add items to the Activity page

Items are needed to store the GPS coordinates that are retrieved by the plug-in. 

Furthermore we need a check box to indicate whether the record is entered on the location of the activity. 

• Go to page 15
• Go to P15_ACT_LOCATION and press the right mouse button
• Select Create Page Item
• Name: P15_USE_LOCATION
• Type: Checkbox
• Appearance > Template: Hidden
• List of Values:
• Type: Static Values
• Static Values: STATIC:Use location;Y
• Create another item:
• Name: P15_ACT_LATTITUDE
• Type: Text Field
• Label: Lattitude
• Source:
• Type: Database Column
• Database Column: ACT_LATTITUDE
• And another one:
• Name: P15_ACT_LONGITUDE
• Type: Text Field
• Label: Longitude
• Source:
• Type: Database Column
• Database Column: ACT_LONGITUDE

Adding the Dynamic Actions

Two dynamic actions will be added. One to retrieve the GPS location and another to react on the retrieval of the location. These are separate actions because the retrieval of the GPS location is an asynchronous process. 

• Go to page 15
• Go to the Dynamic Actions tab
• Right click on Page Load and click Create Dynamic Action
• Name: Page Load - Retieve GPS location
• Select the Action
• Change Identification > Action to Store Location [Plug-In]
• In Settings:
• Lattitude Item: P15_ACT_LATTITUDE
• Longitude Item: P15_ACT_LONGITUDE
• Right click on Page Load and click Create Dynamic Action
• Name: Location retrieved
• When:
• Event: Custom
• Custom Event: location-retrieved
• Selection Type: jQuery Selector
• jQuery Selector: html
• Select the Action
• Action: Execute PL/SQL Code
• Settings > PL/SQL Code:

:P15_ACT_LOCATION := nvl(:P15_ACT_LOCATION

       , ttm_alg.default_location(:P15_ACT_LATTITUDE,:P15_ACT_LONGITUDE));

• Items to Submit: P15_ACT_LATTITUDE,P15_ACT_LONGITUDE
• Items to Return: P15_ACT_LOCATION

The last Dynamic Action retrieves a default value for the field Location based on the GPS location. The default value is retrieved in the function ttm_alg.default_location that uses an Oracle Spatial query to find activities that have been recorded close to the current GPS location. The query is discussed in the last paragraphs of this post. 

Eating the pudding

The proof is in..., well you know. 

So let's try it. 

• log in to the application on your phone
• press New activity
• after the page has been opened your permission to use the location is asked. Confirm it.
• now scroll down to the bottom
• (after some time) you will see the GPS coordinates
• fill in the items of the activity
• enter in  Location: test GPS location
• check Use Location, else the GPS location will not be stored
• accept the changes
• press New again
• scroll down to the bottom
• (after some time) you will see the GPS coordinates
• Location should then contain the value you entered for the previous record: test GPS location

If things do not work as expected use the Chrome Inspector (or any other Developer tools) to examine what is wrong. 

Background on selecting the default value

Based on the GPS coordinates the default value for the Location field is determined. This is done using a spatial query:

      select act_location

      from   ttm_activities

      where  act_lattitude is not null 

        and  act_longitude is not null

        and  act_use_location = 'Y'

        and  sdo_geom.within_distance(act_sdo_location,0.2

                , sdo_geometry(2001,8307

                              ,sdo_point_type(p_longitude,p_lattitude,null)

                              ,null,null),1,'unit=km')   

             = 'TRUE'

        order by sdo_geom.sdo_distance(act_sdo_location

                , sdo_geometry(2001,8307

                              ,sdo_point_type(p_longitude,p_lattitude,null)

                              ,null,null),1,'unit=km')   

      ; 

This query uses the Oracle Spatial function sdo_geom.within_distance to find all the records with a GPS location within 200 m's (the value 0.2) from the given GPS coordinates. This proves to be a reasonable distance to correct for the bias in GPS location. 

The records are sorted based on the distance to the coordinates which is retrieved using the function sdo_geom.sdo_distance. This way if the query delivers more than one row the one closest to the the given point is selected. 

Using Oracle Spatial functions

The Oracle Spatial functions expect arguments in the  form of a SDO Geometry objects. The column act_sdo_location is already stored as such an object. The GPS coordinates are numbers and need to be converted to a SDO Geometry object:

sdo_geometry(2001,8307

            ,sdo_point_type(p_longitude,p_lattitude,null)

                              ,null,null),1,'unit=km')

Without going to deep into the complex geometry subjects: 

• the first parameter 2001 indicates that the shape is a point. You can also have lines, polygons etc. 
• the second parameter 8307 references the coordinate system
• you notice the values for longitude and lattitude in the call to sdo_point_type
• the last parameter indicates that the unit used is kilometers. 
• more detailed information can be found in the Oracle documentation

To be able to compare values the coordinate system and the unit used should be consistent in database and code.