Home » Server Options » Spatial » Using SDO_UTIL.GETVERTICES() to fetch all points
Using SDO_UTIL.GETVERTICES() to fetch all points [message #654896] Tue, 16 August 2016 09:31 Go to next message
moehre
Messages: 43
Registered: June 2016
Member
Hi,

I wanna fetch all coordinates from my oracle database and I tried to use the intern function SDO_UTIL.GETVERTICES()!
This works really fast for my webserver.

Code:
SELECT a.id , t.x, t.y, t.z, d.Classname 
FROM   building a, THEMATIC_SURFACE b, SURFACE_GEOMETRY c, TABLE(SDO_UTIL.GETVERTICES(c.geometry))t, OBJECTCLASS d  
WHERE  a.grid_id_500 = 2728 
AND a.id = b.BUILDING_ID 
AND b.LOD2_MULTI_SURFACE_ID = c.ROOT_ID 
AND c.GEOMETRY IS NOT NULL 
AND b.OBJECTCLASS_ID = d.ID;

result:
__ID______X_________Y_________Z______CLASSNAME____
1314867 3500936,67 5394350,72 439,174 BuildingWallSurface
1314867 3500938,59 5394348,52 439,174 BuildingWallSurface
1314867 3500938,59 5394348,52 458,23 BuildingWallSurface

The problem is the function is used for point-geometries. But in my case I have multipolygons with more than one point!
Is there a possibility to extend this function to fetch all x,y,z values?
Re: Using SDO_UTIL.GETVERTICES() to fetch all points [message #654908 is a reply to message #654896] Tue, 16 August 2016 14:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
moehre wrote on Tue, 16 August 2016 07:31
Hi,

I wanna fetch all coordinates from my oracle database and I tried to use the intern function SDO_UTIL.GETVERTICES()!
This works really fast for my webserver.

Code:
SELECT a.id , t.x, t.y, t.z, d.Classname 
FROM   building a, THEMATIC_SURFACE b, SURFACE_GEOMETRY c, TABLE(SDO_UTIL.GETVERTICES(c.geometry))t, OBJECTCLASS d  
WHERE  a.grid_id_500 = 2728 
AND a.id = b.BUILDING_ID 
AND b.LOD2_MULTI_SURFACE_ID = c.ROOT_ID 
AND c.GEOMETRY IS NOT NULL 
AND b.OBJECTCLASS_ID = d.ID;

result:
__ID______X_________Y_________Z______CLASSNAME____
1314867 3500936,67 5394350,72 439,174 BuildingWallSurface
1314867 3500938,59 5394348,52 439,174 BuildingWallSurface
1314867 3500938,59 5394348,52 458,23 BuildingWallSurface

The problem is the function is used for point-geometries. But in my case I have multipolygons with more than one point!
Is there a possibility to extend this function to fetch all x,y,z values?
It does fetch all coordinates. That is why there are multiple rows for the same id and classname.
There is one row for each set of coordinates.
Re: Using SDO_UTIL.GETVERTICES() to fetch all points [message #654909 is a reply to message #654908] Tue, 16 August 2016 16:36 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
Oh sorry my mistake... You are right.
So this is working...

I have a Webserver which executes the following sql query :
SELECT a.id , t.x, t.y, t.z, d.Classname FROM building a, THEMATIC_SURFACE b, SURFACE_GEOMETRY c, TABLE(SDO_UTIL.GETVERTICES(c.geometry))t, OBJECTCLASS d WHERE a.grid_id_500 = 2728 AND a.id = b.BUILDING_ID AND b.LOD2_MULTI_SURFACE_ID = c.ROOT_ID AND c.GEOMETRY IS NOT NULL AND b.OBJECTCLASS_ID = d.ID

But if I will fetch it I get only one point:
for id,x,y,z, classname in cursor:
result.append({
"building_nr":id,"geometry": {
"type":"polygon","coordinates":[(x,y,z)],}, "polygon_typ":classname,})


This is done with Python + Web.py

result: [{'building_nr': 1314867, 'geometry': {'type': 'polygon', 'coordinates': [(3500936.67, 5394350.72, 439.17400000000004)]}, 'polygon_typ': 'BuildingWallSurface'}, ...

The problem is I get only one point geometry. But for example there exists 5 points for this wall surface. I need all points with the same building_nr (id)!

Re: Using SDO_UTIL.GETVERTICES() to fetch all points [message #654910 is a reply to message #654909] Tue, 16 August 2016 17:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
It appears that you have data something like this:

SCOTT@orcl_12.1.0.2.0> SELECT c.root_id, c.geometry
  2  FROM   surface_geometry c
  3  /

   ROOT_ID
----------
GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
        27
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_AR
RAY(3482967.35, 5376954.8, 521.988, 3482965.61, 5376958.37, 521.988, 3482966.11,
 5376958.61, 521.988, 3482963.9, 5376963.24, 521.988, 3482968.12, 5376965.28, 52
1.988, 3482973.5, 5376954.25, 521.988, 3482970.35, 5376952.78, 521.988, 3482967.
35, 5376954.8, 521.988))

        28
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_AR
RAY(3482973.73, 5376953.75, 521.847, 3482973.5, 5376954.25, 521.847, 3482968.12,
 5376965.28, 521.847, 3482973.01, 5376967.63, 521.847, 3482975.48, 5376962.49, 5
21.847, 3482976.79, 5376963.12, 521.847, 3482979.9, 5376956.67, 521.847, 3482977
.21, 5376955.37, 521.847, 3482976.84, 5376954.15, 521.847, 3482975, 5376953.32,
521.847, 3482973.73, 5376953.75, 521.847))


2 rows selected.

And you are doing something like this:

SCOTT@orcl_12.1.0.2.0> SELECT c.root_id, t.x, t.y, t.z
  2  FROM   surface_geometry c,
  3  	    TABLE (SDO_UTIL.GETVERTICES(c.geometry)) t
  4  /

   ROOT_ID          X          Y          Z
---------- ---------- ---------- ----------
        27 3482967.35  5376954.8    521.988
        27 3482965.61 5376958.37    521.988
        27 3482966.11 5376958.61    521.988
        27  3482963.9 5376963.24    521.988
        27 3482968.12 5376965.28    521.988
        27  3482973.5 5376954.25    521.988
        27 3482970.35 5376952.78    521.988
        27 3482967.35  5376954.8    521.988
        28 3482973.73 5376953.75    521.847
        28  3482973.5 5376954.25    521.847
        28 3482968.12 5376965.28    521.847
        28 3482973.01 5376967.63    521.847
        28 3482975.48 5376962.49    521.847
        28 3482976.79 5376963.12    521.847
        28  3482979.9 5376956.67    521.847
        28 3482977.21 5376955.37    521.847
        28 3482976.84 5376954.15    521.847
        28    3482975 5376953.32    521.847
        28 3482973.73 5376953.75    521.847

19 rows selected.

If you want all of the coordinates in one row, then just do something like this.
You can do whatever formatting to the string that you want.
SCOTT@orcl_12.1.0.2.0> SELECT c.root_id, c.geometry.sdo_ordinates as coordinates
  2  FROM   surface_geometry c
  3  /

   ROOT_ID
----------
COORDINATES
--------------------------------------------------------------------------------
        27
SDO_ORDINATE_ARRAY(3482967.35, 5376954.8, 521.988, 3482965.61, 5376958.37, 521.9
88, 3482966.11, 5376958.61, 521.988, 3482963.9, 5376963.24, 521.988, 3482968.12,
 5376965.28, 521.988, 3482973.5, 5376954.25, 521.988, 3482970.35, 5376952.78, 52
1.988, 3482967.35, 5376954.8, 521.988)

        28
SDO_ORDINATE_ARRAY(3482973.73, 5376953.75, 521.847, 3482973.5, 5376954.25, 521.8
47, 3482968.12, 5376965.28, 521.847, 3482973.01, 5376967.63, 521.847, 3482975.48
, 5376962.49, 521.847, 3482976.79, 5376963.12, 521.847, 3482979.9, 5376956.67, 5
21.847, 3482977.21, 5376955.37, 521.847, 3482976.84, 5376954.15, 521.847, 348297
5, 5376953.32, 521.847, 3482973.73, 5376953.75, 521.847)


2 rows selected.
Re: Using SDO_UTIL.GETVERTICES() to fetch all points [message #654911 is a reply to message #654909] Tue, 16 August 2016 17:35 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
If you have data like this:

SCOTT@orcl_12.1.0.2.0> SELECT grid_id_500, id FROM building
  2  /

GRID_ID_500         ID
----------- ----------
       2728    1314867

1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT building_id, lod2_multi_surface_id, objectclass_id FROM thematic_surface
  2  /

BUILDING_ID LOD2_MULTI_SURFACE_ID OBJECTCLASS_ID
----------- --------------------- --------------
    1314867                    27             35
    1314867                    28             35

2 rows selected.

SCOTT@orcl_12.1.0.2.0> SELECT root_id, geometry FROM surface_geometry
  2  /

   ROOT_ID
----------
GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
        27
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_AR
RAY(3482967.35, 5376954.8, 521.988, 3482965.61, 5376958.37, 521.988, 3482966.11,
 5376958.61, 521.988, 3482963.9, 5376963.24, 521.988, 3482968.12, 5376965.28, 52
1.988, 3482973.5, 5376954.25, 521.988, 3482970.35, 5376952.78, 521.988, 3482967.
35, 5376954.8, 521.988))

        28
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_AR
RAY(3482973.73, 5376953.75, 521.847, 3482973.5, 5376954.25, 521.847, 3482968.12,
 5376965.28, 521.847, 3482973.01, 5376967.63, 521.847, 3482975.48, 5376962.49, 5
21.847, 3482976.79, 5376963.12, 521.847, 3482979.9, 5376956.67, 521.847, 3482977
.21, 5376955.37, 521.847, 3482976.84, 5376954.15, 521.847, 3482975, 5376953.32,
521.847, 3482973.73, 5376953.75, 521.847))


2 rows selected.

SCOTT@orcl_12.1.0.2.0> SELECT id, classname FROM objectclass
  2  /

        ID CLASSNAME
---------- ------------------------------
        35 BuildingWallSurface
        35 BuildingWallSurface

2 rows selected.

And you are doing something like this:

SCOTT@orcl_12.1.0.2.0> SELECT a.id , t.x, t.y, t.z, d.Classname
  2  FROM   building a,
  3  	    THEMATIC_SURFACE b,
  4  	    SURFACE_GEOMETRY c,
  5  	    TABLE(SDO_UTIL.GETVERTICES(c.geometry))t,
  6  	    OBJECTCLASS d
  7  WHERE  a.grid_id_500 = 2728
  8  AND    a.id = b.BUILDING_ID
  9  AND    b.LOD2_MULTI_SURFACE_ID = c.ROOT_ID
 10  AND    c.GEOMETRY IS NOT NULL
 11  AND    b.OBJECTCLASS_ID = d.ID
 12  /

        ID          X          Y          Z CLASSNAME
---------- ---------- ---------- ---------- ------------------------------
   1314867 3482967.35  5376954.8    521.988 BuildingWallSurface
   1314867 3482967.35  5376954.8    521.988 BuildingWallSurface
   1314867 3482965.61 5376958.37    521.988 BuildingWallSurface
   1314867 3482965.61 5376958.37    521.988 BuildingWallSurface
   1314867 3482966.11 5376958.61    521.988 BuildingWallSurface
   1314867 3482966.11 5376958.61    521.988 BuildingWallSurface
   1314867  3482963.9 5376963.24    521.988 BuildingWallSurface
   1314867  3482963.9 5376963.24    521.988 BuildingWallSurface
   1314867 3482968.12 5376965.28    521.988 BuildingWallSurface
   1314867 3482968.12 5376965.28    521.988 BuildingWallSurface
   1314867  3482973.5 5376954.25    521.988 BuildingWallSurface
   1314867  3482973.5 5376954.25    521.988 BuildingWallSurface
   1314867 3482970.35 5376952.78    521.988 BuildingWallSurface
   1314867 3482970.35 5376952.78    521.988 BuildingWallSurface
   1314867 3482967.35  5376954.8    521.988 BuildingWallSurface
   1314867 3482967.35  5376954.8    521.988 BuildingWallSurface
   1314867 3482973.73 5376953.75    521.847 BuildingWallSurface
   1314867 3482973.73 5376953.75    521.847 BuildingWallSurface
   1314867  3482973.5 5376954.25    521.847 BuildingWallSurface
   1314867  3482973.5 5376954.25    521.847 BuildingWallSurface
   1314867 3482968.12 5376965.28    521.847 BuildingWallSurface
   1314867 3482968.12 5376965.28    521.847 BuildingWallSurface
   1314867 3482973.01 5376967.63    521.847 BuildingWallSurface
   1314867 3482973.01 5376967.63    521.847 BuildingWallSurface
   1314867 3482975.48 5376962.49    521.847 BuildingWallSurface
   1314867 3482975.48 5376962.49    521.847 BuildingWallSurface
   1314867 3482976.79 5376963.12    521.847 BuildingWallSurface
   1314867 3482976.79 5376963.12    521.847 BuildingWallSurface
   1314867  3482979.9 5376956.67    521.847 BuildingWallSurface
   1314867  3482979.9 5376956.67    521.847 BuildingWallSurface
   1314867 3482977.21 5376955.37    521.847 BuildingWallSurface
   1314867 3482977.21 5376955.37    521.847 BuildingWallSurface
   1314867 3482976.84 5376954.15    521.847 BuildingWallSurface
   1314867 3482976.84 5376954.15    521.847 BuildingWallSurface
   1314867    3482975 5376953.32    521.847 BuildingWallSurface
   1314867    3482975 5376953.32    521.847 BuildingWallSurface
   1314867 3482973.73 5376953.75    521.847 BuildingWallSurface
   1314867 3482973.73 5376953.75    521.847 BuildingWallSurface

38 rows selected.

Then, using the functions from your other posts, you could do something like this to get, not just all the coordinates
for one geometry, but for the whole building:

SCOTT@orcl_12.1.0.2.0> SELECT a.id , concat_json(a.id), d.Classname
  2  FROM   building a,
  3  	    THEMATIC_SURFACE b,
  4  	    SURFACE_GEOMETRY c,
  5  	    OBJECTCLASS d
  6  WHERE  a.grid_id_500 = 2728
  7  AND    a.id = b.BUILDING_ID
  8  AND    b.LOD2_MULTI_SURFACE_ID = c.ROOT_ID
  9  AND    c.GEOMETRY IS NOT NULL
 10  AND    b.OBJECTCLASS_ID = d.ID
 11  GROUP  BY a.id, d.classname
 12  /

        ID
----------
CONCAT_JSON(A.ID)
--------------------------------------------------------------------------------
CLASSNAME
------------------------------
   1314867
{"type":"Polygon","coordinates":[[[3482967.35,5376954.8,521.99],[3482965.61,5376
958.37,521.99],[3482966.11,5376958.61,521.99],[3482963.9,5376963.24,521.99],[348
2968.12,5376965.28,521.99],[3482973.5,5376954.25,521.99],[3482970.35,5376952.78,
521.99],[3482967.35,5376954.8,521.99],[3482973.73,5376953.75,521.85],[3482973.5,
5376954.25,521.85],[3482968.12,5376965.28,521.85],[3482973.01,5376967.63,521.85]
,[3482975.48,5376962.49,521.85],[3482976.79,5376963.12,521.85],[3482979.9,537695
6.67,521.85],[3482977.21,5376955.37,521.85],[3482976.84,5376954.15,521.85],[3482
975,5376953.32,521.85],[3482973.73,5376953.75,521.85]]]}
BuildingWallSurface


1 row selected.
Re: Using SDO_UTIL.GETVERTICES() to fetch all points [message #655095 is a reply to message #654911] Mon, 22 August 2016 03:55 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
Both statements are working Smile thank you.

I use the first one
SELECT c.root_id, c.geometry.sdo_ordinates as coordinates
FROM surface_geometry c
.
Because I wanna have all polygons seperate with objectclass.
I get it back as an object datatype. But in my case I need either Number oder Varchar.
Is it possible to handle this? So that I get my result in one of these types back?

My webserver has native support to number and varchar. For Blob, Clob...I have to write a pl/sqlwrapper. This requires runtime.
I will try a solution maybe create new column with coordinates in number or varchar and then just read it out...

[Updated on: Mon, 22 August 2016 07:35]

Report message to a moderator

Re: Using SDO_UTIL.GETVERTICES() to fetch all points [message #655126 is a reply to message #655095] Mon, 22 August 2016 13:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Concat_json returns a clob. This was necessary because, in another of your posts, you found that varchar2 was not long enough.
Re: Using SDO_UTIL.GETVERTICES() to fetch all points [message #655128 is a reply to message #655126] Mon, 22 August 2016 13:59 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
Ah ok. So there is no other possibility with Oracle locator? Nummer,string varchar data types would be the best for my Webserver because then I have direct support. And objects like clob, Lob I have to write a pl/sql wrapper.

Then I will test it with clob Type
Re: Using SDO_UTIL.GETVERTICES() to fetch all points [message #655168 is a reply to message #655128] Tue, 23 August 2016 05:39 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
But it must be possible to create VARCHAR2 with only coordinates right? Or do you think this is bigger than the maximum size of the column?
So that inside are only the coordinates of the polygons. For example I need the polygons seperate. So I can create a column in my table surface_geometry which contains the coordinates x,y,z([xx.xxxx,yy.yyyy,zz.zzzz],[...],[...]. Because then I can have direct access to the coordinates.
I think it will be faster then using clob for me.
Re: Using SDO_UTIL.GETVERTICES() to fetch all points [message #655174 is a reply to message #655168] Tue, 23 August 2016 14:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You will have to test and see if it is too big. In SQL, the limit for VARCHAR2 is 4000 characters. In PL/SQL, the limit for VARCHAR2 is 32767 characters. For anything larger than that you need a CLOB. If you want to have all of the coordinates for all of the polygons in one building in one string, then you will likely exceed the VARCHAR2 limit and need a CLOB.

When you say that "it" will be faster, what is "it"? You should be more concerned with how quickly the data can be accessed/queried than how long it takes to load it. If you are planning to access/query spatial data through Oracle, then you should be using a column of data type MDSYS.SDO_GEOMETRY, not CLOB or VARCHAR2.

[Updated on: Tue, 23 August 2016 14:59]

Report message to a moderator

Re: Using SDO_UTIL.GETVERTICES() to fetch all points [message #655337 is a reply to message #655174] Mon, 29 August 2016 02:54 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
OK so when I have a webserver which executes queries to fetch geometry data I have to use a column of type MDSYS.SDO_GEOMETRY? For this I can have faster access than CLOB or VARCHAR?
Re: Using SDO_UTIL.GETVERTICES() to fetch all points [message #655367 is a reply to message #655337] Mon, 29 August 2016 15:02 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
If you plan to use Oracle Spatial features to access the data, then, for maximum efficiency, you should have the data stored in columns of type sdo_geometry. Otherwise, you will either not be able to use the Oracle spatial features or will have to convert the data on the fly to use the features. Since you have an Oracle database, it should not be a problem to create columns of sdo_geometry data type and load the data into them. What data types are returned when you query the data depends on what types of queries you are running. I don't understand what problems you perceive. So far, it appears that everything that you have asked is related to storage of, not retrieval of the data.

If, for example, you store the data in a clob or varchar2 somehow, then you cannot create a spatial index on it, which means that you cannot access it via any Oracle spatial feature that requires an Oracle Spatial index created on a column of type sdo_geometry.





[Updated on: Mon, 29 August 2016 15:05]

Report message to a moderator

Re: Using SDO_UTIL.GETVERTICES() to fetch all points [message #655371 is a reply to message #655367] Mon, 29 August 2016 16:06 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
Ah ok so for example if I convert my data to a clob or varchar 2 then I have only a normal Index.
On the other side if I have a so_geometry type I have a spatial Index which is more efficient then the normal one.
My project is not related on the storage it is focused on the retrieval of the data. The aim is to have fast acces and transfer of the data to the Client
Re: Using SDO_UTIL.GETVERTICES() to fetch all points [message #655374 is a reply to message #655371] Mon, 29 August 2016 17:02 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
It is not just that an sdo_geom column and spatial index is more efficient. There are many spatial functions and procedures that require an sdo_geom column and spatial index. Without an sdo_geom column and a spatial index, you will not be able to use such functions or procedures to retrieve your data. Having just a regular index on a clob or varchar2 column will mean that you cannot use most spatial functions or procedures to access the data. For a few of them, you might be able to convert your non-sdo_geom data, but it would be very slow. For most, you would not be able to use them at all. If you are going to access Spatial data on an Oracle database, then you should definitely store it in an sdo_geom column, make appropriate entries in the user_sdo_geom_metadata view, and create a spatial index on the column.

[Updated on: Mon, 29 August 2016 17:03]

Report message to a moderator

Re: Using SDO_UTIL.GETVERTICES() to fetch all points [message #655379 is a reply to message #655374] Tue, 30 August 2016 03:02 Go to previous message
moehre
Messages: 43
Registered: June 2016
Member
OK thanks for your help Smile
This helps me a lot...
Previous Topic: Arraysize over 5000
Next Topic: Create JSON in new column by using sdo2geojson3d
Goto Forum:
  


Current Time: Thu Mar 28 17:44:52 CDT 2024