5

I have certain string fields in my geodatabase tables with which I would like to enforce an all-uppercase rule. These fields are ones that do not lend themselves to domains (such as address fields).

Is it possible to create a geodatabase string field that supports only uppercase characters inherently? I would like to be able to use the built-in ESRI tools within ArcMap to edit my tables and have them converted to uppercase on the fly.

I am using ArcSDE and MS SQL-Server. Answers that utilize front end validation on ESRI's stack are preferred, but a generalized SQL solution that parses inputs on the RDBMS side with something like an INSTEAD OF trigger is acceptable as well.


Edit: As the accepted answer consists of SQL editing on an RDBMS backend of ArcSDE, Devdatta Tengshe has noted that it can be very dangerous to do this if you do not know what you are doing. It could possibly corrupt your data.

If your situation is conducive, you should use BradHard's answer instead (creating a database view as your data source).

ESRI does not actually discourage the usage of SQL triggers on the backend to maintain data. However, ESRI instructs in their documentation that you should not use SQL on your RDBMS to edit the following ArcSDE data:

Do not use SQL to edit the following:

  • ArcSDE geodatabase system tables

  • ObjectIDs (or row IDs) that are maintained by ArcSDE or the geodatabase

  • Tables or feature classes that participate in the following geodatabase functionality:

    1. Annotation feature classes and feature-linked annotation

    2. Archived data

    3. Cadastral or parcel fabrics

    4. Domains

    5. Geodatabase replication

    6. Geometric networks

    7. Network datasets

    8. Relationship classes

    9. Representations

    10. Schematic datasets

    11. Subtypes

    12. Survey datasets

    13. Terrains

    14. Topology

As long as the fields that you want to force uppercase do not fail any of the requirements listed above, then you can safely use Devdatta Tengshe's SQL trigger method below to force uppercase on whatever individual string fields you desire.

  • Do you have Versioning and Replication Set up? – Devdatta Tengshe Dec 31 '13 at 5:58
  • Yes, versioning and replication are set up. – Conor Dec 31 '13 at 14:03
3

There are a lot of unknowns in your questions. I do not know your level of skills with MS SQL server or ArcSDE. I don't know how comfortable you are with messing around with the backend tables.

That's why in general, I would say that BradHard's Answer is the best way of achieving what you want. Having a spatial view with an upper operator is the safest option.

But If you are brave enough to mess around with the RDBMS backend, do remember that you are pretty much on your own and there is an high chance of corrupting the database.

This is one way of use a trigger for data validation:

  1. Firstly make sure that your featureclass is registered as versioned.
  2. Go to the SDE_table_registry table, and find out out the registration_id of your featureclass. This registration_id will tell you which is the A (i.e. Addition) Table for your versioned Featureclass. In my case, the id was 20. Hence I need to make a trigger on table a20
  3. Make the following trigger:
CREATE TRIGGER dbo.upper_trig 
   ON  dbo.a20 
   Instead of Insert
AS 
BEGIN
    INSERT INTO [dbo].[a20]
           ([OBJECTID] ,[Name],[Id],[SHAPE],[SDE_STATE_ID])
     Select
           OBJECTID, UPPER(Name),Id,SHAPE,SDE_STATE_ID from inserted;
END
GO

In this example, my featureclass has two fields Name & Id, in addition to the system generated fields.

  • 1
    I'm not near an ArcSDE so I can't test but I don't think that an INSTEAD OF UPDATE will not work. When you update a featureclass, the existing row in the A Table is not updated. Instead an Entry is made in the D Table, and a new row is made in the A Table. AFAIK, the INSTEAD OF UPDATE trigger does not run on inserts, and hence when you edit an existing row, or even create a new row in your Feature class, this trigger will not fire. – Devdatta Tengshe Jan 1 '14 at 6:33
  • I just tested this via SDE and can confirm that this is in fact the case. Whenever updates are performed, a new row is inserted each time. Thanks Devdatta. – Conor Jan 2 '14 at 15:07
3

You didn't specify the particular table, so a general solution might be to create a VIEW that returns the UPPER of the table. Then it won't matter what is actually inserted/updated, it'll still be in upper case.

  • I like your answer and it probably is the best solution in most cases (because forcing the data to act in a certain way like Devdatta's answer is likely not necessary in most cases and will increase server overhead). I chose Devdatta's answer because this answer does not provide a solution that forces uppercase in a specific string field but rather it is a (albeit better and safer) workaround. – Conor Dec 31 '13 at 17:05

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.