Hello all,
I have a grand master plan for how I want to set up our MS SQL database when we migrate our data this summer, and part of that includes having a MASTER database that contains base data (like parcels, roads, cities) that I show as views in other databases based on various geographies. I have written a query that does this using parcels from my MASTER_GIS database and a boundary from OTHER_GDB and stores the view in OTHER_GDB. In ArcMap, however, it runs painfully slow (when it draws at all). See said SQL query below:
USE OTHER_GDB
GO
CREATE VIEW dbo.parcelView
AS
SELECT parc.OBJECTID, parc.PROP_ID, parc.SITE_ADDR, parc.SHAPE
FROM MASTER_GIS.dbo.PARCELS AS parc
JOIN dbo.BOUNDARY AS boundary
ON
parc.SHAPE.STIntersects(boundary.SHAPE) = 1
My question: Is it possible to accomplish this and end up with usable feature class in ArcMap?
Cross-database queries will usually have poor performance, which is part of the reason they're not supported by ArcGIS.
In order to determine if anything could be done to improve this query, we'd need to know:
- V
Some of SQL Server's spatial operations are known to be sensitive to the number of vertices in the polygons involved in the operations, at least this was the case with SQL Server 2008, 2008R2, and 2012 (I haven't revisited the issue with 2014 or newer). There are numerous MSDN threads that discuss various aspects of this issue. Some that come to mind are:
The titles might not sound relevant, but there is some good content in all three of those threads. Personally, I have used the tessellation method put forward by Jakub K. in the aforementioned threads, and it dramatically improved performance.
If you can live with fuzzy results, i.e., a few false positives around the peripheries of your boundaries, it might be worth looking into Filter instead of STIntersects because Filter is a "fast, index-only intersection method."
Of course, tuning spatial indexes can also sometimes improve performance instead of relying on the default spatial indexes that have been chosen for you when creating the feature classes.
Thanks for the responses. I should have mentioned initially - I am a complete novice, so please forgive my ignorance. I’ve only been playing with SQL Server 2012 Express for about three weeks.
Based on your info, I changed the query to use a simpler boundary file and used Filter() instead of STIntersect(). The view now draws and I can do selections. Though it is still a little slower than the unaltered parcels feature class, it sounds like that’s the best I can hope for when attempting cross-database queries - at least until I learn more about optimization. I'm realizing I need to crawl before I run. The new query is:
CREATE VIEW dbo.parcView
AS
SELECT parc.OBJECTID, parc.PROP_ID, parc.SITE_ADDR, parc.SHAPE
FROM MASTER_GIS.dbo.PARCELS AS parc
JOIN dbo.SIMPLE_BOUNDARY AS bdry
ON
parc.SHAPE.Filter(bdry.SHAPE) = 1
Here are the stats (or at least some of them, omissions made where my answer is a simple shrug):
1607 vertices (the original boundary file had 4125)
I put just the SELECT query in a new query in SSMS and the time said 00:00:01
I don’t know how to measure this – would love to learn though.
The default spatial index that is added when you import data, I don’t know how to look at the options or alter the index right now. Something I will look into.
I am very interested in trying the tessellation method. I need to Google about 6000 terms before I even understand what that means, but I’ll get there eventually!