[postgis-users] PLPGSQL Function to Calculate Bearing
Max Demars
burton449geo at gmail.com
Tue Nov 25 10:39:38 PST 2014
Im happy for both of you!
On Tue, Nov 25, 2014 at 1:33 PM, Rémi Cura <remi.cura at gmail.com> wrote:
> 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
>>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
--
Stack Overflow: http://stackoverflow.com/users/1914034/burton449
GIS Overflow: http://gis.stackexchange.com/users/14426/burton449
LastFm: http://www.lastfm.fr/user/burton449
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20141125/577e2a30/attachment.html>
More information about the postgis-users
mailing list