1

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?

2

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).

  • Thanks Vince. This was very helpful. I'm writing this as part of an ST_Geometry adapter for the Django framework. I thought once all the SDE tables, triggers, etc. were set up I could basically manipulate the data through SQL, but it sounds like the dependency on ArcGIS software might get in the way. – Rob Jun 2 '15 at 17:13
  • You can use SQL to manipulate tables containing SDE.ST_GEOMETRY, and you can coexist with ArcGIS Desktop/Server, provided the tables are "simple feature classes" (they are not part of a feature dataset, or have any geodatabase behaviors, and they meet the requirements for registration with ArcSDE). The dependency is imposed by wanting full geodatabase functionality without meeting geodatabse requirements. – Vince Jun 2 '15 at 17:28
  • Nice Explanation Vince.. I would add that sometimes the index remains 0 as the data is indexed by other non-basic indexing (like B-Tree, GIST..). The grid size(s) only apply to standard (grid) index... That one did my head in for a while until I read that PostGIS uses its own indexing (revenant.ca/www/postgis/workshop/indexing.html) and not the standard grid(s) therefore the Esri interpretation is 0. Only personal geodatabases need to have their spatial indexing specified at creation, file/enterprise GDB can modify/build indexes after data is added - as you said that's the best way. – Michael Stimson Jun 2 '15 at 22:43
  • Ah, yes, it's true that PostgreSQL could have a nominal 0 grid size, but not Oracle. The LAYERS table could have negative value in the GSIZE0 column to indicate SDO_GEOMETRY R-Tree index, but not the SDE.ST_GEOMETRY index parameter. – Vince Jun 2 '15 at 23:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.