[postgis-users] Extracting points from a line

[David William Bitner]

Tom,

You've got the right toolbox out, but you're doing a bit of mismatching
between the tools.

You can certainly do this using the Linear Referencing tools by either
using ST_Add_Measure and then using ST_LocateAlong (or
ST_Locate_Along_Measure pre 2.0) OR just use ST_Line_Interpolate_Point
along with ST_Length.

Examples of how to do this in a single step:

With Measures:

SELECT
>
>  ST_ASEWKT(ST_Locate_Along_Measure(linem,i))
>
> FROM
>
> (
>
>  SELECT
>
>  ST_AddMeasure(line,0,ST_Length(line)) as linem,
>> generate_series(0,ST_Length(line)::int,25) as i
>
>  FROM
>
>  ST_MakeLine(ST_MakePoint(0,0),ST_MakePoint(1000,1000)) as line
>
>  ) as foo
>
>
Without:

>

SELECT

 ST_ASEWKT(ST_Line_Interpolate_Point(line,i/length))

FROM

(

 SELECT

 line, ST_Length(line) as length,
> generate_series(0,ST_Length(line)::int,25) as i

 FROM

 ST_MakeLine(ST_MakePoint(0,0),ST_MakePoint(1000,1000)) as line

 ) as foo


On Wed, Jul 25, 2012 at 6:30 AM, Tom van Tilburg
<tom.van.tilburg at gmail.com>wrote:

>  Hi Rob,
>
> Points in a regular interval have always been a pain to create in postgis.
> I hope someone will write a decent linear_reference code for this some time
> soon.
>
> Here's a way to do it:
>
> 1) make a line out of your points (ST_SetSrid(ST_MakeLine(pointa, pointb),
> 27700))
> 2) add an M value to your line (ST_AddMeasure(geom, start, end))
> 3) generate_series of intervals
> generate_series(0,floor(ST_Length(geom)/25.0))  < please check how to deal
> with that last segment smaller than 25 meter!
> 4) now you know how many points you need in every line you can use
> (ST_Line_Interpolate_Point(geom, (N*25)/St_Length(geom))
> when N being the numbers you got from generate series in 3).
>
> Make sure not to put dimensional restrictions to your tables since
> ST_AddMeasure will want to add that extra M dimension.
>
> Good luck!
>     Tom
>
>
> On 25-7-2012 12:50, uk52rob wrote:
>
>  Hi,
>
>  I have two point geometry tables, with which I need to compare a single
> point from the left table to all points in the right table.
>
>  I need to extract X and Y co-ordinates (EPSG: 27700 OSGB36/British
> National Grid) every 25m along a fictitious line between the points in
> order to build a 'profile' of the land from a database containing height
> data.
>
>  Does anyone have any 'best practise' or experience with this sort of
> project?
>
>  Many thanks,
>
>  Rob
>
>
> _______________________________________________
> postgis-users mailing listpostgis-users at postgis.refractions.nethttp://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>


-- 
************************************
David William Bitner
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120726/14bab219/attachment.html>