topic Re: Convert a database table to a feature class? in ArcGIS Enterprise Questions

Convert a Oracle table to a feature class?

Bud — Tue, 21 Jun 2022 08:16:34 GMT

What are the steps needed to convert a Oracle table to a FC in an EGDB?

For example:

1. Take an existing Oracle table.
2. Add a geometry column if needed.
3. If it's an SDO_GEOMETRY column, then register with USER_SDO_GEOM_METADATA?
4. Add a spatial index using the Add Spatial Index GP tool?
5. Register with the geodatabase using Catalog in ArcMap?
6. Other?

I've tried various combinations of the above steps, but I often have problems like the resulting FC's attribute table is empty when I view it in ArcMap. Or the spatial reference is broken. Or the geometry type (point/line/polygon) isn't properly defined, etc.
I usually give up at that point and try something else, like create an empty FC via ArcMap, create a spatial view on the Oracle table, and then use the Load tool in Catalog to load the data into the empty FC. But that's pretty clunky...I'm hoping there's a better way.

Thanks.

Re: Convert a database table to a feature class?

JakeSkinner — Thu, 16 Jun 2022 11:35:04 GMT

Hi @Bud,

Here is an example of creating a table, inserting points, inserting into the user_sdo_geom_metadata table, creating the index, and then registering with your geodatabase:

create table SDO_Points (OID number(38) not null unique, TestID number, shape SDO_GEOMETRY); Insert into SDO_Points values (1, 1, SDO_GEOMETRY(4001, 8260, null, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(10.0, 10.0, 10, 1100))); Insert into SDO_Points values (2, 2, SDO_GEOMETRY(4001, 8260, null, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(10.0, 20.0, 20, 1200))); Insert into SDO_Points values (3, 3, SDO_GEOMETRY(4001, 8260, null, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(10.0, 30.0, 30, 1300))); Insert into SDO_Points values (4, 4, SDO_GEOMETRY(4001, 8260, null, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(10.0, 40.0, 40, 1400))); insert into user_sdo_geom_metadata values ('SDO_Points', 'SHAPE', SDO_DIM_ARRAY(SDO_DIM_ELEMENT('Lat', -90, 90, 0.05), SDO_DIM_ELEMENT('Long', -180, 180, 0.05), SDO_DIM_ELEMENT('Elevation', 0, 5000, 0.05), SDO_DIM_ELEMENT('M', 0, 5000, 0.05)), 8260); create index SDO_8260_4D_Point_indx on SDO_Points(shape) indextype is mdsys.spatial_index;

Last step is to execute the Register with Geodatabase tool in ArcGIS Pro/ArcMap.

Re: Convert a database table to a feature class?

Bud — Tue, 21 Jun 2022 07:35:26 GMT

Thanks very much. That really helps.

Re: Convert a Oracle table to a feature class?

Bud — Wed, 20 Jul 2022 19:52:52 GMT

@JakeSkinner

FYI - I noticed just now that SQL Developer has a tool for entering Spatial Metadata for a given table:

That could be handy.