ArcGIS Server 10.1.1 SQL Server Standard 2012 Current workflow is user submits a table (text file) containing lat-long data (same spatial reference each time); SQL Server Integration services pushes this data into an SQL table. Data is viewed as an XY Event Layer in ArcGIS Desktop and published to AGS.

The ideal workflow is to to utilize a Query Layer instead of a XY Event Layer (according to ESRI).

My question is what is the best to get these XY (Lat-Long) fields into the spatial column?

At this point in the workflow, I think we would be limited to SQL commands (or something within SSIS); the table is a text file going into SSIS so I doubt we'd be able to add an appropriately formatted column/field which would could then be mapped/loaded into the spatial column of the target SQL table.

[The question is much less about ArcGIS Server but I added that background for context.]

  • I can only speak from Oracle experience, but we had to write packages and triggers that took the XY coordinates and built an Oracle spatial SDO_GEOMETRY object out of it. I suspect there's some similar methods in SQL Server that can convert text to a geometry object. – Mintx May 3 '13 at 16:34

The answer to this was to simply create a computed column using the Geometry type in SQL. The computed text in this case was: ([geometry]::STGeomFromText(((('POINT('+CONVERT(varchar,[Long]))+' ')+CONVERT(varchar,[Lat]))+')',(4326)))

Now the table can be added as a query layer within ArcMap. Works like a charm.

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.