[postgis-users] PLPGSQL Function to Calculate Bearing

Rémi Cura remi.cura at gmail.com
Tue Nov 25 10:33:43 PST 2014

```Great, I'm happy for you ;-)
Cheers,
Rémi-C

2014-11-25 18:11 GMT+01:00 Paul & Caroline Lewis <paulcaz80 at hotmail.com>:

> Hi Rémi,
>        Thanks for the help here.
> Got it solved based on your suggestions through both SQL and plpgsql.
> So good to learn!!
>
> Regards,
>
> Paul
>
> ------------------------------
> Date: Mon, 24 Nov 2014 19:34:58 +0100
> From: remi.cura at gmail.com
> To: postgis-users at lists.osgeo.org
> Subject: Re: [postgis-users] PLPGSQL Function to Calculate Bearing
>
>
> Hey,
> first you don't need plpgsql for this, you can do it with windows
> functions (
> http://www.postgresql.org/docs/current/static/functions-window.html)
>
> for the following I assume you have some order available on your point
> data, the order is stored in "id" column :
>
> CREATE TABLE my_azimuth_data AS
> SELECT id, wgs_geom --, ST_X(wgs_geom), ST_Y(wgs_geom), ST_Z(wgs_geom) --
> X,Y,Z seems unecessary
> FROM points_table
> WHERE ST_Within(points_table.wgs_geom, ST_GeomFromtext('POLYGON...',4326)
> = TRUE
> ORDER BY id ASC ;
>
> SELECT *, ST_Azimuth(wgs_geom,lead(wgs_geom) OVER (ORDER BY id ASC)  ) AS
> my_azimuth
> FROM my_azimuth_data
> ORDER BY id ASC
>
> --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...
>
> now if you really want to use plpgsql :
>
>     DROP FUNCTION IF EXISTS get_bearings_from_points();
>     CREATE OR REPLACE FUNCTION get_bearings_from_points()
>     RETURNS  TABLE(id int, ogeom geometry, my_azimuth double) AS
>     \$BODY\$
>     DECLARE
>         r record ; --generic type, will hold any row
>         old_point geometry := NULL; --we store the value of previous
> geometry
>     BEGIN
>         FOR r IN
>             SELECT *
>             FROM my_azimuth_data --loop on the point
>             ORDER BY id ASC
>         LOOP
>             --note : you may want to deal with the first row in a peculiar
> fashion
>             id := r.id; --filling the output row
>             ogeom:= r.wgs_geom ;
>             my_azimuth := ST_Azimuth(old_point, r.wgs_geom) ;
>             old_point := r.wgs_geom ; --updating the old_point with new
> value for next iteration
>         RETURN NEXT; --outputing the row
>         END LOOP;
>
>         RETURN;
>     END
>     \$BODY\$
>     LANGUAGE plpgsql;
>
> If you are more familiar with python you may benefit from using pl/pythonu.
>
> Cheers,
> Rémi-C
>
> 2014-11-24 18:33 GMT+01:00 Paul & Caroline Lewis <paulcaz80 at hotmail.com>:
>
> Basically I can't get my head around the syntax of plpgsql and would
> appreciate some help with the following efforts.
> 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:
>
>     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)
>
> 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.
> 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.
> However my efforts at understanding basic data access and handling within
> a plpgsql function are failing miserably.
>
> An example of the current version of the function I'm trying to create is
> as follows:
>
>     CREATE TYPE bearing_type AS (x numeric, y numeric, z numeric, bearing
> numeric);
>     DROP FUNCTION IF EXISTS get_bearings_from_points();
>     CREATE OR REPLACE FUNCTION get_bearings_from_points()
>     RETURNS SETOF bearing_type AS
>     \$BODY\$
>     DECLARE
>         rowdata points_table%rowtype;
>         returndata bearing_type;
>     BEGIN
>         FOR rowdata IN
>             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)
>         LOOP
>             returndata.x := ST_X(rowdata.wgs_geom);
>             returndata.y := ST_Y(rowdata.wgs_geom);
>             returndata.z := ST_Z(rowdata.wgs_geom);
>             returndata.bearing := ST_Azimuth(<current_point> ,
> <next_point>)
>         RETURN NEXT returndata;
>         END LOOP;
>         RETURN;
>     END
>     \$BODY\$
>     LANGUAGE plpgsql;
>
> I should just be able to call this function as follows:
>
>     SELECT get_bearings_from_points();
>
> and get the desired result.
> Basically the problems are understanding how to access the rowdata
> properly such that I can read the current and next points.
>
> 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.
>
> Thanks
>
> Paul
>
>
> Any insights/help would be much appreciated
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________ postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20141125/88570d6a/attachment.html>
```