Function to extract the sdo_elem_info from a geometry. - Spatial

Hi
Is there a function similar to the sdo_util.getvertices for extracting the SDO_ELEM_INFO from a geometry?
Thanks
Jackie

Hi,
There isn't. You could write your own. If you did, it might look something like this:
create or replace type elem_info_type
AS OBJECT (
OFFSET NUMBER,
ETYPE NUMBER,
INTERP NUMBER)
/
create or replace type elem_info_set_type
as varray(349525) of elem_info_type
/
create or replace function get_elem_info (geom mdsys.sdo_geometry)
return elem_info_set_type is
ogg mdsys.sdo_geometry := geom;
ii number := 1;
eist elem_info_set_type;
eioffset number;
begin
eist := elem_info_set_type();
while ii < ogg.sdo_elem_info.count
loop
eioffset := (ii+2)/3;
eist.extend;
eist(eioffset) := elem_info_type(null,null,null);
eist(eioffset).offset := ogg.sdo_elem_info(ii);
eist(eioffset).etype := ogg.sdo_elem_info(ii+1);
eist(eioffset).interp := ogg.sdo_elem_info(ii+2);
ii := ii + 3;
end loop;
return eist;
end;
/
and call it like this:
select state, gei.offset, gei.etype, gei.interp
from geod_states, table(get_elem_info(geom)) gei
where state='Florida';

Related

Calling function in data model

Hello All,
I am having 1 issues in a report.
Here is what i am doing.
I created a type of varchar2 and created a function which will return me the output of type.
this runs fine in sqlplus but when i put the same query in data model and join another set of tables,the data model is not able to determine the output of the colums.It shoes me a question mark and not the varchar field.
Due to this my generation to xml is failing.
Pls help
Thanks
Saurabh 
could you post your function definition and your query-definition from reports 
TYPE CREATED.
create or replace type str2tblType as table of varchar2(4000);
FUNCTION CREATED.
create or replace
function str2tbl
(date_from in number,date_to in number,v_inter number)
return str2tblType
as
d varchar2(100);
l_data str2tblType := str2tblType();
v_date_from varchar2(100);
v_date_to varchar2(100);
begin
v_date_from := date_from/3600;
v_date_to := date_to/3600;
IF INSTR(v_date_from,'.')=0 THEN
v_date_from := v_date_from||':00';
ELSE
v_date_from := SUBSTR(v_date_from,1,INSTR(v_date_from,'.')-1)||':'||(v_date_from - SUBSTR(v_date_from,1,INSTR(v_date_from,'.')-1)) * 60 ;
END IF;
IF INSTR(v_date_to,'.')=0 THEN
v_date_to := '0'||v_date_to||':00';
ELSE
v_date_to := SUBSTR(v_date_to,1,INSTR(v_date_to,'.')-1)||':'||(v_date_to - SUBSTR(v_date_to,1,INSTR(v_date_to,'.')-1)) * 60 ;
END IF;
d := v_date_from;
WHILE d!=v_date_to
loop
l_data.extend;
d:= to_char(to_date(d,'HH24:MI')+v_inter/60/24,'HH24:MI');
l_data(l_data.count) :=d;
end loop;
return l_data;
end;
/
SELECT QUERY:
select * from TABLE(cast( str2tbl(36000,21600,20) as str2TblType ) ) 
i think the problem is the SELECT *. I don't remember the exact name of the result of a "flat" table put it was something like SELECT COLVALUE or SELECT COLUMNVALUE. To check it you could either do a desc on your type in sql*plus or just query it and see what header the output gets.

Update sdo_elem_info

