Spatial views from SQL Server 2012

[Anonymous User]

Original User: mbakerDPS

All,

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 result:

[ATTACH=CONFIG]25497[/ATTACH]

b) our database will require using a view of spatial data (using a union, etc.) to serve boundaries to our internal users.

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:

[ATTACH=CONFIG]25498[/ATTACH]

...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

[ShannonShields]

Matthew,

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

[Anonymous User]

Original User: mbakerDPS

Thanks for your help, Shannon...

Here's the result I get back:

[ATTACH=CONFIG]25554[/ATTACH]

...definitely looks strange...

I think I might have to try a more realistic example to see what I get back.

Also: someone mentioned a tool called 'create spatial view', but I don't see that anywhere in my 10.1 toolbox...

Thanks again!

-m

[MatthewBaker2]

Many thanks, Shannon! That worked great!!!

Next I will try a more realistic example using our school boundaries...I will be in touch!

Thank you again!

-m

[Anonymous User]

Original User: vangelo

That output looked right to me (binary stream, converted to ASCII). What result were
you  expecting?   If you want Well-Known Text back, you need to include it in the query.

You need to decide how you want to use ArcGIS.  If you just want to use Query Layers
with existing SQL geometry tables or views, then you don't need to learn geodatabase
tools.

- V

[MatthewBaker2]

Sorry Vince, it looked wrong because I had a setting wrong in SQL management studio. Shannon's solution worked fine! Thanks!

That output looked right to me (binary stream, converted to ASCII). What result were
you  expecting?   If you want Well-Known Text back, you need to include it in the query.

You need to decide how you want to use ArcGIS.  If you just want to use Query Layers
with existing SQL geometry tables or views, then you don't need to learn geodatabase
tools.

- V

[Anonymous User]

Original User: vangelo

I should point out that this is a very expensive use of views -- If the content of the
base table isn't changing frequently, but frequent queries are made, then you'd be
better off running a script to make a bunch of singleton tables, doing the UNION
processing once, rather than incurring that cost with each query.

- V

[MatthewBaker2]

Yes it is...I'm figuring out how spatial views work in the hopes that we can utilize them "properly"...but with that said, it will all be dependent on the amount the inputs are updated, as you've suggested. Thanks again!

[MatthewBaker2]

Hi Shannon and Vince,

I tried out this function with great success:

SELECT AVG(OBJECTID) AS ID, ELM_SCHOOL, GEOMETRY::UnionAggregate(Shape) AS geom
FROM [gissql12].[gdb].[bounds_2012]
where ELEM_NUM > 0
GROUP BY [elm_SCHOOL]

This groups all elementary school boundary 'chunks' into one geometry by merging features together by their name (this is how it would be done)...boundaries with an ELEM_NUM > 0 are those within our district.

...but you'll notice my OBJECTID hack!

Any thoughts on how to merge geometries together and generate a useful (to ArcMap) OBJECTID? I suppose averaging the object (or some similar function) would be acceptable?

-m

[Anonymous User]

Original User: vangelo

Registered rowid (objectid) columns have a very specific purpose -- they tie the graphics
on the map to the rows in the table.  Not only must the values be NOT NULL and unique,
they also must be reproducible in successive queries.

An avg() function could not be guaranteed to return unique values, and even a min()
would not be reversible without a full table scan. 

You could use views to simplify the creation of "materialized views", but those materialized
views would be real tables with fixed values and storage requiements.

- V