1

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?

6
  • I don't know the ESRI preferred type, but my default is always use the proper data type to avoid potential issues down the track. So in this case it is geography.
    – MickyT
    Commented Aug 4, 2015 at 19:20
  • What are "potential issues" with using geometry with geographic coordinates?
    – Marcin
    Commented Aug 4, 2015 at 19:23
  • I've been using geometry just fine... Commented Aug 4, 2015 at 19:32
  • 1
    How many points are in this table? On-the-fly projection is a boat-anchor on performance for web-mapping, so it's not recommended. If you store the data in a projected coordinate system, the point is moot.
    – Vince
    Commented Aug 4, 2015 at 19:54
  • @Vince, we're just starting and have about 300 points so far. What is your suggestion to avoid on-the-fly projection? I haven't found native SQL Server 2008 function which project data from WGS84 to projected CS.
    – Marcin
    Commented Aug 4, 2015 at 20:00

1 Answer 1

2

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.

6
  • Where is your ArcGIS Server service being consumed? I am considering just using SQL Server Spatial to feed out coordinate info and a web map API to consume and generate geometry objects. My assumption would be that is easier than adding the ArcGIS part in the middle if I am just displaying a web map.
    – Branco
    Commented Aug 4, 2015 at 19:45
  • @Branco we're using the ArcGIS Javascript API and these are services that will be used in our public-facing school finder application... but they work in ArcMap just the same. However, Geoserver has a SQL server extension so that yields another set of options! Commented Aug 4, 2015 at 19:51
  • @mapBaker, thanks for the answer - it proves that SQL Server geometries can successfuly be used in Esri services. However, it doesn't answer my question as you already have geometries in SQL Server and we have only coordinates.
    – Marcin
    Commented Aug 4, 2015 at 19:56
  • @mapBaker That sounds interesting. I am looking to implement something using just my web host and the SQL Server back-end to do a few personal web maps. It would only be web app + DB solution though. @ Marcin I was reading over this post (gis.stackexchange.com/questions/30409/…) you might find it useful, too.
    – Branco
    Commented Aug 4, 2015 at 19:57
  • @Marcin Sorry I missed that part - see my updated answer / first paragraph... does that make sense? Commented Aug 4, 2015 at 20:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.