Populate X and Y fields in SDE Point Data Using SQL trigger

Question

Here is my situation. I am running ESRI ArcGIS Server and Desktop 10.2 on SQL Server 2008 R2. I would like to populate the Latitude and Longitude fields from point data (Signs data collected using ESRI Collector App) when saving. I have guessed as using a SQL server trigger on the table, but to no avail as the STPointfromtext SQL code does not appear to derive the X and Y data from the geolocation of the point being created. So since the x and y data will not currently exist in the field and I am wanting it to populate it when a save occurs, does anyone have any thoughts on how this might be able to be accomplished? Would the SQL code listed actually work if formatted correctly? If this would not work, how would you go about getting this field populated without manually doing it each time?

• Fezter: Have you had a look at STX and STY?

Answer 1

Provided that your data would not allow new inserts, having a computed column in SQL Server would be sufficient. However, since you are dealing with new inserts, I'd go for a trigger.

CREATE TRIGGER [XY_calc] ON dbo.Signspoints
AFTER INSERT AS
UPDATE dbo.SIGNSPOINTS
SET x_sql = Shape.STX,y_sql = Shape.STY
WHERE x_sql is null or y_sql is null

Execute this SQL for the table you are working with. Shape (the standard geometry field name in ArcGIS geodatabase) represents the field of Microsoft Geometry data type. It has STX and STY properties. Make sure you make your calculated columns of sufficient precision which will depend on how exact you want to store the point coordinates. SQL numeric(16, 8) should suffice for your purpose.

Answer 2

Please be aware of that you might need to compress your database in order to be able to see the results, because the business tables in SQL not updated automatically when edits done, and the edits are stored in the Versioned views in the database.