ArcSDE - spatial index grid size for empty table

Question

I'm trying to write the SQL to create a feature class and spatial index in an Oracle SDE geodatabase using ST_Geometry. This is how I'm making the table:

CREATE TABLE STREETS (ID NUMBER(11) NOT NULL PRIMARY KEY, SEG_ID NUMBER(11) NOT NULL, SHAPE SDE.ST_GEOMETRY NULL)

Next I need to make the index which will look something like:

CREATE INDEX STREETS_IDX ON STREETS(SHAPE) INDEXTYPE IS sde.st_spatial_index PARAMETERS('st_grids=0 st_srid=4326')

The problem is I'm not sure what would be an appropriate grid size. I found this article which suggested using "one grid level with a cell size three times the average feature extent size", but the table is empty so I don't see how I would calculate that. Secondly, the spatial reference is WGS84 so what units would I be passing in? Degrees?

Answer 1

Actually, grid size zero is usually an indicator to ArcGIS to wait until after initial loading to determine the spatial index. In best practice, you shouldn't build an index until after the data is loaded -- it can significantly impact load performance to populate a large table with an index enabled. By the time loading is complete, you will know an appropriate grid size (which should only be a single value for most datasets with uniform feature size).

Grid size is always in native coordinate reference units (degrees in a coordinate reference with a GCS_WGS_1984 coordinate system). If in doubt, you can make the index unnaturally large (e.g,., 90 degrees or 10000km), just to get data loaded, then go back later and ask ArcGIS to rebuild the spatial index.

You haven't mentioned what version of Oracle or ArcGIS you are using, which makes it difficult to determine if the existence of the SE_FLOAT64_TYPE 'ID' column with a PRIMARY KEY constraint will cause registration with ArcSDE to fail. Certainly the lack of any NUMBER(38) columns (which map to SE_INT32_TYPE) will make the table contents unavailable to either sdelayer -o register with ArcSDE or as an ArcGIS Query Layer.

Best practice is to use ArcGIS to create tables you expect to use with ArcGIS. You can skirt around that by creating tables just like ArcGIS would have (e.g., with OBJECTID NUMBER(38) NOT NULL), but the farther away to you wander from that standard, the more difficulty you are likely to have later. You should also make sure the "4326" coordref exists, and verify that the precision and range it supports will be adequate to your purpose (an SRID in SDE has a far deeper meaning than the coordinate system indicated by SRIDs in other storage mechanisms -- consult the Understanding Coordinate Management whitepaper for details).