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
