POST
|
Thank you, very useful stuff. I will try out both options in the next few days and let you know what I end up doing. For option 1, would this entail setting a trigger that re-creates the table every time there is an edit to either the polygons or the points, or did you have another method in mind? For option 2, could I accomplish this with a Query Layer?
... View more
07-05-2016
12:47 PM
|
0
|
0
|
861
|
POST
|
The final venue is for ArcMap, I want this to be a layer accessible by my users. I am testing the speed by running just the SELECT query in SSMS, then I will go ahead and create the view and test it in ArcMap. -ArcMap loads quickly when I am using the default tables to create the view and SSMS runs the SELECT query in less than a second. -ArcMap loads slowly when I am using the versioned view to create the view and SSMS runs the SELECT query in 29 seconds. What are the regular methods of accessing versioned data? I am new to SQL Server and versioning.
... View more
07-01-2016
09:19 AM
|
0
|
2
|
861
|
POST
|
Hi Everyone, I've got a question that is similar to one that has already been answered here: Optimizing STIntersects queries That thread concludes that you take a massive performance hit if you try to use STIntersects directly on a versioned view in SQL Server Mgmt Studio because it can't use the spatial index of the versioned view, so it's best to use temporary tables to circumvent the problem. They also threw out a possible solution of manually applying your query to the A tables, but there wasn't an example. My trouble is that I am trying to do the same thing, but use my select statement to create a database view. Here is a simplified example: CREATE VIEW dbo.polygonwithpointtotal AS SELECT MIN(poly.OBJECTID), MIN(poly.NAME), SUM(pts.TOTAL), geometry::UnionAggregate(poly.SHAPE) AS SHAPE FROM dbo.polygon_evw as poly LEFT JOIN (SELECT * FROM dbo.points_evw WITH(INDEX(S33_idx)) WHERE ptsid = 1) AS pts ON poly.SHAPE.STIntersects(pts.SHAPE) = 1 GROUP BY poly.OBJECTID And the hang-up is with the STIntersects method. If I run it with the default tables (without the _evw at the end) the query takes about 1 second - a perfectly reasonable load time when working with a view in ArcMap. But when I use the versioned view it takes about 30 seconds and the execution plan makes no mention of utilizing a spatial view even though I'm trying to force it in the query. Any suggestions? Using: ArcGIS for Desktop 10.4 ArcGIS for Server 10.4 Microsoft SQL Server 2012
... View more
07-01-2016
07:14 AM
|
0
|
4
|
2811
|
POST
|
Christian, Thank you for your assistance. I ended up deleting my pa feature class and archive table, re-importing it back to the database, re-enabling archiving, and re-creating my views and they worked! So, I guess I must have corrupted that pa feature class somehow in my SQL experiments. Good thing it was just practice data! Thanks, Chloe
... View more
05-25-2016
03:33 PM
|
0
|
0
|
1157
|
POST
|
Thank you for the suggestion. I tried the STIsValid() function on the intermediate view and on the pa and pa_evw tables and all records returned 1 in the validate column.
... View more
05-25-2016
03:10 PM
|
0
|
2
|
1157
|
POST
|
I am working with MS SQL Server 2012 and ArcMap 10.4. I'm a bit of newbie to SQL so please bear with me. I have created a view in my database using the following query: CREATE VIEW dbo.intermediate AS SELECT MIN(pa.OBJECTID) OBJECTID, MIN(pa.INT_NAME) INT_NAME, MIN(pa.INT_NAME) + ' INT' AS CAMPUS, MIN(pa.ISD) ISD, geometry::UnionAggregate(pa.Shape) SHAPE FROM dbo.pa_evw as pa GROUP BY pa.ISD, pa.INT_NAME Where 'dbo.pa_evw' is an archived view of a polygon layer with a category field called INT_NAME. The query runs without an error in SQL Server Management Studio and the spatial results look how I would expect them to. However, when I bring the layer into ArcMap, I get a drawing error that says there is a "Shape integrity error." Can anyone point me in the right direction?
... View more
05-25-2016
12:45 PM
|
1
|
7
|
3689
|
POST
|
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): How complex the "BOUNDARY" geometry is (number of vertices) 1607 vertices (the original boundary file had 4125) The exact time it takes to query BOUNDARY from a SQL prompt I put just the SELECT query in a new query in SSMS and the time said 00:00:01 The exact time it takes to draw the view in ArcMap. I don’t know how to measure this – would love to learn though. The spatial index options used on the PARCELS geometry column index 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!
... View more
03-31-2016
09:21 AM
|
0
|
0
|
1231
|
POST
|
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?
... View more
03-30-2016
03:52 PM
|
0
|
3
|
4506
|
Title | Kudos | Posted |
---|---|---|
1 | 05-25-2016 12:45 PM |
Online Status |
Offline
|
Date Last Visited |
11-11-2020
02:24 AM
|