[postgis-users] PLPGSQL Function to Calculate Bearing

[Paul & Caroline Lewis]

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 		 	   		  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20141125/32199fd2/attachment.html>