Search code examples
oracle-databasegeometrygisoracle-spatialoracle18c

Snap a point to a line and get the line ID

I have an Oracle 18c database that has SDO_GEOMETRY tables:

  • Sidewalk polyline table
  • Sidewalk inspection point table

When sidewalk inspection points are created, I want to automatically snap the points to the closest line --and-- insert the line's ID into a column.

enter image description here

  • I'm not interested in 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 it is mid-segment).
  • The search tolerance would be 5 metres.

Is there a way to do this via an Oracle Spatial/SDO_GEOMETRY insert trigger?

(I've poked around in the docs, but I haven't seen anything so far that lends itself to snapping.)

Solution

  • I answered this question here: https://gis.stackexchange.com/a/352809/21232

    In a nutshell, the solution is to use the Linear Referencing features of Oracle Spatial