We have a SQL Server 2008R2 database where point coordinates are stored as decimal degrees.
I want to create an Esri map service which shows those points.
As the point data is dynamic I decided to use query layer to the table with coordinates and convert coordinates to SQL Server spatial type on the fly.
I tested both spatial types – geometry and geography and both allow to display points in ArcMap. Both types use WGS84 coordinate system because coordinates are longitude and latitude, but map service is in Web Mercator (3857).
What is should be preffered type of SQL Server spatial type to use to display longitude and latitude on the fly in Esri map service?
Best Answer
The only way I can think of converting Lat/Lon columns to geometry on the fly would be to create a view of your table and create a geometry column using STGeomFromText (latColumn, lonColumn, SRID). There is an article here that describes how this is done. In your case, you would publish the view in the method I describe below...
I publish views of SQL Spatial tables (geometry already created) joined to create 'pseudo-feature classes' that I bring into ArcMap then publish to ArcGIS Server.
The view brings 2 tables together using a Union function, then adds an OBJECTID column (using a RowNum function).
The underlying spatial data is of geometry type and is stored in a projected coordinate system of State Plane Colorado Central (SRID:2877).
I use the MXD to project these points on the fly to Web Mercator. (I don't use a query layer, but add them as if they were a feature class from the SDE connection to the same SQL server database - ArcMap then interprets them as query layers)
I then publish these MXD's to ArcGIS Server as if they were standard SDE layers.
I've had no issues displaying or querying these datasets.