<html>
<head>
<style><!--
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 12pt;
font-family:Calibri
}
--></style></head>
<body class='hmmessage'><div dir='ltr'>Hi Rémi,<div> Thanks for the help here.</div><div>Got it solved based on your suggestions through both SQL and plpgsql.</div><div>So good to learn!!</div><div><br></div><div>Regards,</div><div><br></div><div>Paul<br><br><div><hr id="stopSpelling">Date: Mon, 24 Nov 2014 19:34:58 +0100<br>From: remi.cura@gmail.com<br>To: postgis-users@lists.osgeo.org<br>Subject: Re: [postgis-users] PLPGSQL Function to Calculate Bearing<br><br><div dir="ltr"><div><div><div><div>Hey,<br></div><div>first you don't need plpgsql for this, you can do it with windows functions (<a href="http://www.postgresql.org/docs/current/static/functions-window.html" target="_blank">http://www.postgresql.org/docs/current/static/functions-window.html</a>)<br><br></div>for the following I assume you have some order available on your point data, the order is stored in "id" column :<br><br></div>CREATE TABLE my_azimuth_data AS <br> SELECT id, wgs_geom --, ST_X(wgs_geom), ST_Y(wgs_geom), ST_Z(wgs_geom) -- X,Y,Z seems unecessary<br>FROM points_table <br>WHERE ST_Within(points_table.wgs_geom, ST_GeomFromtext('POLYGON...',4326) = TRUE<br></div><div>ORDER BY id ASC ;<br><br></div><div>SELECT *, ST_Azimuth(wgs_geom,lead(wgs_geom) OVER (ORDER BY id ASC) ) AS my_azimuth<br>FROM my_azimuth_data<br></div><div>ORDER BY id ASC<br></div><br></div>--note : you may need to deal with the last row, because it has nothing after it , so I don't know what it's azymuth should be...<br><div><br></div><div>now if you really want to use plpgsql :<br> <div> DROP FUNCTION IF EXISTS get_bearings_from_points();</div><div> CREATE OR REPLACE FUNCTION get_bearings_from_points()</div><div> RETURNS TABLE(id int, ogeom geometry, my_azimuth double) AS</div><div> $BODY$</div><div> DECLARE</div><div> r record ; --generic type, will hold any row<br></div><div> old_point geometry := NULL; --we store the value of previous geometry<br></div><div> BEGIN</div><div> FOR r IN</div><div> SELECT *<br> FROM my_azimuth_data --loop on the point<br></div><div> ORDER BY id ASC<br></div><div> LOOP<br></div><div> --note : you may want to deal with the first row in a peculiar fashion<br></div><div> id := <a href="http://r.id" target="_blank">r.id</a>; --filling the output row<br> ogeom:= r.wgs_geom ;<br></div><div> my_azimuth := ST_Azimuth(old_point, r.wgs_geom) ; <br> old_point := r.wgs_geom ; --updating the old_point with new value for next iteration<br></div><div> RETURN NEXT; --outputing the row<br></div><div> END LOOP;<br><br></div><div> RETURN;</div><div> END</div><div> $BODY$</div><div> LANGUAGE plpgsql;</div><br></div><div>If you are more familiar with python you may benefit from using pl/pythonu.<br><br></div><div>Cheers,<br>Rémi-C<br></div></div><div class="ecxgmail_extra"><br><div class="ecxgmail_quote">2014-11-24 18:33 GMT+01:00 Paul & Caroline Lewis <span dir="ltr"><<a href="mailto:paulcaz80@hotmail.com" target="_blank">paulcaz80@hotmail.com</a>></span>:<br><blockquote class="ecxgmail_quote" style="border-left:1px #ccc solid;padding-left:1ex;">
<div><div dir="ltr"><div>Basically I can't get my head around the syntax of plpgsql and would appreciate some help with the following efforts.</div><div>I have a table containing 1000's of wgs84 points. The following SQL will retrieve a set of points within a bounding box on this table:</div><div><br></div><div> SELECT id, ST_X(wgs_geom), ST_Y(wgs_geom), ST_Z(wgs_geom) FROM points_table INNER JOIN (SELECT ST_Transform(ST_GeomFromText('POLYGON((-1.73576102027 51.5059743629,-1.73591122397 51.5061067655,-1.73548743495 51.5062838333,-1.73533186682 51.5061514313,-1.73576102027 51.5059743629))',4326),) AS bgeom) AS t2 ON ST_Within(local_geom, t2.bgeom)</div><div><br></div><div>What I need to do is add a bearing/azimuth column to the results that describes the bearing at each point in the returned data set.</div><div>So the approach I'm trying to implement is to build a plpgsql function that can select the data as per above and calculate the bearing between each set of points in a loop.</div><div>However my efforts at understanding basic data access and handling within a plpgsql function are failing miserably.</div><div><br></div><div>An example of the current version of the function I'm trying to create is as follows:</div><div><br></div><div> CREATE TYPE bearing_type AS (x numeric, y numeric, z numeric, bearing numeric);</div><div> DROP FUNCTION IF EXISTS get_bearings_from_points();</div><div> CREATE OR REPLACE FUNCTION get_bearings_from_points()</div><div> RETURNS SETOF bearing_type AS</div><div> $BODY$</div><div> DECLARE</div><div> rowdata points_table%rowtype;</div><div> returndata bearing_type;</div><div> BEGIN</div><div> FOR rowdata IN</div><div> SELECT nav_id, wgs_geom FROM points_table INNER JOIN (SELECT ST_Transform(ST_GeomFromText('POLYGON((-1.73576102027 53.5059743629,-1.73591122397 53.5061067655,-1.73548743495 53.5062838333,-1.73533186682 53.5061514313,-1.73576102027 53.5059743629))',4326),27700) AS bgeom) AS t2 ON ST_Within(local_geom, t2.bgeom)</div><div> LOOP</div><div> returndata.x := ST_X(rowdata.wgs_geom);</div><div> returndata.y := ST_Y(rowdata.wgs_geom);</div><div> returndata.z := ST_Z(rowdata.wgs_geom);</div><div> returndata.bearing := ST_Azimuth(<current_point> , <next_point>)</div><div> RETURN NEXT returndata;</div><div> END LOOP;</div><div> RETURN;</div><div> END</div><div> $BODY$</div><div> LANGUAGE plpgsql;</div><div><br></div><div>I should just be able to call this function as follows:</div><div><br></div><div> SELECT get_bearings_from_points();</div><div><br></div><div>and get the desired result.</div><div>Basically the problems are understanding how to access the rowdata properly such that I can read the current and next points.</div><div><br></div><div>In the above example I've had various problems from how to call the ST_X etc SQL functions and have tried EXECUTE select statements with errors re geometry data types.</div><div><br></div><div>Thanks</div><div><br></div><div>Paul</div><div><br></div><div><br></div><div>Any insights/help would be much appreciated</div> </div></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>
<br>_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</div></div> </div></body>
</html>