Hello,
I need to update the sdo_elem_info_array in several spatial
tables.
I am currently using the following sql to do it:
CREATE or REPLACE FUNCTION
set_elem_info(geom mdsys.sdo_geometry)
RETURN mdsys.sdo_geometry IS
geom_copy mdsys.sdo_geometry := geom;
BEGIN
geom_copy.sdo_elem_info(1) := 1;
geom_copy.sdo_elem_info(1) := 1003;
geom_copy.sdo_elem_info(1) := 1;
RETURN geom_copy;
END;
/
which is executed by:
create or replace procedure elem_info_proc as
myGeomIn MDSYS.SDO_GEOMETRY := null;
CURSOR GPS_CURSOR IS
SELECT geometry,object_id FROM MAURY_PARCELS;
myGPSCURSOR GPS_CURSOR%ROWTYPE;
begin
FOR myGPSCURSOR IN GPS_CURSOR LOOP
myGeomIn := null;
myGeomIn := myGPSCURSOR.geometry;
myGeomIn.sdo_elem_info(1) := 1;
myGeomIn.sdo_elem_info(2) := 1003;
myGeomIn.sdo_elem_info(3) := 1;
insert into maury_parcels_copy (GEOMETRY,OBJECT_ID) values
(myGeomIn,myGPSCURSOR.object_id);
end loop;
End;
/
Does anyone have an easier way? I'm on 8.1.7.
Thanks
Dave 
Sorry for the confusion.
Only the second procedure is being executed.
Dave 
Hi David,
I think you are updating all the rows in the table. If so, I'd
drop the spatial index if it exists and try:
update spatial_table a
set
a.spatial_column.sdo_elem_info=mdsys.sdo_elem_info_array(1,1003,1
);
Hope this helps,
Dan

Plsql reading sdo_ordinates

I am having problems returning several rows from my spatial table called shape. I am using Plsql and it works for returning one row.
CREATE OR REPLACE PACKAGE sal_test as
FUNCTION insert_test return varchar2;
END sal_test;
/
CREATE OR REPLACE PACKAGE BODY sal_test AS
FUNCTION insert_test RETURN varchar2
IS
     str_ords varchar2(40);
     CURSOR c1 is select a.shape ords from mzdbms_element1 a;
     xcoord number(15,4) :=1;
     ycoord number(15,4);
     i number(30) :=1;     
     sandra c1%ROWTYPE;
BEGIN
for c1_rec in c1 loop
     while (i<c1_rec.ords.sdo_ordinates.count) loop
     xcoord:=c1_rec.ords.sdo_ordinates(i);
     i:=i+1;
     ycoord:=c1_rec.ords.sdo_ordinates(i);
     if (i = 2) then
     str_ords:=str_ords||xcoord||','||ycoord;
     else
     str_ords:=str_ords||','||xcoord||','||ycoord;
     exit;
     end if;
     i:=i+1;
     end loop;
exit;
END LOOP;
RETURN str_ords;
END insert_test;
END sal_test;
/
What am I doind wrong?
TIA
Sandra 
I have this above problem sorted out now.
Now my problem is
What TYPE would I define for sdo_ordinates. I have tried
create type joseph as varray(5) of varchar2(5); and
create type joseph as varray(5) of number(5); and
create type joseph as table of varchar2(5); and
create type joseph as table of number(5);
with the following PLSQL
CREATE OR REPLACE PACKAGE sal_test as
FUNCTION insert_test return varchar2;
END sal_test;
/
CREATE OR REPLACE PACKAGE BODY sal_test AS
FUNCTION insert_test RETURN varchar2
IS
     str_ords varchar2(40):='A';
     CURSOR c1 is select a.shape ords from mzdbms_element1 a;
     --CURSOR c1 is select sal from temp;
     xcoord number(15,4) :=1;
     ycoord number(15,4);
     i number(30) :=1;
     j number:=1;     
     stock joseph:=joseph();     
     
BEGIN
     open c1;
     loop
     stock.EXTEND;
     fetch c1 into stock(j);
     EXIT WHEN c1%NOTFOUND;
     str_ords:=str_ords||stock(j);
     j:=j+1;
end loop;
     
