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
