15.1.1 Producing GeoJSON from Coordinates

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.

Consider an EMP_ADDRESSES table that stores each employee's address, and the following EMP_AND_ADDRESS view that joins EMP and EMP_ADDRESS tables:

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);

Tip:

When the join column in both tables has the same name like EMPNO here, then the USING (EMPNO) clause is an alternative to ON e.EMPNO = a.EMPNO to avoid repeating the column name. Notice in this case that the SELECT list omits the table alias before the join column name EMPNO.

Luckily, the GeoJSON format for a point is simple:

{"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.

Tip:

Using your helper functions from Simplifying Points and Distance, you could alternatively use the following expression for the GEO_JSON_POINT column:

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

Caution:

Use JSON_OBJECT and JSON_ARRAY to create GeoJSON. They format decimal coordinates as JSON-compatible decimal values regardless of the end user's browser language. Using a string-based approach like the following would work fine in English and other locales that use the "." as their decimal separator. However, it would fail when running in Italian, for example, where a decimal is formatted like 33,12345 instead.

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

A string approach can work, but would require formatting the numbers using TO_CHAR() by explicitly specifying the 'NLS_NUMERIC_CHARACTERS=.,' in its optional third parameter.