[postgis-users] Extracting points from a line

[Tom van Tilburg]

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 list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120725/24d8a74a/attachment.html>