AnswerBun.com

Selecting polygon feature classes with one record in Oracle geodatabase using SQL query?

Geographic Information Systems Asked by Mich on November 6, 2020

I have an Oracle Enterprise geodatabase with many feature classes. I would like a SQL query to find all the polygon feature classes with only one record in its table. So far, I can only query all the feature classes.

  SELECT * 
  FROM SDE.GDB_ITEMS items INNER JOIN SDE.GDB_ITEMTYPES itemtypes ON items.Type = itemtypes.UUID
  WHERE itemtypes.Name = 'Feature Class' 

One Answer

Several parts are involved:

  • getting all the tables from the database (used code from the answer https://stackoverflow.com/a/9954833/3346915)
  • reading geodatabase metadata (in your question body)
  • filtering using the where clause to include only those tables that are of feature class type and their shape type are of polygon (parsing XML is involved).

This code would work on SQL Server:

SELECT 
    TableName = t.NAME,
    TableSchema = s.Name,
    RowCounts = p.rows
FROM 
    sys.tables t
INNER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID 
    AND i.index_id = p.index_id
WHERE 
    t.name in (
        (SELECT PARSENAME(items.Name,1)
          FROM SDE.GDB_ITEMS items INNER JOIN SDE.GDB_ITEMTYPES itemtypes 
          ON items.Type = itemtypes.UUID
          WHERE itemtypes.Name = 'Feature Class' and 
                Definition.value('(/DEFeatureClassInfo/ShapeType)[1]', 
                'varchar(max)') = 'esriGeometryPolygon'))   
    and t.is_ms_shipped = 0 
    and p.rows = 1

GROUP BY
    t.NAME, s.Name, p.Rows
ORDER BY 
    s.Name, t.Name

The results:

    TableName           TableSchema      RowCounts
--  ------------------  -------------  -----------
 0  ND_1226_DIRTYAREAS  dbo                      1
 1  REDLINING           dbo                      1
 2  SS_WORKAREAS        dbo                      1
 3  T_1_DIRTYAREAS      dbo                      1

Answered by Alex Tereshenkov on November 6, 2020

Add your own answers!

Related Questions

Making a heightmap DEM for use in putting together a relief

1  Asked on June 22, 2021 by sameer-iyengar

     

Changing Points into Hexagon-Polygons using QGIS

1  Asked on June 22, 2021 by dale-smith

       

Open link in new window when clicking on marker

1  Asked on June 22, 2021 by paul-van-zyl

   

Directional Buffer increasing radius of geometry

1  Asked on June 22, 2021 by macintosh1997

     

Deleting GDAL created raster

0  Asked on June 22, 2021 by d_c

       

Missing features after merge/append

0  Asked on June 22, 2021 by yuzhen

     

Loading multiple CSVs into QGIS 3

1  Asked on June 22, 2021 by ajie

         

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP