Go to content Go to navigation and search

Home

Current Oracle Spatial Blog Articles


Search

Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

Selecting all SDO_GTYPE values for all tables/sdo_geometry columns in a schema

Wednesday March 14 2012 at 03:21

Keywordssdo_gtype values sdo_geometry all tables schema
Summary

This article demonstrates how to craft a piece of PL/Sql that queries the Oracle metadata to find all objects with sdo_geometry columns and then, for each object/column, queries the data to return all sdo_gtype values.

A question was asked on the Oracle Technology Network Spatial forum:

I am trying to select sdo_gtype from all tables in schema, i mean to execute same sql statement given below for entire schema.

  1. SELECT a.geometry.sdo_gtype FROM centerline a;

Any help on the usage of dynamic sql in PLSQL to execute select statement for all tables in the schema.

Here is a handy piece of anonymous PL/SQL for finding the sdo_gtype values for all sdo_geometry columns of data in any table/object in a schema.

  1. SET serveroutput ON SIZE unlimited
  2. DECLARE
  3.   v_sql         varchar2(4000);
  4.   v_gtype       varchar2(40);
  5.   v_gtype_count NUMBER;
  6.   c_gtypes      sys_refcursor;
  7. BEGIN
  8.   FOR rec IN (SELECT TABLE_NAME, column_name
  9.                 FROM user_tab_columns
  10.                WHERE data_type = 'SDO_GEOMETRY'
  11.               ORDER BY TABLE_NAME, column_name) loop
  12.   BEGIN
  13.      v_sql := 'SELECT a.' || rec.column_name || '.sdo_gtype gtype, count(*) as gtype_count ' ||
  14.                ' FROM ' || rec.TABLE_NAME || ' a ' ||
  15.               ' WHERE a.' || rec.column_name || ' is not null ' ||
  16.               ' GROUP BY a.' || rec.column_name || '.sdo_gtype';
  17.      OPEN c_gtypes FOR v_sql;
  18.      FETCH c_gtypes INTO v_gtype, v_gtype_count;
  19.      IF ( c_gtypes%NOTFOUND ) THEN
  20.         dbms_output.put_line(rec.TABLE_NAME || '.' || rec.column_name ||' has no data.');
  21.      ELSE    
  22.        LOOP
  23.           dbms_output.put_line(rec.TABLE_NAME || '.' || rec.column_name ||' = ' ||
  24.                                (CASE WHEN v_gtype IS NULL THEN 'NULL' ELSE to_char(v_gtype) END) ||
  25.                                ' Count(' || v_gtype_count || ')');
  26.           FETCH c_gtypes INTO v_gtype, v_gtype_count;
  27.           EXIT WHEN c_gtypes%NOTFOUND;
  28.        END LOOP;
  29.      END IF;
  30.      CLOSE c_gtypes;
  31.      exception
  32.         WHEN others THEN
  33.              dbms_output.put_line('Query (' || v_sql || ') threw error (' || SQLCODE ||')');
  34.   END;
  35.   END loop;
  36. END;
  37. /
  38. -- Results
  39. anonymous block completed
  40. GEODPOINT2D.GEOM = 2001 COUNT(500)
  41. GEODPOINT3D.GEOM = 3001 COUNT(500)
  42. GEODPOLY2D.GEOM = 2003 COUNT(1)
  43. GUTDATA.GEOM = 2001 COUNT(1000)
  44. HAS_METADATA.GEOM = 2001 COUNT(500)
  45. HAS_METADATA3D.GEOM = 3001 COUNT(500)
  46. LOCALLINE2D.GEOM = 2002 COUNT(2)
  47. LOCALPOINT2D.GEOM = 2001 COUNT(1)
  48. LOCALPOLY2D.GEOM = 2007 COUNT(1)
  49. LOCALPOLY2D.GEOM = 2003 COUNT(1)
  50. LRS_ROUTES.ROUTE_GEOMETRY = 3302 COUNT(4)
  51. MARK_A.GEOM = 2001 COUNT(25)
  52. MARK_EMPTY.GEOM has no DATA.
  53. MARK_L.GEOM = 3001 COUNT(4)
  54. NO_METADATA.GEOM = 2001 COUNT(500)
  55. ORACLE_TEST_GEOMETRIES.GEOMETRY = 2005 COUNT(4)
  56. ORACLE_TEST_GEOMETRIES.GEOMETRY = NULL COUNT(1)
  57. ORACLE_TEST_GEOMETRIES.GEOMETRY = 2112 COUNT(1)
  58. ORACLE_TEST_GEOMETRIES.GEOMETRY = 2001 COUNT(3)
  59. ORACLE_TEST_GEOMETRIES.GEOMETRY = 2006 COUNT(8)
  60. ORACLE_TEST_GEOMETRIES.GEOMETRY = 4002 COUNT(1)
  61. ORACLE_TEST_GEOMETRIES.GEOMETRY = 2007 COUNT(8)
  62. ORACLE_TEST_GEOMETRIES.GEOMETRY = 3002 COUNT(1)
  63. ORACLE_TEST_GEOMETRIES.GEOMETRY = 3302 COUNT(1)
  64. ORACLE_TEST_GEOMETRIES.GEOMETRY = 2003 COUNT(17)
  65. ORACLE_TEST_GEOMETRIES.GEOMETRY = 2004 COUNT(2)
  66. ORACLE_TEST_GEOMETRIES.GEOMETRY = 2002 COUNT(18)
  67. ORIENTED_POINT.GEOM = 3001 COUNT(1533)
  68. PROJ41014POLY2D.GEOM = 2003 COUNT(2)
  69. PROJ41914POLY2D.GEOM = 2003 COUNT(1)
  70. PROJCIRCLE2D.GEOM = 2003 COUNT(500)
  71. PROJCOMPOUND2D.GEOM = 2004 COUNT(3)
  72. PROJLINE2D.GEOM = 2002 COUNT(5)
  73. PROJLINE3D.GEOM = 3002 COUNT(2)
  74. PROJMULTILINE2D.GEOM = 2006 COUNT(500)
  75. PROJMULTIPOINT2D.GEOM = 2005 COUNT(9)
  76. PROJMULTIPOINT3D.GEOM = 3005 COUNT(1)
  77. PROJMULTIPOLY2D.GEOM = 2007 COUNT(500)
  78. PROJPOINT2D.GEOM = 2001 COUNT(500)
  79. PROJPOINT3D.GEOM = 3001 COUNT(500)
  80. PROJPOLY2D.GEOM = 2003 COUNT(8)
  81. PROJPOLY3D.GEOM = 3003 COUNT(2)
  82. TOOLSPOLY2D.CENTROID = 2001 COUNT(1)
  83. TOOLSPOLY2D.GEOM = 2003 COUNT(2)
  84. V_GEODPOINT2D.GEOM = 2001 COUNT(500)
  85. V_GEODPOLY2D.GEOM = 2003 COUNT(1)
