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  |
 |
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  |
 |
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
moehre wrote on Tue, 16 August 2016 07:31Hi,
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 |
|
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 |
|
Barbara Boehmer
Messages: 9100
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 |
|
Barbara Boehmer
Messages: 9100
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 |
|
moehre
Messages: 43
Registered: June 2016
|
Member |
|
|
Both statements are working  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 #655174 is a reply to message #655168] |
Tue, 23 August 2016 14:55 |
|
Barbara Boehmer
Messages: 9100
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 #655367 is a reply to message #655337] |
Mon, 29 August 2016 15:02 |
|
Barbara Boehmer
Messages: 9100
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 #655374 is a reply to message #655371] |
Mon, 29 August 2016 17:02 |
|
Barbara Boehmer
Messages: 9100
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 |
|
|
|
|
|
Goto Forum:
Current Time: Thu Nov 21 07:25:26 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
