OraFAQ Forum: Spatial » Select geometry column

Home » Server Options » Spatial » Select geometry column (oracle 11gr1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Select geometry column [message #548330]

Wed, 21 March 2012 07:54

katy
Messages: 4
Registered: March 2012
Location: Czech Republic

Junior Member

Hi All!

I need help...

How can I select something from table with a geometry column, how should I specify this column in the query?

I tried this and it didn't work:

select * from a where geometry = MDSYS.SDO_GEOMETRY(2003,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(0,0,1,0,1,1,0,1,0,0));

it says:
ORA-22901: nelze porovnat atributy VARRAY nebo LOB typu objektu
22901. 00000 - "cannot compare nested table or VARRAY or LOB attributes of an object type"
*Cause: Comparison of nested table or VARRAY or LOB attributes of an
object type was attempted in the absence of a MAP or ORDER
method.
*Action: define a MAP or ORDER method for the object type.
Error at Line: 20 Column: 22

I have also a similar problem with another table with a topo geometry column:

select * from a_a_topo where feature = MDSYS.SDO_TOPO_GEOMETRY(3,1,1,22);

it says:
ORA-14551: uvnitř dotazu není možno vykonat operaci DML
ORA-06512: na "MDSYS.SDO_TOPO_GEOMETRY", line 14
14551. 00000 - "cannot perform a DML operation inside a query "
*Cause: DML operation like insert, update, delete or select-for-update
cannot be performed inside a query or under a PDML slave.
*Action: Ensure that the offending DML operation is not performed or
use an autonomous transaction to perform the DML operation within
the query or PDML slave.

Thanks a lot for your help!!!

Report message to a moderator

Re: Select geometry column [message #548344 is a reply to message #548330]

Wed, 21 March 2012 09:01

_jum
Messages: 577
Registered: February 2008

Senior Member

You can't compare SDO_GEOMETRY directly.
What means two lines are equal ?
-all coordinates
-with which tolerance
-in the same direction
...
You could use the function SDO_GEOM.RELATE instead:

SELECT sdo_geom.relate(shape, 'EQUAL', MDSYS.SDO_GEOMETRY(2003,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(0,0,1,0,1,1,0,1,0,0)),0.05)
  FROM cola_markets;

Report message to a moderator

Re: Select geometry column [message #548391 is a reply to message #548344]

Wed, 21 March 2012 15:36

katy
Messages: 4
Registered: March 2012
Location: Czech Republic

Junior Member

Thank You very much for your response.
I didn't know, that I can't ask the geometry column directly.

The problem is, that I need to find out, which shapes (A, B, C or D) have the tg_id = 1 or tg_id = 2. So I wanted to choose:

--tg_id=1
select * from a_a_topo where feature = MDSYS.SDO_TOPO_GEOMETRY(3,1,1,22);
--tg_id=2
select * from a_a_topo where feature = MDSYS.SDO_TOPO_GEOMETRY(3,2,1,22);

this simple table (printscreen) is in attachment, the right answer is shape A and B, but how can I get this answer? Is there any other possibility how to discover it?

Thank You very much!!!

/forum/fa/9979/0/

[mod-edit: jpg image inserted into message body by bb, since many people cannot download such things]

[EDITED by LF: cropped the image]

Attachment: printscreen.jpg
(Size: 20.53KB, Downloaded 7968 times)

[Updated on: Thu, 22 March 2012 01:08] by Moderator

Report message to a moderator

Re: Select geometry column [message #548394 is a reply to message #548391]

Wed, 21 March 2012 17:06

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

Katy,

Welcome to the OraFAQ forums. Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Many people cannot download attachments, so I inserted your file into your post. However, such things cannot be copied and pasted to create test cases. So, in the future, please provide a test case, something like this:

create table a_a_topo
  (id        number,
   nazev     varchar2 (4),
   ab        varchar2 (2),
   feature   mdsys.sdo_topo_geometry)
/
insert all
into a_a_topo values (1, 'A', 'AB', MDSYS.SDO_TOPO_GEOMETRY (3, 1, 1, 22))
into a_a_topo values (2, 'B', 'AB', MDSYS.SDO_TOPO_GEOMETRY (3, 2, 1, 22))
into a_a_topo values (3, 'C', 'CD', MDSYS.SDO_TOPO_GEOMETRY (3, 3, 1, 22))
into a_a_topo values (4, 'D', 'CD', MDSYS.SDO_TOPO_GEOMETRY (3, 4, 1, 22))
select * from dual
/
commit
/

In response to your question, you can select like this:

SCOTT@orcl_11gR2> select a.*
  2  from   a_a_topo a
  3  where  a.feature.tg_id IN (1, 2)
  4  /

        ID NAZE AB
---------- ---- --
FEATURE(TG_TYPE, TG_ID, TG_LAYER_ID, TOPOLOGY_ID)
--------------------------------------------------------------------------------
         1 A    AB
SDO_TOPO_GEOMETRY(3, 1, 1, 22)

         2 B    AB
SDO_TOPO_GEOMETRY(3, 2, 1, 22)


2 rows selected.

Report message to a moderator

Re: Select geometry column [message #548677 is a reply to message #548394]

Sat, 24 March 2012 06:02

katy
Messages: 4
Registered: March 2012
Location: Czech Republic

Junior Member

Than You very much for Your responses,
I would like to try it as soon as possible, I can't do it now because another problem appeared..

All columns in the whole database, which store the SDO_TOPO_GEOMETRY, are EMPTY. I don't know what happened with it. When I create new topology (I created it from spatial geometries), this column in the new topology is also empty. The tables like EDGE, NODE, FACE, HISTORY and RELATION tables, have data; the tables which store the SDO_GEOMETRY are also OK. Just the SDO_TOPO_GEOMETRY column is empty.. Also when I make a query for this column (like GET_TOPO_OBJECT function), the result is also empty. But it looks like that the data ARE there, because when I make an export from this table, the data are there. It looks like the data are not visible (also the result of the query is not visible..?). I don't know what to do it with it, how to repair it, I'm trying something already three days and without solution..

$C:\empty.jpg$

It looks like it is since I installed Georaptor, but I don't know, if it can have something to do with it? (it is strange)

Does anybody have some idea, how to repair it, what could happen? I appreciate Your help!!

Thank You all, Katy

Report message to a moderator

Re: Select geometry column [message #548678 is a reply to message #548677]

Sat, 24 March 2012 06:25

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

Your image did not display. You need to upload the file, then select the option to insert the image. However, as stated before, you should not be posting an image anyhow. You should post a copy and paste of your select statement run from SQL*Plus, as I previously demonstrated. You need to read and follow the guidelines that I previously directed you to. You need to post a copy and paste of a run of the SQL query that is not producing expected results.

Report message to a moderator

Re: Select geometry column [message #549143 is a reply to message #548678]

Wed, 28 March 2012 05:14

katy
Messages: 4
Registered: March 2012
Location: Czech Republic

Junior Member

Dear All,
thank You very much for Your advices, it helped me a lot!!

The problem with empty feature column (column with SDO_TOPO_GEOMETRY)was really in the Georaptor, I uninstalled it and since than it runs without problems. What a pity, that I can't use it together with my topology data, so I can't visualizate my SDO_GEOMETRY...

(I'm sorry for the images, that I couldn't insert directly, so I used an attachment.. )

Thank You, Katy

Report message to a moderator

Previous Topic:	Introduction to Oracle Spatial
Next Topic:	How to move sdo_geometry type index?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 06:52:18 CST 2024