15.2.2 Referencing Coordinates Item Values

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

Using an SDO_GEOMETRY type, access them like this in PL/SQL:

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;

To access them using SQL instead, use:

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;

If you prefer to work with the GeoJSON document, use the following in PL/SQL:

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;

Alternatively, to access them via the GeoJSON document in SQL, use:

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;