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

[MatthewBaker2]

So what I'm gathering is that spatial views are *not* the way to provide dynamic views of aggregated data that is otherwise being maintained as seperate features...?

[Anonymous User]

Original User: KAdamsHRSA

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

Could you do the "heavy lifting" in a preliminary query, then enclose the results in an outer query that uses the SQL Server ROW_NUMBER() function to generate your final object IDs? 

SELECT ROW_NUMBER() OVER (order by elm_school) as OBJECTID, theData.*
FROM (
[INDENT]SELECT ELM_SCHOOL, GEOMETRY::UnionAggregate(Shape) AS geom
FROM [gissql12].[gdb].[bounds_2012]
where ELEM_NUM > 0
GROUP BY [elm_SCHOOL]
[/INDENT]) theData

[MatthewBaker2]

KAdamsHRSA / Keith! That worked like a charm!!!

This will solve many similar issues for us...many many thank you's for this solution!!!

To create the view, be sure to use the SQL:

create view SchoolBoundaries_Elem as
SELECT ROW_NUMBER() OVER (order by elm_school) as OBJECTID, theData.*
FROM (
SELECT ELM_SCHOOL, GEOMETRY::UnionAggregate(Shape) AS geom
FROM devdbname.planning.SCHOOLBOUNDARIES_ALL
where ELEM_NUM > 0
GROUP BY [elm_SCHOOL]
) theData

-m

[Anonymous User]

Original User: mbakerDPS

Keith et al, I spoke too soon...

I get an error when trying to create a view from this query:

"SQL text cannot be represented in the grid pane and diagram pane."

any thoughts on that one?

-m

[MatthewBaker2]

Here's an issue:

The OBJECTID field created in that view is of DOUBLE type - which isn't recognized by ArcGIS as a valid OBJECTID field (needs to be of Object ID data type).

Is there a way to create this view such that the OBJECTID field is of Object ID data type?

Thanks again!

-m

[Anonymous User]

Original User: vangelo

In order to qualify as a registered rowid column, it must be an integer (4-byte) and NOT NULL and contain unique values which are repeated in successive queries. -V

[MatthewBaker2]

Vince et al,

Can you think of a way to convert the dynamically generated OBJECTID in the above solution to INT?

I've tried a few methods to CONVERT to INT, but ArcGIS reads a SQL Server INT-type column as LONG INTEGER...thus not reading as an OBJECTID field...

thank you again!

-m

[Anonymous User]

Original User: mbakerDPS

I'm going to run this SQL after I create a table - and am looking to move to FME Workbench where I can build this into a procedure:

ALTER TABLE tableName ADD OID int IDENTITY(1,1)
alter table tableName
add constraint PK_primaryKeyName primary key(OID)

This will create an OID column that ArcGIS recognizes...

Will have to do more testing, but seems this might work for now...