ArcGIS 10.2 SDE Create a view of one feature based on another

2758
10
10-10-2013 12:04 PM
NathanOgden
New Contributor III
I'm new to this so please bear with me.

I saw this cool feature new -> view in ArcCatalog when I right clicked on my database.  I immediately created a view with the sql statement select * from Temp.DBO.NTS_50k where NTS50K = '72E16' and it worked perfectly.

I got to thinking wouldn't it be great if I could create a view of one dataset based on its intersection with another.  That would sure save me a lot of time and not affect my original data.  Can anyone tell me how to create (or if it is possible to create) such a query to make a view where one dataset is a subset of features which intersect another feature found in a geodatabase.

I'm looking for parcels which intersect my county's boundary.  Similar to Select by Location if possible.

Layers Temp.DBO.Parcels, Temp.DBO.CountyBoundary
SQL Server 2012 Std
ArcGIS Server Enterprise 10.2
ArcCatalog 10.2
0 Kudos
10 Replies
VinceAngelo
Esri Esteemed Contributor
You could use a subselect in a SQL query, but it might get expensive, especially
if you need to aggregate the shapes to avoid polynomial expansion.

- V
0 Kudos
NathanOgden
New Contributor III
Please excuse my very basic understanding.

Not sure if expensive will impact me or not.  Usually under 10 users - VMware environment with lots of resources.

Is there a better way to accomplish the goal?
0 Kudos
NathanOgden
New Contributor III
Also, would you have a link to any documentation on creating a subset query?  Thanks in advance.
0 Kudos
VinceAngelo
Esri Esteemed Contributor
Based on the "DBO" I'm assuming that you're using SQL-Server, but you haven't
specified what version is in use.  Your options change slightly with Geometry
implementation.
[Oops, never mind; I guess I mentally edit out anything
that looks like a signature]

There's more Google hits on "SQL" than any other topic (except "hot girls"), but a
search on "learn SQL subquery" seems to return a number of reasonable options.
The trick is making the subquery result into a Geometry that can be used as a
parameter in a  STIntersects (or STCrosses or STContains) query.

Expensive queries (like unioning 1:100k counties to determine a state boundary)
always impact everyone.  If you can add a field or even a list of Boolean fields
that store pre-calculated relationships, you can make a view join hum.

- V
0 Kudos
NathanOgden
New Contributor III
Hi Vince,
Thank you for the helpful posts.  I can see that I didn't explain well enough.

The syntax I think I can work out through trial and error.  It is the spatial intersection that escape me.  In 'pseudo-code' what I am trying to do is:

SELECT * FROM Temp.DBO.NTS_50k WHERE (Temp.DBO.NTS_50k features spatially intersect any part of Temp.DBO.CountyBoundary)

Is the above select statement even possible with MSSQL Standard 2012?
If it is, where would I look for information on what syntax would be required for the 'spatially intersect' part of the query, again keeping in mind what I am curious about is if I can use this to create a view.  If there is a better way to keep an original data set intact, reduce the need for an extra data set and be able to access a subset based on spatial location rather than a query data contained in a field, please let me know.
0 Kudos
NathanOgden
New Contributor III
Vince, I didn't read your previous post well enough so I'm going to look into STIntersects a bit more, but I think I tried it yesterday and got an error so I just assumed my system wasn't capable.  Maybe it was just the syntax.
0 Kudos
VinceAngelo
Esri Esteemed Contributor
For whatever reason, the Microsoft operator functions are case-sensitive, so you
need to be very careful when you type out the query. There's a possibility that
ArcSDE will UPCASE your query, in which case, you'll need to change the WHERE
clause to something mundane ("1=1") then edit the view later.  You should
certainly prototype your query within SQL-Server tools, then once it's working,
paste it into the "Create View" window.

- V
0 Kudos
NathanOgden
New Contributor III
So my latest try is:
SELECT * FROM Temp.DBO.NTS_50k WHERE Temp.DBO.County_Boundary_2005.STIntersection(Temp.DBO.NTS_50k)
and I received the following error:
[[Microsoft][SQL Server Native Client 10.0][SQL Server]An expression of non-Boolean type specified in a context where a condition is expected, near ')'.]

I'm pretty sure I have the Caps etc right because the select statement without the where clause works to create the appropriate view.
0 Kudos
NathanOgden
New Contributor III
SELECT * FROM Temp.DBO.NTS_50k WHERE Temp.DBO.County_Boundary_2005.STIntersection(Temp.DBO.NTS_50k)=1

Results in the error below in the image
0 Kudos