Create line geometry from lat long

Question

I need to create a linestring geometry from lat, long values returned by another query.

Values returned from this query

SELECT LATITUDE, LONGITUDE FROM POINTS

should go into the sdo_ordinate_array

sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), sdo_ordinate_array ( 1,1,2,2,3,4 ))

No idea how to convert the resultset into an array.

Answer 1

declare
elem mdsys.sdo_elem_info_array;
ordinates mdsys.sdo_ordinate_array;
srid number := 4326;
gtype number := 2002;

begin

elem:=mdsys.sdo_elem_info_array();
ordinates:=mdsys.sdo_ordinate_array();

elem.EXTEND(3);
elem(1):=1;
elem(2):=2;
elem(3):=1;

FOR cur_rec IN (SELECT LONGITUDE,LATITUDE FROM POINTS) LOOP
ordinates.EXTEND(2);
ordinates(ordinates.last-1):=cur_rec.LONGITUDE;
ordinates(ordinates.last):=cur_rec.LATITUDE;
END LOOP;

INSERT INTO MYLINETABLE (ID,GEOM) VALUES (MYSEQUENCE.NEXTVAL,SDO_UTIL.SIMPLIFY(MDSYS.SDO_GEOMETRY(gtype,srid,null,elem,ordinates),6,0.5));

commit;

end;