topic Re: Spatial views from SQL Server 2012 in Data Management Questions

Spatial views from SQL Server 2012

Anonymous User — Wed, 26 Jun 2013 14:17:32 GMT

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

Re: Spatial views from SQL Server 2012

ShannonShields — Thu, 27 Jun 2013 01:24:47 GMT

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

Re: Spatial views from SQL Server 2012

Anonymous User — Thu, 27 Jun 2013 16:09:07 GMT

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

Re: Spatial views from SQL Server 2012

MatthewBaker2 — Thu, 27 Jun 2013 16:16:52 GMT

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

Re: Spatial views from SQL Server 2012

Anonymous User — Thu, 27 Jun 2013 16:22:37 GMT

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

Re: Spatial views from SQL Server 2012

MatthewBaker2 — Thu, 27 Jun 2013 16:28:02 GMT

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

Re: Spatial views from SQL Server 2012

Anonymous User — Thu, 27 Jun 2013 18:16:15 GMT

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

Re: Spatial views from SQL Server 2012

MatthewBaker2 — Thu, 27 Jun 2013 18:18:56 GMT

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!

Re: Spatial views from SQL Server 2012

MatthewBaker2 — Thu, 27 Jun 2013 20:37:29 GMT

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

Re: Spatial views from SQL Server 2012

Anonymous User — Thu, 27 Jun 2013 20:47:04 GMT

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

Re: Spatial views from SQL Server 2012

MatthewBaker2 — Thu, 27 Jun 2013 20:51:28 GMT

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

Re: Spatial views from SQL Server 2012

Anonymous User — Mon, 15 Jul 2013 18:34:38 GMT

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

Re: Spatial views from SQL Server 2012

MatthewBaker2 — Fri, 02 Aug 2013 16:02:02 GMT

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

Re: Spatial views from SQL Server 2012

Anonymous User — Fri, 02 Aug 2013 16:24:56 GMT

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

Re: Spatial views from SQL Server 2012

MatthewBaker2 — Fri, 02 Aug 2013 16:36:54 GMT

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

Re: Spatial views from SQL Server 2012

Anonymous User — Sat, 03 Aug 2013 01:04:57 GMT

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

Re: Spatial views from SQL Server 2012

MatthewBaker2 — Mon, 05 Aug 2013 17:52:48 GMT

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

Re: Spatial views from SQL Server 2012

Anonymous User — Fri, 16 Aug 2013 15:48:50 GMT

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