2

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)

enter image description here

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)

0
4
+50

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.
1
3

Here is the answer from my side using different functions with the 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 insert or update of inspection_point on 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 insert or update of inspection_point on 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.

2
  • Using the SDO_GEOM.SDO_CLOSEST_POINTS() is a clever idea. It avoids going via the LRS mechanism. – Albert Godfrind Mar 8 '20 at 17:56
  • Thanks @AlbertGodfrind – Muhammad Imran Siddique Mar 9 '20 at 10:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.