3 Replies Latest reply on Oct 7, 2013 9:18 AM by _jum

    How to get the null geometry records


      Hi all,


      I have a table which some records have "MDSYS.SDO_GEOMETRY(0,0,NULL,NULL,NULL)" value. I want to create a view which filters these records and display the ones only with geometry info. I tried to use "where TABLENAME.POLY IS NOT NULL" but it does not work. I made a search on the internet. I found arcgis sde.is_empty which does not work for Oracle. So how can I check if the record has empty or null geometry? Many thanks in advance



        • 1. Re: How to get the null geometry records
          Ivan Bush
          Hi,  The case you list here is not a NULL geometry. Assuming all the records you want to exclude are the same as this you can get the one with geometries using:         select *         from TABLENAME t         where  t.POLY.sdo_srid!=0  Hope this helps.  Ivan
          • 2. Re: How to get the null geometry records
            B Hall

            If you can, it would best to eliminate that "data" and actually set the field to NULL. What you are actually doing now is storing the framework for data (nested arrays), with nothing useful in them.



            1 person found this helpful
            • 3. Re: How to get the null geometry records

              It depends on your valid geometries, here an example, comment the first or the second AND to filter geometries with orphan SDO_POINT or empty SDO_ORDINATES:


              WITH geodata AS
              (SELECT 1 id, MDSYS.SDO_GEOMETRY(   0,    0,                       NULL, NULL,NULL) geom                                                   FROM dual UNION ALL
                SELECT 2   , MDSYS.SDO_GEOMETRY(3001, NULL,SDO_POINT_TYPE(  0,  0,  0), NULL,NULL)                                                         FROM dual UNION ALL
                SELECT 3   , MDSYS.SDO_GEOMETRY(3002, NULL,                       NULL, SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(  0,0,0,10,10,10))  FROM dual)
              SELECT *
                FROM geodata gd
              WHERE 1=1
              AND gd.geom.sdo_point     IS NULL
              AND gd.geom.sdo_ordinates IS NULL;
              1 person found this helpful