如何选择一个FOR循环内进行进一步的计算？ [英] How to select inside a FOR-Loop for further computations?

问题描述

使用postgresql和postgis我有2个openstreetmap表，其中包含：

1. 点：带有单个坐标的位置


2. 多边形：带有坐标集的区域

现在，我试着循环点表每个记录我试图用postgis函数做一些计算，例如 ST_Intersects（）。然后尝试将结果插入到另一个表中。

到目前为止，我只使用postgis完成了简单的 SELECT 查询函数，他们基本上是这样工作的：

pre code $ SELECT a.name，b.name
FROM table_point AS a，table_polygon AS b
WHERE a.name ='Berlin'AND ST_Intersects（a.way，b.way）;

注意： way 包含几何数据的表。

回到我想要操作的循环，我发现自己缺乏plpgsql基础知识。我创建了以下循环结构，但不知道如何为postgis函数选择第二组记录（ table_point AS a，table_polygon AS b ）。
Short：如何选择 table_polygon 和FOR循环的记录？
或者，有没有更好的方法来解决这个问题？

  DO 
 $ $ 
 
 DECLARE 
r RECORD; 
 
 BEGIN 
对于r IN SELECT * FROM table_point 
 LOOP 
 RAISE NOTICE'％'，r; 
 
 ... 
 
 END LOOP; 
 END; 
 $ do $ LANGUAGE plpgsql 
  

在Ubuntu 14.04 64位上使用PGSQL 9.3.5。

解决方案

您正在程序化地思考，而在大多数情况下，基于集合的方法在关系数据库中是优越的。像：

  INSERT INTO table_other（point，polygon，result）
 SELECT a.name，b.name，calculate （a。？，b。？）结果 -  undefined做什么
 FROM table_point a 
 JOIN table_polygon b ON ST_Intersects（a.way，b.way）
 WHERE a.name ='柏林'; 
  

Working with postgresql and postgis I have 2 openstreetmap tables, containing:

1. Point: Locations with a single coordinate
2. Polygon: Areas with sets of coordinates

Now, I'm trying to loop through the Point table and for each record I'm trying to do some computations with postgis functions, e.g. ST_Intersects(). Then trying to insert the results into another tables.

So far I've only done simple SELECT queries with postgis functions, they basically work like this:

SELECT a.name, b.name
FROM   table_point AS a, table_polygon AS b
WHERE  a.name = 'Berlin' AND ST_Intersects(a.way, b.way);

Note: way is the column in both tables containing geometry data.

Coming back to the loop I want to operate, I find myself with lacking plpgsql basics. I've created the following loop structure, but don't know how to select a second set of records (table_point AS a, table_polygon AS b) for the postgis function. Short: How to select the records for table_polygon along with that FOR-loop? Or, is there a better way to solve this?

DO
$do$

DECLARE
r RECORD;

BEGIN
FOR r IN SELECT * FROM table_point
LOOP
RAISE NOTICE '%', r;

...

END LOOP;
END;
$do$ LANGUAGE plpgsql

Using PGSQL 9.3.5 on Ubuntu 14.04 64-bit.

解决方案

You are thinking procedurally, while for most cases a set-based approach is superior in relational databases. Like:

INSERT INTO table_other (point, polygon, result)
SELECT a.name, b.name, calculate(a.?, b.?) AS result -- undefined what to do
FROM   table_point   a
JOIN   table_polygon b ON ST_Intersects(a.way, b.way)
WHERE  a.name = 'Berlin';

这篇关于如何选择一个FOR循环内进行进一步的计算？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！