Transferring Measures to Points

Hello,

I was wondering if someone could help me.  

I have a spatial table containing lines (with measures) and a spatial table of
points sitting approximately along these lines.  I am attempting to transfer the
m (measured) value and id from a selected line to each point in the table.

An example would be transferring the measured value to accident points along a
selected road.

What would be the best work flow to achieve this?  

If anyone can help it would be greatly appreciated.
  
Cheers,

Robert Lewis

I haven't actually tried this, but the ST_PerpPoints function looks like 
it should provide this capability.  The Spatial Extender User's Guide 
describes this as:

ST_PerpPoints takes a curve or multicurve and a point as input 
parameters and returns the perpendicular projection of the given point 
on the curve or multicurve. The point with the smallest distance between 
the given point and the perpendicular point is returned. If two or more 
such perpendicular projected points are equidistant from the given 
point, they are all returned.

If no perpendicular point can be constructed, then an empty point is 
returned. If the given curve or multicurve has Z or M coordinates, the Z 
or M coordinate of the resulting points are computed by interpolation on 
the given curve or multicurve.

Regards,
David

Robert.Lewis@roads.vic.gov.au wrote:
> Hello,
> 
> I was wondering if someone could help me.  
> 
> I have a spatial table containing lines (with measures) and a spatial table
of points sitting approximately along these lines.  I am attempting to transfer
the m (measured) value and id from a selected line to each point in the table.
> 
> An example would be transferring the measured value to accident points
along a selected road.
> 
> What would be the best work flow to achieve this?  
> 
> If anyone can help it would be greatly appreciated.
>   
> Cheers,
> 
> Robert Lewis

David Adler wrote:

> I haven't actually tried this, but the ST_PerpPoints function looks like
> it should provide this capability.  The Spatial Extender User's Guide
> describes this as:
> 
> ST_PerpPoints takes a curve or multicurve and a point as input
> parameters and returns the perpendicular projection of the given point
> on the curve or multicurve. The point with the smallest distance between
> the given point and the perpendicular point is returned. If two or more
> such perpendicular projected points are equidistant from the given
> point, they are all returned.
> 
> If no perpendicular point can be constructed, then an empty point is
> returned. If the given curve or multicurve has Z or M coordinates, the Z
> or M coordinate of the resulting points are computed by interpolation on
> the given curve or multicurve.

David is right.  ST_PerpPoint does exactly that.

Note that ST_PerpPoint return ST_Geometry values because a point may have
(potentially) multiple perpendicular points.  But in if the point is
exactly on the linestring, this situation cannot occur.

-- 
Knut Stolze
DB2 z/OS Utilities Development