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.
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;