close c1;
RETURN str_ords;
END insert_test;
END sal_test;
/
The gives me compilation errors as my type doesn't match what is been returned in my cursor.
So does anyone know what TYPE I would set to return sdo_ordinates from a cursor?
Regards 
I'm not quite sure what you are trying to do.
If I assume that shape is an mdsys.sdo_geometry column in the following:
CURSOR c1 is select a.shape ords from mzdbms_element1 a;
then I might define a geom column:
stock mdsys.sdo_geometry
after the:
fetch c1 into stock;
which will load the entire sdo_ordinate array for that geometry
I might do something like this:
i := 0;
while (i < stock.sdo_ordinates.count) LOOP
i := i + 1;
str_ords:=str_ords||stock.sdo_ordinates(j);
end loop;

How to find LRS shape point?

Hello,
I have defined a 2d line which consists of connected segments, using the lrs measure as 3rd dimension. Is it possible, for a given point along the line (which is given by its measure), to obtain the long/lat coordinates of the next shape point in the direction of the line? without having to "manually" walk through the whole VARRAY of ordinates for this geometry?
I hope you understood what I mean! Thank you for suggestions!
Regards,
Bernhard 
Hello,
There is no spatial function to this for you. Here is a quick one you can create or modify
if it doesn't work out for you:
create or replace function next_shp_point (lrs_geom mdsys.sdo_geometry,measure number)
return mdsys.sdo_geometry deterministic is
lrs_srid number;
long1 number;
lat1 number;
i number;
cur_measure number;
shp_geom mdsys.sdo_geometry;
meas_found number := 0;
meas_ordinate number;
begin
lrs_srid := lrs_geom.sdo_srid;
i := 3;
while i <= lrs_geom.sdo_ordinates.count
loop
if lrs_geom.sdo_ordinates(i) < measure
then
i := i + 3;
else
meas_ordinate := i;
i := lrs_geom.sdo_ordinates.count+1;
meas_found := 1;
end if;
end loop;
if meas_found = 1
then
long1 := lrs_geom.sdo_ordinates(meas_ordinate-2);
lat1 := lrs_geom.sdo_ordinates(meas_ordinate-1);
cur_measure := lrs_geom.sdo_ordinates(meas_ordinate);
return mdsys.sdo_geometry (3301, LRS_SRID, NULL, -- change 3301 to 3001 if pre9i
MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1),
MDSYS.SDO_ORDINATE_ARRAY(long1,lat1,cur_measure));
else
return null;
end if;
END;
/
select next_shp_point(mdsys.sdo_geometry(3302,32775,null,
mdsys.sdo_elem_info_array(1,2,1),
mdsys.sdo_ordinate_array(0,1,0,2,1,2,4,1,4,6,1,6,8,1,8,10,1,10)),3)
from dual;
Hi Daniel,
thank you for your help! I knew the PL/SQL solution, I just wanted to know if there is a way which would maybe more performant.
Regards
Bernhard

Source Code of SDO_UTIL.GETVERTICES()

