Oracle SQL aggregate Geometries (Longitude, Latitude)

Question

I have this table

I wanted to sort by POS ASC and also aggregate the two columns X, and Y (so that these POINTS become a LINESTRING, EPSG:25832) so that my ID becomes Unique.

I don't have much experience with ORACLE SDO GEOMETRIES but with PostGIS. Is there an easy way to do it? Since I didn't find any solution which worked correctly.

The output should be:

Any help maybe..? Thanks.

Answer 1

You can aggregate the coordinates into a string and convert it to an SDO_GEOMETRY type:

SELECT id,
       SDO_GEOMETRY(
         'LINESTRING(' || LISTAGG(x || ' ' || y, ',') WITHIN GROUP (ORDER BY pos) || ')'
       ) AS line
FROM   table_name
GROUP BY id;

However, the string is limited to 4000 characters so it may fail if you have lots of coordinates.

If you just want the string (without converting it to an SDO_GEOMETRY) then remove the call to the SDO_GEOMETRY() constructor and just use LISTAGG on its own.

---

Alternately, you can generate the SDO_GEOMETRY object directly using UNPIVOT and CAST/COLLECT:

SELECT id,
       SDO_GEOMETRY(
         2002,
         NULL,
         NULL,
         SDO_ELEM_INFO_ARRAY(1, 2, 1),
         CAST(COLLECT(value ORDER BY pos, coord) AS SDO_ORDINATE_ARRAY)
       )
FROM   table_name t
UNPIVOT (value FOR coord IN (x AS 1, y AS 2))
GROUP BY id

If you want it as a WKT string then:

SELECT id,
       SDO_GEOMETRY(
         2002,
         NULL,
         NULL,
         SDO_ELEM_INFO_ARRAY(1, 2, 1),
         CAST(
           COLLECT(value ORDER BY pos, coord)
           AS SDO_ORDINATE_ARRAY
         )
       ).get_WKT() AS wkt
FROM   table_name t
UNPIVOT (value FOR coord IN (x AS 1, y AS 2))
GROUP BY id

Which, for the sample data, outputs:

ID	WKT
1	LINESTRING (362019.6 5693216.74, 361967.53 5693180.03)
2	LINESTRING (361993.564 5693198.385)
3	LINESTRING (361993.564 5693198.385)

fiddle

---

In Oracle 11g, if you just want the WKT string then you can create the types and function:

CREATE TYPE coord IS OBJECT(x NUMBER, y NUMBER)
/

CREATE TYPE coord_table IS TABLE OF coord
/

CREATE FUNCTION coords_to_wkt(
  i_coords IN coord_table
) RETURN CLOB
IS
  v_clob CLOB;
BEGIN
  IF i_coords IS NULL OR i_coords.COUNT = 0 THEN
    RETURN NULL;
  END IF;
  v_clob := 'LINESTRING(' || i_coords(1).x || ' ' || i_coords(1).y;
  FOR i IN 2 .. i_coords.COUNT LOOP
    v_clob := v_clob || ',' || i_coords(i).x || ' ' || i_coords(i).y;
  END LOOP;
  RETURN v_clob || ')';
END;
/ 

and use:

SELECT id,
       coords_to_wkt( CAST( COLLECT( coord(x, y) ORDER BY pos ) AS coord_table ) ) AS wkt
FROM   table_name
GROUP BY id;

fiddle