How to loop over a Geometry column data in MySQL / PostgreSQL?

Question

I have a column named schedule with data type MULTILINESTRING.
Almost cases, this column contains any 1 LINESTRING, so I fetch data easily by script

ST_GeometryN(schedule, 1)

and then I check conditions from fetched data.

But if this column contains one more LINESTRING, I need to loop over it to check.

What is the best way to do?
Sorry for my bad English.

Is it mysql or postgresql ? – Daniel E. Feb 27 '18 at 10:25 — Daniel E., Feb 27 '18 at 10:25

Jim Jones · Answer 1 · 2018-02-27T15:00:27.537

PostgreSQL (PostGIS)

The function you're looking for is ST_NumGeometries.

Based on the following table structure:

CREATE TEMPORARY TABLE tb (id INT, schedule GEOMETRY);

Containing the following records:

INSERT INTO tb 
VALUES (1,ST_GEOMFROMTEXT('MULTILINESTRING ((10 10, 20 20, 10 40),(40 40, 30 30, 40 20, 30 10))'));
INSERT INTO tb 
VALUES (2,ST_GEOMFROMTEXT('LINESTRING (30 10, 10 30, 40 40)'));

Iterate over the geometries using something like (quick and dirty):

DO $$
DECLARE 
  i INT DEFAULT 0; 
  j RECORD;
  dim INT DEFAULT 0;  
BEGIN
    FOR j IN SELECT * FROM tb LOOP
      SELECT ST_NumGeometries(j.schedule) INTO dim;
      WHILE i < dim LOOP
        i:=i+1;  
        RAISE NOTICE 'Do something with geometry % : %',j.id,(SELECT ST_ASTEXT(ST_GeometryN(j.schedule, i)) );          
      END LOOP;
      i:=0;
    END LOOP;
END$$;

Output:

Do something with geometry 1 : LINESTRING(10 10,20 20,10 40)
Do something with geometry 1 : LINESTRING(40 40,30 30,40 20,30 10)
Do something with geometry 2 : LINESTRING(30 10,10 30,40 40)

Depending on what you need to do with these linestrings, I would suggest put this code into a function.

How to loop over a Geometry column data in MySQL / PostgreSQL?

1 Answers1