2

Our GDB is using the SQL native GEOMETRY data storage type (SQL 2012 Standard) and the spatial reference for this uses a unit of square feet. I want to create a (persisted) computed column which will populate another column as acres.

Using the following query I'm able to produce the results I want:

Select shape.STArea()/43560 as Area from DATAOWNER.CTF_STANDS

In setting up the computed column I used the following: Computed Text

Editing on the front end GIS application (ArcGIS), the Shape.STArea() field updates during edits, but updates to my computed column fail to happen.

I think that Shape.STArea is actually a function and appears to be enforced/maintained by the SDE/GDB Schema itself, so working with spatial column appears to be the best way forward.

I don't want to convert the Geometry column itself to another unit, but rather use it to populate another column via a formula which converts square feet to acres.

How do I create a computed column which will derive acres from the geometry column who's current values are in square feet?

This other SE post's answer is very similar.

14
  • 1
    Maybe it's just the end of a long day, but I don't see a question here. Could you edit this to include a question mark not in parenthesis? As it stands, I can't tell if you want help creating a query, or a view, or a new column.
    – Vince
    Aug 1, 2014 at 22:58
  • I want to create a computed column (the MSSQL term) using either the geometry column or the existing values from shape.starea() which calculates acres from the existing square feet units. Aug 2, 2014 at 19:41
  • Please edit your question to revise it with the requested clarification and be sure to include a single question mark somewhere so that what you are asking is clear.
    – PolyGeo
    Aug 3, 2014 at 3:26
  • So you are saying that the value is shown in your GIS application, but when you edit your spatial data or add a new record, this column is not updated? Aug 3, 2014 at 8:39
  • I will edit question tomorrow for format and clarity. Thanks for keeping this in line. Aug 4, 2014 at 4:38

1 Answer 1

1

You have create a computed column in your database. This also means that it's up to the database to actually calculate the value in this column.

As a result, you'll only see an updated value when the record has been recalculated by the database. Changing part of the record within your GIS might not tricker the database to recalculate the value.

If you were working with MapInfo Professional the recalculation wouldn't happen until you saved your change to the database and potentially refreshed the current view of the data in MapInfo Professional. The last part does depend on whether you are using a live or a linked table connecting to the database.

I know you probably aren't using MapInfo Professional, but I'm guessing any application might work in a (more or less) similar way.

A solution to this would be to have your GIS application add and calculate this value to the table.

5
  • I would have thought that if the computed column was 'persisted', the value would be updated 'automatically'. After all, shape.STArea() is updated even before committing your edits in the GIS application. I'm aware of the option to script this in the GIS front-end, but that would still need to be executed by the end-user so I don't see that as a viable option for the workflow I'm trying to achieve here. Certainly, there must be a way to handle this on the RDBMS back-end. Aug 11, 2014 at 13:01
  • Where can you see that Shape.STArea() is updated before committing your edits? Do you have that as a column as well? Aug 11, 2014 at 15:25
  • While performing spatial edits in ArcMap, the Shape.STArea() & Shape.STLength() dynamically update upon completing an edit. You can see these fields in the attribute table in ArcMap. Creating a persisted spatial column with some kind of conversion seems to be the right path which is why I think I should maybe take this to StackOverflow. Aug 11, 2014 at 15:43
  • I'm not that familiar with the ESRI products, but I think the two columns you are talking about are dynamic column updated by the Arc product itself and not by the database Aug 12, 2014 at 6:09
  • I have removed the possibility of working with Shape.STArea() as a viable option in this question. I think the answer lies in working directly with the spatial/GEOMETRY column itself. Aug 12, 2014 at 12:27

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.