How can I measure(length) line of segment in postgis?

Question

This is my first question. I wonder that, how can I get length of line's segment?

Yellow line is my road. blue Points are my start and end points. So I want to measure point1 and point2 between route.This points are variable. So I ll select them. How can I get it with postgis query ?

I UPDATE MY QUESTION. :)

Answer 1

I found my solution.

select ST_Length(ST_AsText(ST_Line_Substring(line,ST_Line_Locate_Point(line,ST_ClosestPoint(line,strtpoint )),ST_Line_Locate_Point(line,ST_ClosestPoint(line,endpoint ))))as cizgi_uzunlugu FROM ( SELECT 'POINT(30 30)'::geometry As strtpoint,'POINT(110 0)'::geometry As endpoint,'LINESTRING(0 0, 0 30, 30 30, 30 50, 90 50, 90 60, 110 60, 110 0, 150 0, 150 30)'::geometry As line) As foo;

Answer 2

Here's what I would try:

1. ST_PointN() to get desired vertices
2. ST_MakeLine with points acquired in previous step
3. ST_Length with line acquired in previous step to get the final length.