Tom,<div><br></div><div>You've got the right toolbox out, but you're doing a bit of mismatching between the tools.</div><div><br></div><div>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.</div>
<div><br></div><div>Examples of how to do this in a single step:</div><div><br></div><div>With Measures:</div><div><br></div><div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">SELECT </blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
<span class="Apple-tab-span" style="white-space:pre"> </span>ST_ASEWKT(ST_Locate_Along_Measure(linem,i))</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
FROM</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span class="Apple-tab-span" style="white-space:pre"> </span>( </blockquote>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span class="Apple-tab-span" style="white-space:pre"> </span>SELECT </blockquote>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span class="Apple-tab-span" style="white-space:pre"> </span>ST_AddMeasure(line,0,ST_Length(line)) as linem, generate_series(0,ST_Length(line)::int,25) as i</blockquote>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span class="Apple-tab-span" style="white-space:pre"> </span>FROM </blockquote>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span class="Apple-tab-span" style="white-space:pre"> </span>ST_MakeLine(ST_MakePoint(0,0),ST_MakePoint(1000,1000)) as line</blockquote>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span class="Apple-tab-span" style="white-space:pre"> </span>) as foo</blockquote>
</blockquote><div><br></div><div>Without:</div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"> </blockquote>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">SELECT </blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
<span class="Apple-tab-span" style="white-space:pre"> </span>ST_ASEWKT(ST_Line_Interpolate_Point(line,i/length))</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
FROM</blockquote><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span class="Apple-tab-span" style="white-space:pre"> </span>( </blockquote>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span class="Apple-tab-span" style="white-space:pre"> </span>SELECT </blockquote>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span class="Apple-tab-span" style="white-space:pre"> </span>line, ST_Length(line) as length, generate_series(0,ST_Length(line)::int,25) as i</blockquote>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span class="Apple-tab-span" style="white-space:pre"> </span>FROM </blockquote>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span class="Apple-tab-span" style="white-space:pre"> </span>ST_MakeLine(ST_MakePoint(0,0),ST_MakePoint(1000,1000)) as line</blockquote>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span class="Apple-tab-span" style="white-space:pre"> </span>) as foo</blockquote>
<br><div class="gmail_quote">On Wed, Jul 25, 2012 at 6:30 AM, Tom van Tilburg <span dir="ltr"><<a href="mailto:tom.van.tilburg@gmail.com" target="_blank">tom.van.tilburg@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div bgcolor="#FFFFFF" text="#000000">
<div>Hi Rob,<br>
<br>
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.<br>
<br>
Here's a way to do it:<br>
<br>
1) make a line out of your points (ST_SetSrid(ST_MakeLine(pointa,
pointb), 27700))<br>
2) add an M value to your line (ST_AddMeasure(geom, start, end))<br>
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! <br>
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))<br>
when N being the numbers you got from generate series in 3).<br>
<br>
Make sure not to put dimensional restrictions to your tables since
ST_AddMeasure will want to add that extra M dimension.<br>
<br>
Good luck!<br>
Tom<div><div class="h5"><br>
<br>
On 25-7-2012 12:50, uk52rob wrote:<br>
</div></div></div>
<blockquote type="cite"><div><div class="h5">
<div style="font-size:12pt;font-family:times new roman,new york,times,serif">
<div style="font-family:'times new roman','new york',times,serif;font-size:12pt">Hi,</div>
<div style="font-family:'times new roman','new york',times,serif;font-size:12pt"><br>
</div>
<div style="font-family:'times new roman','new york',times,serif;font-size:12pt">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.</div>
<div style="font-family:'times new roman','new york',times,serif;font-size:12pt"><br>
</div>
<div><font face="times new roman, new york, times, serif" size="3">I need to extract X and Y co-ordinates (EPSG: 27700
OSGB36/British National Grid) every 25m along a </font><font face="times new roman, new york, times, serif">fictitious</font><font face="times new roman, new york, times, serif" size="3"> line
between the points in order to build a 'profile' of the land
from a database containing height data.</font></div>
<div><font face="times new roman, new york, times, serif" size="3"><br>
</font></div>
<div><font face="times new roman, new york, times, serif">Does
anyone have any 'best practise' or experience with this sort
of project?</font></div>
<div><br>
</div>
<div><font face="times new roman, new york, times, serif">Many
thanks,</font></div>
<div><font face="times new roman, new york, times, serif"><br>
</font></div>
<div><font face="times new roman, new york, times, serif">Rob</font></div>
</div>
<br>
<fieldset></fieldset>
<br>
</div></div><pre>_______________________________________________
postgis-users mailing list
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
<br>
</div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br><br clear="all"><div><br></div>-- <br>************************************<br>David William Bitner<br>
</div>