Snap a point to a line and get the line ID (Oracle Spatial or SDE.ST_GEOMETRY)

Question

I have an Oracle 18c/10.7.1 geodatabase that has:

• A sidewalk polyline feature class (existing features)
• And a sidewalk inspection point feature class (the features are created via ArcGIS Collector/Portal)

---

As soon as the sidewalk inspection points are created, I want the database to automatically snap the points to the closest line (and insert the line's ID into the point FC).

• It would be ideal if this could happen instantly -- instead than doing it with post-processing.
• I'm not interested in cheating by snapping to the line vertices (sidewalk deficiencies/inspections don't necessarily occur at vertices). I need to be able to snap to the closest position along the line (even if the closest position is mid-segment).
• The search tolerance would be 5 metres.

---

Is there a way to snap a point to a line (and get the line's ID) via logic in the database? (Oracle Spatial or SDE.ST_GEOMETRY)

Answer 1

This example uses Oracle Spatial's Linear Referencing facility to project the inspection points onto the closest sidewalk like. It then updates the inspection point with:

• The id of the closest sidewalk line,
• The distance to the sidewalk line,
• The point on the line,
• The measure on the line from the start of the line.

Let's assume the tables are like this:

create table sidewalks (
  id number primary key,
  geom sdo_geometry
);

create table inspections (
  id number primary key,
  inspection_point sdo_geometry,
  sidewalk_id number,
  sidewalk_distance number,
  sidewalk_measure number,
  projected_point sdo_geometry
);

Fill the sidewalks with data from your example

insert into sidewalks 
values (
 1,
 sdo_geometry ('LINESTRING(0 5, 10 10, 30 0, 50 10, 60 10)')
);
commit;

Need a spatial index:

insert into user_sdo_geom_metadata
values (
  'SIDEWALKS',
  'GEOM',
  sdo_dim_array (
    sdo_dim_element('X',0,1000,0.05),
    sdo_dim_element('Y',0,1000,0.05)
  ),
  null
);
commit;

create index sidewalks_sx on sidewalks(geom) indextype is mdsys.spatial_index;   

Here is the trigger:

create or replace trigger sidewalk_inspections
before insert or update of inspection_point on inspections
for each row

declare
  sidewalk_id number;
  sidewalk_distance number;
  sidewalk_geom sdo_geometry;
  sidewalk_lrs sdo_geometry;
  projected_point sdo_geometry;

begin

  -- Find nearest sidewalk
  select id, geom, sdo_nn_distance(1)
  into sidewalk_id, sidewalk_geom, sidewalk_distance
  from sidewalks
  where sdo_nn(geom, :new.inspection_point, 'sdo_num_res=1',1) = 'TRUE';

  -- Convert sidewalk geometry to LRS
  sidewalk_lrs := sdo_lrs.convert_to_lrs_geom(sidewalk_geom);

  -- Project inspection point onto the sidewalk line
  projected_point := sdo_lrs.project_pt(sidewalk_lrs,:new.inspection_point);

  -- Update inspection point
  :new.sidewalk_id := sidewalk_id;
  :new.sidewalk_distance := sidewalk_distance;
  :new.sidewalk_measure := sdo_lrs.get_measure(projected_point);
  :new.projected_point := sdo_lrs.convert_to_std_geom(projected_point);
end;
/
show errors

Example insert:

insert into inspections(id,inspection_point) values (101, sdo_geometry('POINT(20 1)'));
commit;

The result:

SQL> select * from inspections;

        ID INSPECTION_POINT                                                    SIDEWALK_ID SIDEWALK_DISTANCE SIDEWALK_MEASURE PROJECTED_POINT
---------- ------------------------------------------------------------------- ----------- ----------------- ---------------- ---------------
       101 SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(20, 1, NULL), NULL, NULL)             1        3.57770876       24.1495342  SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(21.6, 4.2, NULL), NULL, NULL)

1 row selected.

Answer 2

Here is the answer from my side using different functions with a trigger.

Create Table.

create table linestrings (
  id number primary key,
  geom sdo_geometry
);

create table inspection (
  id number primary key,
  inspection_point sdo_geometry,
  linestring_id number,
  linestring_distance number,
  projected_point sdo_geometry
);

Insert data into

insert into linestrings 
values (
 1,
 sdo_geometry ('LINESTRING(0 5, 10 10, 30 0, 50 10, 60 10)')
);
commit;

Create geom index.

insert into user_sdo_geom_metadata
values (
  'LINESTRINGS',
  'GEOM',
  sdo_dim_array (
    sdo_dim_element('X',0,1000,0.05),
    sdo_dim_element('Y',0,1000,0.05)
  ),
  null
);
commit;

create index linestrings_sx on linestrings(geom) indextype is mdsys.spatial_index;

Create trigger.

create or replace trigger linestring_inspections
before inserting or updating inspection_point on the inspection
for each row

declare
  linestring_id number;
  linestring_distance number;
  linestring_geom sdo_geometry;
  geomb sdo_geometry;

  projected_point sdo_geometry;

begin

  -- Find nearest linestring
  select id, geom
  into linestring_id, linestring_geom
  from linestrings
  ORDER BY SDO_GEOM.SDO_DISTANCE(geom, :new.inspection_point, 1) ASC fetch first 1 rows only;
  
  SDO_GEOM.SDO_CLOSEST_POINTS(linestring_geom, :new.inspection_point, 1.25, NULL, linestring_distance, projected_point, geomb);
  
  -- Update inspection point
  :new.linestring_id := linestring_id;
  :new.linestring_distance := linestring_distance;
  :new.projected_point := projected_point;
end;

Another trigger approach.

create or replace trigger linestring_inspections
before inserting or updating inspection_point on the inspection
for each row

declare
  linestring_id number;
  linestring_distance number;
  linestring_geom sdo_geometry;
  geoma sdo_geometry;
  geom_distance number;
  projected_point sdo_geometry;

begin

  -- Find nearest linestring
  select id, geom, sdo_nn_distance(1)
  into linestring_id, linestring_geom, linestring_distance
  from linestrings
  where sdo_nn(geom, :new.inspection_point, 'sdo_num_res=1', 1) = 'TRUE';
  
  SDO_GEOM.SDO_CLOSEST_POINTS(linestring_geom, :new.inspection_point, (linestring_distance*1.25/100), NULL, geom_distance, projected_point, geoma);
  
  -- Update inspection point
  :new.linestring_id := linestring_id;
  :new.linestring_distance := linestring_distance;
  :new.projected_point := projected_point;
end;

Insert data.

insert into inspection(id, inspection_point) values (101, sdo_geometry('POINT(20 1)'));
commit;

select id, SDO_UTIL.TO_WKTGEOMETRY(INSPECTION_POINT), SDO_UTIL.TO_WKTGEOMETRY(PROJECTED_POINT), LINESTRING_DISTANCE from inspection

Base answer copied from @Albert.