5.6.4 Simplifying Points and Distance

The SDO_GEOMETRY type lets your applications store, search on, and manipulate spatial data.

A point is the simplest information an SDO_GEOMETRY value can hold. It can represent addresses and GPS locations. However, this data type also can handle:

• lines – for driving or walking routes or property boundaries,
• polygons – for city limits, sales territories, store footprints, lakes, or forests
• circles – for service areas, or delivery or impact zones.

Focusing on the points you need for the current task, in Oracle AI Database 26ai, you create an SDO_GEOMETRY for a point using the simple expression:

SDO_GEOMETRY(longitude,latitude)

However, in earlier database versions, the following helper functions can simplify your work. The MAKE_POINT function returns an SDO_GEOMETRY value for longitude and latitude:

function make_point(
    p_longitude in number,
    p_latitude  in number) 
return sdo_geometry
deterministic 
is
    geom_type_2d_point   constant number := 2001;
    spatial_ref_type_gps constant number := 4326;
begin
    return case 
              when     p_longitude is not null
                   and p_latitude  is not null
              then 
                 sdo_geometry(
                    geom_type_2d_point,
                    spatial_ref_type_gps, 
                    sdo_point_type(p_longitude, p_latitude,
                    null), null, null)
            end;
end;

The DISTANCE_IN_KM function returns the distance between two SDO_GEOMETRY points:

function distance_in_km(
    p_origin      in sdo_geometry,
    p_destination in sdo_geometry
)
return number
deterministic
as
begin
    return sdo_geom.sdo_distance(
            p_origin,
            p_destination, 
            tol=>1, 
            unit=>'unit=KM');
end distance_in_km;

Tip:

For any SDO_GEOMETRY object, use the TO_GEOJSON function in the SDO_UTIL package to get its GeoJSON representation.