0

I have a non-spatial table in an external system. The table has X & Y columns.

The table will be synced to an Oracle 18c/10.7.1 geodatabase table or FC (via the ArcGIS REST API). There will be roughly 200 inserts or updates from the external system per day.


I would like to serve up the synced geodatabase object (table or FC) to a Portal web map as a spatial point layer.

Question:

Is it possible to convert the X & Y columns to a SHAPE column -- using a method that would perform well in Portal?


Examples:

  1. A feature class with an empty SDE.ST_GEOMETRY column. When rows are inserted or updated, a custom trigger would populate the value in the shape column (via st_point).

  2. An XY event layer.

  3. A registered spatial view that generates a calculated SDE.ST_GEOMETRY column (via the st_point function).

  4. A registered spatial view that generates a calculated SDO_GEOMETRY column.

  5. Something else?

  • You seem to be seeking a discussion on "What would be the highest-performing option for converting the X & Y coordinates to a shape column (in terms of spatial layer performance in the map)?". For focused Q&A, I think you should research, and preferably test, one of your ideas and if you get stuck then describe in detail what you researched/tried related to that idea, and where you are stuck with it. – PolyGeo Jan 8 at 2:46
  • 1
    2, 3, and 4 are non-starters -- You never want a calculated geometry. Oracle Spatial might be free, but ArcGIS never uses any Spatial functions, so that's a non-sequitur. Your real choices are an SDO_GEOMETRY column and an sde.ST_GEOMETRY column. For points, they're nearly the same, storage-wise, so there isn't much difference. The question is what else you've got (polygon and line), and how they interact with the points. The thing is, this is more a discussion post than a question, so it seems likely to be closed. – Vince Jan 8 at 3:59
3

I assume that there is a physical Oracle table that sees insert/update events as the REST service fires.

And that those events aren't a complete truncate/reload.

If so I would create a before insert/update trigger and construct an sde.st_geometry object from the X,y columns. No indexes should exist before the initial load; create them after the load. As long as the subsequent transactions are small, the index changes should not slow things down.

The fact that Oracle Spatial is now free has no bearing on this problem as Locator has all that you need and has always been free. Yes, you can use both, but ESRI practitioners generally don't use Sdo_geometry, preferring sde.st_geometry. ESRI users pay a lot of money for sde.st_geometry, even when Oracle Locator (supported by ESRI in its software products) was free.

2

Number 1 will have least overhead. Based on map extent used most often set smallest grid slightly smaller than this map extent zoomed to. Set next middle grid 3 times this value. Set largest grid three times the middle grid value. This works like a spatial index explained here: https://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/guidelines-to-choose-spatial-index-grid-size.htm

  • Points shouldn't have three levels of indexing (they'd never use more than the first anyway), and index size tuning hasn't been a regular part of geodatabase administration for a while now. – Vince Jan 8 at 4:02

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.