I hope this helps someone out there.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment [4]

Good solution Simon. It’d be nice to modify it slightly to also return the count of features per geometry type in each table.

— John O'Toole · 14 March 2012, 08:52 · #

John,

Good idea.

I’ve modified the code accordingly.

regards
Simon

Simon Greener · 14 March 2012, 10:30 · #

I personally like to have the result as table. I first query db catalog and compose querys much like Simon’s first FOR loop. Then I copy-paste the result and execute the new query. I only have to replace last UNION ALL with semicolon.

select ‘select ‘’‘ || table_name || ‘’‘ as tab, ‘’‘ || column_name || ‘’‘ as col, a.’ || column_name || ‘.sdo_gtype as gtype, count(*) as gtype_count ‘ || ‘ from ‘ || table_name || ‘ a ‘ || ‘ where a.’ || column_name || ‘ is not null ‘ || ‘ group by a.’ || column_name || ‘.sdo_gtype union all’
from (select table_name, column_name from user_tab_columns where data_type = ‘SDO_GEOMETRY’ order by table_name, column_name)
;

(secret: I’m not at all comfortable with loops, cursors, exeptions etc. :-)

— Riivo Kolka · 15 March 2012, 19:15 · #

Riivo,

I understand and sympathise as I always have to look up the OPEN/FETCH version of a cursor – the others are OK. I prefer SQL first and foremost, but in this case if you wanted a dynamic view (instead of a table) you can’t do it using either method.

One small change to your SQL which removes the last UNION ALL:

  1. SELECT qsql ||
  2.        CASE WHEN rid = (MAX(rid) OVER (partition BY 1)) THEN '' ELSE ' union all' END
  3.   FROM (SELECT ROW_NUMBER()  OVER (partition BY 1 ORDER BY TABLE_NAME, column_name) AS rid,
  4.                'select ' || TABLE_NAME || ' as tab, ' || column_name || ' as col, a.' || column_name || '.sdo_gtype as gtype, count(*) as gtype_count ' ||
  5.                 ' from ' || TABLE_NAME || ' a ' ||
  6.                ' where a.' || column_name || ' is not null ' ||
  7.                ' group by a.' || column_name || '.sdo_gtype ' AS qsql
  8.          FROM user_tab_columns u
  9.         WHERE u.data_type = 'SDO_GEOMETRY'
  10.       ) f
  11.   ORDER BY rid;
regards Simon

Simon Greener · 15 March 2012, 21:46 · #