Home » Server Options » Spatial » SDO Geometry Query (Oracle SQL Developer 3.0.04)
SDO Geometry Query [message #553611] Mon, 07 May 2012 04:21 Go to next message
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
Re: SDO Geometry Query [message #553731 is a reply to message #553611] Tue, 08 May 2012 03:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
What do you get when you run the query from SQL*Plus?
Re: SDO Geometry Query [message #553739 is a reply to message #553611] Tue, 08 May 2012 04:17 Go to previous messageGo to next message
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.
Re: SDO Geometry Query [message #553741 is a reply to message #553739] Tue, 08 May 2012 04:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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.
Re: SDO Geometry Query [message #553742 is a reply to message #553741] Tue, 08 May 2012 04:36 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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.



Re: SDO Geometry Query [message #553743 is a reply to message #553742] Tue, 08 May 2012 04:55 Go to previous messageGo to next message
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.
Re: SDO Geometry Query [message #553745 is a reply to message #553743] Tue, 08 May 2012 05:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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.
Re: SDO Geometry Query [message #553748 is a reply to message #553743] Tue, 08 May 2012 05:13 Go to previous messageGo to next message
_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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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.
Re: SDO Geometry Query [message #553753 is a reply to message #553749] Tue, 08 May 2012 05:44 Go to previous messageGo to next message
_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)
Re: SDO Geometry Query [message #553754 is a reply to message #553749] Tue, 08 May 2012 05:46 Go to previous messageGo to next message
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.
Re: SDO Geometry Query [message #553782 is a reply to message #553754] Tue, 08 May 2012 08:50 Go to previous messageGo to next message
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...

Re: SDO Geometry Query [message #553833 is a reply to message #553782] Tue, 08 May 2012 12:54 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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;


Re: SDO Geometry Query [message #553869 is a reply to message #553833] Wed, 09 May 2012 00:36 Go to previous message
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:
  


Current Time: Thu Mar 28 18:12:40 CDT 2024