Im happy for both of you! On Tue, Nov 25, 2014 at 1:33 PM, Rémi Cura <[email protected]> wrote:
> Great, I'm happy for you ;-) > Cheers, > Rémi-C > > > 2014-11-25 18:11 GMT+01:00 Paul & Caroline Lewis <[email protected]>: > >> 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: [email protected] >> To: [email protected] >> 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 <[email protected]>: >> >> 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 >> [email protected] >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> >> >> >> _______________________________________________ postgis-users mailing >> list [email protected] >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> > > > _______________________________________________ > postgis-users mailing list > [email protected] > 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
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
