[GIS] How to create and use a SQL Server spatial view in ArcMap

arcmapspatial-viewsql server

All,

(using SQL Server 2012, ArcMap 10.1)

I have created a SQL spatial statement to test out how a) the union function works, and b) how creating a view of this data will work with ArcGIS.

a) the function i'm testing is:

SELECT GEOMETRY::UnionAggregate(shape)
FROM CENSUS_TRACTS_2010_2877
WHERE PCT_ASIAN > 5

…which gives the following correct result:

Click image for larger version Name: spatialUnion.png Views: 0 Size: 37.9 KB ID: 25497

b) our database would greatly benefit using a spatial view of our boundary data (using a union, etc.) to display boundaries to our internal users as a single table.

To accomplish this, it seems that a spatial view would be the best use case, as that gives us the option to edit/maintain one file, and the view will serve up a fresh version of the boundaries.

Problem is, when I create the view in SQL Server, it will not show up in ArcMap, and I get the error:

Warning: Could not add the specified data object to the map.

I think I might be creating the view incorrectly, as this is the field properties ArcMap sees from the view:

enter image description here

…which doesn't seem correct.

Can anyone point me to a place that describes how to create / register / use a spatial view in ArcMap / SQL Server 2012?

Thanks!

-mb

Best Answer

Response from the ArcGIS forum post:

"the problem is that your view does not have something ArcMap recognizes as an ObjectID field. ArcMap must have a field that it can use as a unique identifier. It is a little tricky to generate a field using something like a RowID in SQL Server when you are aggregating a single column, but you can do something inelegant like this:

SELECT 1 as ObjectID, GEOMETRY::UnionAggregate(shape) FROM CENSUS_TRACTS_2010_2877 WHERE PCT_ASIAN > 5

This will obviously only work in this case when your view is resulting in a single record.

-Shannon "

http://forums.arcgis.com/threads/87428-Spatial-views-from-SQL-Server-2012?p=309422&posted=1#post309422

UPDATE:

The best way to give ArcMap the ability to use a spatial view is to use a ROW_NUMBER() function to generate OBJECTID's on the features returned in the view.

A good thread that describes this process can be found here:

How to set up an ArcMap Relate with a table from MS SQL Server 2012

Related Question