how to update extent via sql (st_geometry) using arcgis 10.2

i've noticed that if one inserts a geometry, let's say a point, into a table via st_geometry, the corresponding table extent is not updated.

for instance:

INSERT INTO geo_table(shape) VALUES ( sde.st_geometry('point(-77.80 5.43)', 4686) );

does nothing tho geo_table's extent

is there a way to programatically or automatically update the feature extent?

My underlying db is oracle 11g.

  • 1
    Normally one would use a tool like 'append' or arcpy.da.insertCursor to insert a geometry which would update the extent at the same time. I'm personally not keen on directly modifying any table that's registered to SDE - it's just too easy to break. By directly inserting the point into the table the point is also probably not included in the spatial index - which is very bad. If you must do it this way then try 'repair geometry' to update extents and rebuild your spatial index by delete and add. – Michael Stimson Mar 13 '15 at 0:09
  • 2
    @MichaelMiles-Stimson ST_GEOMETRY is more robust than you give it credit -- the spatial index takes care of itself as necessary. I generally initialize the extent so the layer properties don't require update (this makes the query optimization code in ArcGIS work better), or I use a tool which updates the extent as necessary. – Vince Mar 13 '15 at 0:34
  • Fair enough @Vince. I have a policy of not poking around where I shouldn't (or at least where others will notice if I make a mistake) so stay well away from the backend of SDE except where I have a reason to (like make new databases, backup and restore..). How would you update the extent with the tool? Is it an Esri tool or SQL expression? or do you just set everything with a huge extent first? – Michael Stimson Mar 13 '15 at 0:42
  • That's not a bad policy, really, and encouraging that caution is good practice, but I think "too easy to break" is a bit overstated, at least with respect to doing an INSERT into a simple feature class. – Vince Mar 13 '15 at 1:35

You are correct -- the layer metadata is not updated by SQL inserts. But this isn't particularly significant, since the layer extent ought to be initialized to the range of valid values before population. And if it isn't, it may be for a reason that changing the index as part of insert would impact, so not changing it is probably safer.

If you do need to update the extent of a layer, there are a number of ways to accomplish this, but the safest use the ArcSDE API (either directly by sdelayer -o alter -E calc or sdelayer -o alter -E minx,miny,maxx,maxy, or indirectly through ArcObjects -- the Feature Extent tab of Layer Properties was added for this purpose). I've only rarely modified the SDE.LAYERS table directly via SQL, but I really can't recommend this for anyone with less than twenty years of SDE/ArcSDE experience (which is probably me and three or four others on the whole planet, and I haven't found it necessary in the past ten years)

Best practice is to initialize the extent at layer creation, or to update it if layer contents change significantly. Of course, if layer contents change significantly, then rebuilding the spatial index is probably warranted as well.

There are number of risks to using SQL to insert/update/delete rows outside the ken of ArcGIS, but they mostly pertain to versioned feature classes, or feature datasets, or the presence CAD objects, or specialized behaviors. Simple feature classes (plain old ArcSDE layers) shouldn't be able to be harmed, though things might get confusing if no features have previously been added to the layer and the spatial index is set to zero (auto-populate), and you don't rebuild the spatial index when you're done inserting.

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.