Hi,
where can I find the source code of the aforementioned function? I'm especially interested in the way it gives back the output. I mean how works it together with the table statement. Example:
SELECT col1, t.X, t.Y
FROM table(SDO_UTIL.GETVERTICES(col2)) t;
How does this work? I need to emulate the behavior on a 8.1.7 because the function belongs to spatial option since the 9.2 release.
regards
Marco 
Ask Dusan Jovanovic: he sad that he've installed OAS9r2, so he could send You package header script. :) 
Hi!
Another way: directly simulate this function, returning nested table by per-element scanning of SDO_GEOMETRY.SDO_ORDINATES and returning one-object-column nested table.
I'm not shure for correct PL-code and is it usability in 8.x at whole, but try something like it.
-- First, You have to simulate MDSYS types
CREATE TYPE My_Vert AS OBJECT
(x NUMBER,
y NUMBER,
z NUMBER,
w NUMBER);
CREATE TYPE My_Vert_Set as TABLE OF My_Vert;
-- And procedure itself
CREATE OR REPLACE PROCEDURE GETVERTICES(
G IN MDSYS.SDO_GEOMETRY
) AS My_Vertices My_Vert_Set;
G_VRT Sdo_Ordinate_Array; -- We may not use it, only for readability
Cnt Number; -- number of dimensions
Dim Number;
i Number;
j Number;
Vrt My_Vert;
BEGIN
G_VRT := G.SDO_ORDINATES;
Dim := G.get_Dims();
Cnt := G_VRT.LAST / Dim;
My_Vertices := My_Vert_Set();
FOR i IN 1..Cnt Loop -- Exact number of points;
Vrt := My_Vert(NUll, NUll,NUll,NUll);
FOR j IN 1..Dim Loop --
CASE j
When 1 then Vrt.X := G_VRT((i-1)*Dim + j);
When 2 then Vrt.Y := G_VRT((i-1)*Dim + j);
When 3 then Vrt.Z := G_VRT((i-1)*Dim + j);
When 4 then Vrt.W := G_VRT((i-1)*Dim + j);
End Case;
End Loop;
My_Vertices.Extend;
My_Vertices(My_Vertices.Last) := Vrt;
End Loop; -- i
END GETVERTICES;
Hi!
First of all I want to apologize for my late posting (I was busy with something else) and thank for your help.
I tried to run your procedure on my server (8.1.7) but it doesn't work for me. It seems to be a problem with the CASE-statement (error PLS-00103). It can only be used in SELECTs but not in PL/SQL in this oracle version. Which version are you using?
I will try to understand what are you doing in this procedure and then try to replace it by a IF-THEN-ELSE.
regards
Marco 
Marco,
Here is the code for it. Note that, this still does not handle all the cases like optimized rectangles, etc.
You need to add code to deal with that.
Also, this uses sdo_geometry member function get_dims to find the dimensions of the geometry.
You need to write code to deal with it in 817 differently as there is not get_dims function in 817.
You need these types first:
drop type vertex_set_type;
drop type vertex_type;
Create type vertex_type as object
(X number,
Y number,
Z number,
W number,
ID number );
/
create type vertex_set_type as TABLE of vertex_type;
/
grant execute on vertex_type to public;
grant execute on vertex_set_type to public;
FUNCTION GetVertices(geometry mdsys.sdo_geometry)
RETURN vertex_set_type IS
i number;
dims number;
coords number;
result vertex_set_type;
dim mdsys.sdo_dim_array;
is_zero boolean;
etype number;
begin
result := vertex_set_type();
-- handle the POINT case here
if (geometry.sdo_ordinates is NULL) then
result.extend;
result(1) := vertex_type(geometry.sdo_point.x, geometry.sdo_point.y,
geometry.sdo_point.z,NULL,1);
return result;
end if;
-- all other cases here
coords := geometry.sdo_ordinates.count;
dims := geometry.get_dims;
if (dims = 0) then
return result;
end if;
coords := coords/dims;
FOR i in 0 .. coords-1 Loop
result.extend;
if (dims = 2) then
result(i+1) := vertex_type(geometry.sdo_ordinates(2*i+1),
geometry.sdo_ordinates(2*i+2), NULL,NULL,i+1);
elsif (dims = 3) then
result(i+1) := vertex_type(geometry.sdo_ordinates(3*i+1),
geometry.sdo_ordinates(3*i+2),
geometry.sdo_ordinates(3*i+3) ,NULL,i+1);
elsif (dims = 4) then
result(i+1) := vertex_type(geometry.sdo_ordinates(4*i+1),
geometry.sdo_ordinates(4*i+2),
geometry.sdo_ordinates(4*i+3),
geometry.sdo_ordinates(4*i+4), i+1);
end if;
END LOOP;
return result;
end;

Categories

Resources