# How to find closest point on a line from a point in Oracle Spatial?

I want to find the nearest point on a line from a point in Oracle Spatial. I know how to do in SQL Server and PostgreSQL but could not find anything about Oracle. I have used MDSYS.sdo_nn to find the closest line from a point, but I need the closest point on line as well to calculate distance from the start of that line.

What you are asking for is linear referencing. Check out the `SDO_LRS` package for that.

First the `SDO_NN` operator will give you the nearest line to a given point, then `SDO_LRS.PROJECT_PT()` against that line will give you a point on the line that contains the measure of that point, i.e. the distance from the start of the line.

Here is a complete example. First consider the following two tables

``````SQL> desc us_interstates
Name                                     Null?    Type
---------------------------------------- -------- ----------------------------
ID                                       NOT NULL NUMBER
INTERSTATE                                        VARCHAR2(35 CHAR)
GEOM                                              MDSYS.SDO_GEOMETRY

SQL> desc us_cities
Name                                     Null?    Type
---------------------------------------- -------- ----------------------------
ID                                       NOT NULL NUMBER
CITY                                              VARCHAR2(42 CHAR)
STATE_ABRV                                        VARCHAR2(2 CHAR)
POP90                                             NUMBER
RANK90                                            NUMBER
LOCATION                                          MDSYS.SDO_GEOMETRY
``````

This gets the closest interstate to the Indianapolis:

``````select
interstate,
sdo_nn_distance(1) meters
from us_interstates i, us_cities c
where sdo_nn(i.geom, c.location, 'sdo_num_res=1',1) = 'TRUE'
and c.city = 'Indianapolis';

INTERSTATE     METERS
---------- ----------
I65/I70    367.061345

1 row selected.
``````

Let's get the shape of that interstate and turn it into an LRS geometry:

``````select
interstate,
sdo_nn_distance(1) meters ,
sdo_lrs.convert_to_lrs_geom(i.geom) lrs_line
from us_interstates i, us_cities c
where sdo_nn(i.geom, c.location, 'sdo_num_res=1',1) = 'TRUE'
and c.city = 'Indianapolis';

INTERSTATE     METERS LRS_LINE
---------- ---------- ------------------------------------------------
I65/I70    367.061345 SDO_GEOMETRY(3302, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(-86.145485, 39.753242, 0, -86.144585, 39.754372, 147.275807, -86.144035, 39.755329, 263.516454, -86.143509, 39.75893, 665.868292, -86.14325, 39.760296, 819.150604, -86.143044, 39.766884, 1550.82981, -86.142845, 39.771091, 2018.24493, -86.14267, 39.77475, 2424.78127, -86.141968, 39.776432, 2620.97959, -86.141388, 39.778408, 2845.93198, -86.140984, 39.779915, 3016.79675, -86.140984, 39.781193, 3158.69363, -86.141083, 39.78175, 3221.11637, -86.141243, 39.781971, 3249.22263))

1 row selected.
``````

Each point in that geometry carries a measure. By default, measures start at 0 for the first point of the geometry and increase with the length of the line. The measure of the last point is the same as the measured length of the line (in meters).

Now let's project the point onto that line. This returns an LRS point on the line, which contains the measure of that point, i.e. the distance from the start of the line.

``````select
interstate,
sdo_nn_distance(1) meters ,
sdo_lrs.project_pt(
sdo_lrs.convert_to_lrs_geom(i.geom),
c.location,
0.05
) point_on_line
from us_interstates i, us_cities c
where sdo_nn(i.geom, c.location, 'sdo_num_res=1',1) = 'TRUE'
and c.city = 'Indianapolis';

INTERSTATE     METERS POINT_ON_LINE
---------- ---------- -------------------------------------------------------------------------------
I65/I70    367.061345 SDO_GEOMETRY(3301, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(-86.141809, 39.7769737, 2682.64485))

1 row selected.
``````

And finally, extract that measure:

``````select
interstate,
sdo_nn_distance(1) meters,
sdo_lrs.geom_segment_start_measure (
sdo_lrs.project_pt(
sdo_lrs.convert_to_lrs_geom(i.geom),
c.location,
0.05
)
) measure
from us_interstates i, us_cities c
where sdo_nn(i.geom, c.location, 'sdo_num_res=1',1) = 'TRUE'
and c.city = 'Indianapolis';

INTERSTATE     METERS    MEASURE
---------- ---------- ----------
I65/I70    367.061345 2682.64485

1 row selected.
``````
• I wonder if `SDO_LRS.PROJECT_PT` uses similar logic/math to ArcGIS: "The shortest distance from a point to a line segment is the perpendicular to the line segment. If a perpendicular cannot be drawn within the end vertices of the line segment, then the distance to the closest end vertex is the shortest distance." Source: i.stack.imgur.com/fHvlR.png --and-- How proximity tools calculate distance – User1973 Mar 3 '20 at 4:54
• Yes that is pretty much the way that function works in Oracle. – Albert Godfrind Mar 3 '20 at 12:48