topic Re: ArcGIS 10.2 SDE Create a view of one feature based on another in Data Management Questions

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

NathanOgden — Thu, 10 Oct 2013 19:04:15 GMT

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

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

VinceAngelo — Thu, 10 Oct 2013 19:49:29 GMT

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

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

NathanOgden — Thu, 10 Oct 2013 19:57:45 GMT

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?

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

NathanOgden — Thu, 10 Oct 2013 20:18:32 GMT

Also, would you have a link to any documentation on creating a subset query? Thanks in advance.

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

VinceAngelo — Thu, 10 Oct 2013 20:51:52 GMT

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

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

NathanOgden — Fri, 11 Oct 2013 14:08:50 GMT

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.

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

NathanOgden — Fri, 11 Oct 2013 14:15:43 GMT

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.

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

VinceAngelo — Fri, 11 Oct 2013 15:27:22 GMT

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

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

NathanOgden — Fri, 11 Oct 2013 17:02:14 GMT

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.

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

NathanOgden — Fri, 11 Oct 2013 17:06:30 GMT

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

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

VinceAngelo — Fri, 11 Oct 2013 18:11:04 GMT

Look at the examples in the Microsoft documentation for their implementation use
paradigm. The STIntersects method is a property of the geometry column, so the
WHERE clause should look like

WHERE geometrycolumnname.STIntersects( someothergeometry )

The function itself returns Boolean (nulls allowed), but getting the geometry out of
another table (which of course my have the same SRID) and into that function is
likely going to be the trick, since I've never seen a subselect inside parenthesis
(though it certainly wouldn't hurt to try).

- V