OraFAQ Forum: Spatial » SDO Geometry Query

Home » Server Options » Spatial » SDO Geometry Query (Oracle SQL Developer 3.0.04)

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

SDO Geometry Query [message #553611]

Mon, 07 May 2012 04:21

boba
Messages: 6
Registered: May 2012

Junior Member

Hi there

Is there a special Add-in for SDO Queries in Oracle SQL Developer? The reason why I ask:
When I enter this query into the Worksheet:

SELECT l.nummer, SDO_GEOM.SDO_LENGTH(l.geometrie, 0.005) FROM tableL l

i get:

NUMMER SDO_GEOM.SDO_AREA(L.GEOMETRIE,0.005)
--------- ------------------------------------
777

The tableL has a valid SDO Geometry. My co-worker is using the same SQL Developer Version, the same database and the same DB-User and gets a result for SDO_GEOM.SDO_AREA. Do I have to enable a function?

This is the l.geometrie (X and Y are numeric):
MDSYS.SDO_GEOMETRY(3003,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1005,1,1,2,1),MDSYS.SDO_ORDINATE_ARRAY(X1,Y1,0,X2,Y2,0,X3,Y3,0...))

Thank you for your help
Barbara

Report message to a moderator

Re: SDO Geometry Query [message #553731 is a reply to message #553611]

Tue, 08 May 2012 03:39

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

Senior Member

What do you get when you run the query from SQL*Plus?

Report message to a moderator

Re: SDO Geometry Query [message #553739 is a reply to message #553611]

Tue, 08 May 2012 04:17

boba
Messages: 6
Registered: May 2012

Junior Member

I get the same output in SQL*Plus:

NUMMER SDO_GEOM.SDO_LENGTH(L.GEOMETRIE,0.005)
----------- ---------------------------------------
777

I also executed the query in SQLPlus Worksheet. It's always the same output. I also tried other SDO Queries bu none of them worked.

Report message to a moderator

Re: SDO Geometry Query [message #553741 is a reply to message #553739]

Tue, 08 May 2012 04:31

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

Senior Member

Then that indicates that your problem is not specific to SQL*Developer, so I will move your post from the Client Tools sub-forum to the Spatial sub-forum.

Report message to a moderator

Re: SDO Geometry Query [message #553742 is a reply to message #553741]

Tue, 08 May 2012 04:36

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

Senior Member

Can you provide a complete reproducible test case or test the one that I have provided below? We need to see a copy and paste of a complete run from SQL*Plus with line numbers and results, as in the execution I have posted below. I provided the script for you to copy and paste and the execution separately. Make sure that you either run it in a separate schema from your actual data or change the table name.

-- test script:

CREATE TABLE tableL
  (nummer     NUMBER,
   geometrie  SDO_GEOMETRY)
/
INSERT INTO tableL VALUES 
  (777, 
   SDO_GEOMETRY
     (3003, NULL, NULL, 
      SDO_ELEM_INFO_ARRAY (1, 1005, 1, 1, 2, 1), 
      SDO_ORDINATE_ARRAY (1, 1, 0, 2, 2, 0, 3, 3, 0)))
/
SELECT l.nummer, SDO_GEOM.SDO_LENGTH(l.geometrie, 0.005) FROM tableL l
/

-- execution of test script:

SCOTT@orcl_11gR2> CREATE TABLE tableL
  2    (nummer	   NUMBER,
  3  	geometrie  SDO_GEOMETRY)
  4  /

Table created.

SCOTT@orcl_11gR2> INSERT INTO tableL VALUES
  2    (777,
  3  	SDO_GEOMETRY
  4  	  (3003, NULL, NULL,
  5  	   SDO_ELEM_INFO_ARRAY (1, 1005, 1, 1, 2, 1),
  6  	   SDO_ORDINATE_ARRAY (1, 1, 0, 2, 2, 0, 3, 3, 0)))
  7  /

1 row created.

SCOTT@orcl_11gR2> SELECT l.nummer, SDO_GEOM.SDO_LENGTH(l.geometrie, 0.005) FROM tableL l
  2  /

    NUMMER SDO_GEOM.SDO_LENGTH(L.GEOMETRIE,0.005)
---------- --------------------------------------
       777                             2.82842712

1 row selected.

Report message to a moderator

Re: SDO Geometry Query [message #553743 is a reply to message #553742]

Tue, 08 May 2012 04:55

boba
Messages: 6
Registered: May 2012

Junior Member

I executed your code step by step here's the output from SQL*Plus (Release 10.2.0.4.0) (The DB is Oracle 11g)

SQL> CREATE TABLE tableL
  2    (nummer     NUMBER,
  3     geometrie  SDO_GEOMETRY)
  4  ;

Tabelle wurde erstellt. (Table created)

SQL> INSERT INTO tableL VALUES 
  2    (777, 
  3     SDO_GEOMETRY
  4       (3003, NULL, NULL, 
  5        SDO_ELEM_INFO_ARRAY (1, 1005, 1, 1, 2, 1), 
  6        SDO_ORDINATE_ARRAY (1, 1, 0, 2, 2, 0, 3, 3, 0)))
  7  ;

1 Zeile wurde erstellt. (1 row created)

SQL> SELECT l.nummer, SDO_GEOM.SDO_LENGTH(l.geometrie, 0.005) FROM tableL l;

    NUMMER SDO_GEOM.SDO_LENGTH(L.GEOMETRIE,0.005)
---------- --------------------------------------
       777

SQL> select * from tableL;

    NUMMER
----------
GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
       777
SDO_GEOMETRY(3003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1005, 1, 1, 2, 1), SDO_ORD
INATE_ARRAY(1, 1, 0, 2, 2, 0, 3, 3, 0))

I appreciate your help because i have absolutely no idea why it doesn't work.

Report message to a moderator

Re: SDO Geometry Query [message #553745 is a reply to message #553743]

Tue, 08 May 2012 05:05

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

Senior Member

From SQL*Plus, please issue:

SET NULL NULL

then re-run the query and post the results. I want to confirm if what we are seeing is a null value or something else.

Report message to a moderator

Re: SDO Geometry Query [message #553748 is a reply to message #553743]

Tue, 08 May 2012 05:13

_jum
Messages: 577
Registered: February 2008

Senior Member

I can confirm your problem in ORACLE 11.2.0.2.0


SET NULL NULL

SELECT SDO_GEOM.SDO_LENGTH(   
  SDO_GEOMETRY(3003, NULL, NULL, 
         SDO_ELEM_INFO_ARRAY (1, 1005, 1, 1, 2, 1), 
         SDO_ORDINATE_ARRAY (1, 1, 0, 2, 2, 0, 3, 3, 0))
, 0.005) l3d FROM dual;

       L3D
----------
NULL      
1 row selected.

It works fine in ORACLE 10.2.0.4.0

SELECT SDO_GEOM.SDO_LENGTH(   
  SDO_GEOMETRY(3003, NULL, NULL, 
         SDO_ELEM_INFO_ARRAY (1, 1005, 1, 1, 2, 1), 
         SDO_ORDINATE_ARRAY (1, 1, 0, 2, 2, 0, 3, 3, 0))
, 0.005) l3d FROM dual;

       L3D
----------
2,82842712
1 row selected

[Updated on: Tue, 08 May 2012 05:16]

Report message to a moderator

Re: SDO Geometry Query [message #553749 is a reply to message #553748]

Tue, 08 May 2012 05:20

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

Senior Member

I am using 11.2.0.1.0. I wonder if it is just a bug in some versions or whether it is related to something else. I experimented with different nls_numeric_characters, but that didn't affect it. I can't think what else to check.

Report message to a moderator

Re: SDO Geometry Query [message #553753 is a reply to message #553749]

Tue, 08 May 2012 05:44

_jum
Messages: 577
Registered: February 2008

Senior Member

It depends of the SRID, if SRID is NULL (or coordinate system is PROJECTED !?) you can't calculate 3D-length in 11.2.0.2.0:

--11.2.0.2.0
SET NULL NULL

SELECT SDO_GEOM.SDO_LENGTH(   
  SDO_GEOMETRY(3002, NULL , NULL, 
         SDO_ELEM_INFO_ARRAY (1, 2, 1), 
         SDO_ORDINATE_ARRAY (0, 0, 0, 1, 1, 1))
, 0.005) l3d FROM dual;

       L3D
----------
NULL      
1 row selected.

SELECT SDO_GEOM.SDO_LENGTH(   
  SDO_GEOMETRY(3002, 2 , NULL, 
         SDO_ELEM_INFO_ARRAY (1, 2, 1), 
         SDO_ORDINATE_ARRAY (0, 0, 0, 1, 1, 1))
, 0.005,NULL, NULL) l3d FROM dual;

       L3D
----------
,024682056
1 row selected.

--10.2.0.4.0
SET NULL NULL

SELECT SDO_GEOM.SDO_LENGTH(   
  SDO_GEOMETRY(3002, NULL , NULL, 
         SDO_ELEM_INFO_ARRAY (1, 2, 1), 
         SDO_ORDINATE_ARRAY (0, 0, 0, 1, 1, 1))
, 0.005) l3d FROM dual;

       L3D
----------
1,41421356
1 row selected.

SELECT SDO_GEOM.SDO_LENGTH(   
  SDO_GEOMETRY(3002, 2 , NULL, 
         SDO_ELEM_INFO_ARRAY (1, 2, 1), 
         SDO_ORDINATE_ARRAY (0, 0, 0, 1, 1, 1))
, 0.005,NULL) l3d FROM dual;

       L3D
----------
,024682056
1 row selected.

But the value 1,41421356 is incorrect - only 2D calculation from (0,0,0) to (1,1,0)

Report message to a moderator

Re: SDO Geometry Query [message #553754 is a reply to message #553749]

Tue, 08 May 2012 05:46

boba
Messages: 6
Registered: May 2012

Junior Member

I used the original query. But think that this shouldn't matter. The empty Space is a NULL value:

NUMMER       SDO_GEOM.SDO_LENGTH(L.GEOMETRIE,0.005)
------------ --------------------------------------
777          NULL

I'll ask my co-worker about the Oracle version and check if we really have the same version.

Report message to a moderator

Re: SDO Geometry Query [message #553782 is a reply to message #553754]

Tue, 08 May 2012 08:50

boba
Messages: 6
Registered: May 2012

Junior Member

Well was a stupid idea. of course is the DB version the same when we use the same DB.

C:\Oracle\Ora102\sqlplus

Does anybody knows something about the important files in this folder... The path sounds important for my problem...

Report message to a moderator

Re: SDO Geometry Query [message #553833 is a reply to message #553782]

Tue, 08 May 2012 12:54

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

Senior Member

Please verify that the problem is only when there is a null srid, as jum suspects, by running the two queries that he provided below from SQL*Plus, and seeing if you get a null value for the first, but not for the second.

SET NULL NULL

SELECT SDO_GEOM.SDO_LENGTH(   
  SDO_GEOMETRY(3002, NULL , NULL, 
         SDO_ELEM_INFO_ARRAY (1, 2, 1), 
         SDO_ORDINATE_ARRAY (0, 0, 0, 1, 1, 1))
, 0.005) l3d FROM dual;

SELECT SDO_GEOM.SDO_LENGTH(   
  SDO_GEOMETRY(3002, 2 , NULL, 
         SDO_ELEM_INFO_ARRAY (1, 2, 1), 
         SDO_ORDINATE_ARRAY (0, 0, 0, 1, 1, 1))
, 0.005,NULL, NULL) l3d FROM dual;

Report message to a moderator

Re: SDO Geometry Query [message #553869 is a reply to message #553833]

Wed, 09 May 2012 00:36

boba
Messages: 6
Registered: May 2012

Junior Member

I can verify it. But should't it be possible to run a spatial query without a coordinate-system? why can my co-worker run the absolutely same query for the same DB-User on his computer? Does this mean that I have to do an update first or get a substr with some instr and replaces?

SQL> SET NULL NULL;
SQL> SELECT SDO_GEOM.SDO_LENGTH(   
  2    SDO_GEOMETRY(3002, NULL , NULL, 
  3           SDO_ELEM_INFO_ARRAY (1, 2, 1), 
  4           SDO_ORDINATE_ARRAY (0, 0, 0, 1, 1, 1))
  5  , 0.005) l3d FROM dual;

       L3D
----------
NULL

SQL> SELECT SDO_GEOM.SDO_LENGTH(   
  2    SDO_GEOMETRY(3002, 2 , NULL, 
  3           SDO_ELEM_INFO_ARRAY (1, 2, 1), 
  4           SDO_ORDINATE_ARRAY (0, 0, 0, 1, 1, 1))
  5  , 0.005,NULL, NULL) l3d FROM dual;

       L3D
----------
.024682056

Yes and the coordinatesystem is a projected one (SRID 21781). All the z-values in my geometry are zero.

[Updated on: Wed, 09 May 2012 00:41]

Report message to a moderator

Previous Topic:	How to convert oracle table data to shape file
Next Topic:	merge geometry lines

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Oct 31 03:22:01 CDT 2025