PLPGSQL函数来计算方位 [英] PLPGSQL Function to Calculate Bearing
问题描述
基本上,我无法理解plpgsql的语法,并且希望在以下工作中有所帮助. 我有一个包含1000的wgs84点的表.以下SQL将在此表的边界框中检索一组点:
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 1.5059743629,
-1.73591122397 51.5061067655,-1.73548743495 51.5062838333,-1.73533186682
1.5061514313,-1.73576102027 51.5059743629))', 4326, 27700)
) AS bgeom
) AS t2
ON ST_Within(local_geom, t2.bgeom)
我需要做的是在结果中添加一个方位/方位角列,该列描述返回数据集中每个点的方位. 因此,我尝试实现的方法是构建一个plpgsql函数,该函数可以按上述方式选择数据并计算循环中每组点之间的方位. 但是,我在理解plpgsql函数中的基本数据访问和处理方面的努力却以失败告终.
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
3.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 would like to just 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.
在上面的示例中,我遇到了各种问题,例如如何调用ST_X等SQL函数,并尝试了EXECUTE select语句,这些错误带有关于几何数据类型的错误.
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.
任何见解/帮助将不胜感激.
Any insights/help would be much appreciated.
推荐答案
在PL/pgSQL中,一次完成尽可能多的基本SQL查询操作是最有效的.您可以大大简化.
In PL/pgSQL it's most effective to do as much as is elegantly possible in basic SQL queries at once. You can largely simplify.
我没有从您的问题中得到排序顺序的定义,而是留下???
替您填写:
I didn't get a definition of the sort order out of your question and left ???
to fill in for you:
CREATE OR REPLACE FUNCTION get_bearings_from_points(_bgeom geometry)
RETURNS TABLE (x numeric, y numeric, z numeric, bearing numeric) AS
$func$
BEGIN
FOR x, y, z, bearing IN
SELECT ST_X(t.wgs_geom), ST_Y(t.wgs_geom), ST_Z(t.wgs_geom)
, ST_Azimuth(t.wgs_geom, lead(t.wgs_geom) OVER (ORDER BY ???))
FROM points_table t
WHERE ST_Within(t.local_geom, _bgeom)
ORDER BY ???
LOOP
RETURN NEXT;
END LOOP;
END
$func$ LANGUAGE plpgsql;
窗口函数
The window function lead()
references a column from the next row according to sort order.
这可以简化为单个SQL查询-可能包装为SQL函数:
This can be simplified further to a single SQL query - possibly wrapped into an SQL function:
CREATE OR REPLACE FUNCTION get_bearings_from_points(_bgeom geometry)
RETURNS TABLE (x numeric, y numeric, z numeric, bearing numeric) AS
$func$
SELECT ST_X(t.wgs_geom), ST_Y(t.wgs_geom), ST_Z(t.wgs_geom)
, ST_Azimuth(t.wgs_geom, lead(t.wgs_geom) OVER (ORDER BY ???))
FROM points_table t
WHERE ST_Within(t.local_geom, $1) -- use numbers in pg 9.1 or older
ORDER BY ???
$func$ LANGUAGE sql;
参数名称可以在pg 9.2或更高版本中引用. 第9.2页的发行说明: >
Parameter names can be referenced in pg 9.2 or later. Per release notes of pg 9.2:
允许SQL语言函数按名称引用参数(马修 德雷珀(Draper)
Allow SQL-language functions to reference parameters by name (Matthew Draper)
这篇关于PLPGSQL函数来计算方位的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!