X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Views And Indexes, Any Versions    
post Aug 13 2019, 10:13 PM

Posts: 303
Joined: 4-April 12
From: Bendigo, Australia

I have a problem in using SQL Server 2014 as an input to ArcGIS and am trying to figure out what is happening and how to work around it.

The background is that I have a SqlServer table (Bores) that includes a geography field of point data (PK is BoreID) and a table (WaterLevels) that includes time series data for those points (BoreID is a FK in this table; WaterLevelID is the PK). I have created a view (vewWaterLevels) that relates these tables into a flat structure for use in the GIS system. WaterLevelID is a unique field in the view. In the GIS I want to do a spatial selection of the data in the view so I can get the results within a particular area, defined by a polygon. I can plot the Bores table and vewWaterLevels in the GiS just fine and can select the points in the Bores table that are contained in my polygon. However, when I do a similar select to get records in the view, it hangs the GIS, or at least chugs away until I give up. I have selected records from the view to include only a subset of bores and can then select the water level results for the bores within my polygon if I have about 10 bores (about 100 records) but if I expand it to 100 bores (about 1000 records) the thing crashes. So I know the process 'works' but something is going on that falls over with even modest amounts of records.

I suspect that the GIS is smart enough to use the index on the table but doesn't in the view and gets its knickers in a twist.

Am I correct that a query against a view will use the indexes of the underlying tables where possible? So that it is smart enough to use the WaterLevelID index when doing a non-spatial query within SQL Server? Does the same hold true for spatial queries? Is there a way to tell SQL Server that the WaterLevelID field is effectively a key field? I can see places where I might not have a single-field key in a view.

If I import my polygons into a SQL Server table with a geography field, could I make the query more efficient? That would be a possible fix, but inconvenient because I would have to store different polygons that come from multiple sources. Currently I just attach to those sources with a database connection in ArcGis and would rather not have to manage them myself.

-- Evan
Go to the top of the page

Posts in this topic

Custom Search

RSSSearch   Top   Lo-Fi    19th January 2020 - 11:06 AM