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.