ST_Distance loop

[المشرف العام]

I want to create a postgresql function which calculates the distances between random points who are stored in one table.

For the Table:

CREATE TABLE s_1(id bigserial primary key,the_geom geometry,descr text);For the points:

INSERT INTO s_1(the_geom)SELECT ST_MakePoint(round(random()*100), round(random()*100))FROM generate_series(1,25);Now I want to create a function which calculate all possible distances between the points and store them in an output tablee.g 1 to 2, 1 to 3,...2 to 3, 2 to 4,... and so on.So the loop should look something like this

i = 1n = max idfor i to n-1 for j to n ST_Distance(id.i, id.j)I'm a beginner with sql and not able to get a loop working so far.

Could you help me create my own function as well. I assume it should look something like this ?!?

CREATE OR REPLACE FUNCTION DIST()

RETURNS AS

$$

CREATE TABLE Distance(

a.id integer,b.id integer,ST_Distance integer);INSERT INTO Distance(a.id,b.id,ST_DISTANCE)

SELECT ST_Distance(a.the_geom, b.the_geom) from s_1 a, s_1 b

WHERE a.id < b.id;

$$

LANGUAGE SQL;

In the table I want to save the 1st id, the 2nd id and the distance between them..Not sure if this is even close :-(



أكثر...