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