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.tilb...@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@postgis.refractions.nethttp://postgis.refractions.net/mailman/listinfo/postgis-users > > > > _______________________________________________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > -- ************************************ David William Bitner
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users