topic Re: Slow response when doing spatial queries. in Data Management Questions

Slow response when doing spatial queries.

DaleShearer — Mon, 23 Dec 2013 13:56:09 GMT

I am using Visual Studio 2010 Professional - Version 10.0.40219.1 SP1Rel. Microsoft .NET Framework - Version 4.5.50938 SP1Rel. ArcGIS 10.1 SP1 for Desktop - Build 3143.

I am doing a spatialquery to retreive all address points within a zipcode boundary, so all points within a polygon.

If I have the point layer and polygon layer loaded in the ArcMap session and the end result is 15,000 points within the polygon it may run 10-15 minutes, if I do it on the server it runs out of memory.

I believe the problem lies in how the Visual Studio is setup, I did not set it up, it was just installed on my machine. In looking for a resolution I found in the Advanced Compiler Settings to change the Target CPU, from the AnyCPU setting it was set on, to x86. After I did that it would run on the server (very slow) and occassionally run out of memory, not great, but an improvement.

It also appears that I go loop the zipcodes it just gets slower and slower. If I close and restart the session, the speed (what there is of it) picks back up and then starts slowing down.

I have always done small spatialqueries, one point, or points in a neighborhood, but not this large of query. Yes, I am releasing the cursor after each spatialquery.

I will post the code I am using, thank you for looking at this.

In the code I am finding all the points within the zipcode boundary and verifying that the zipcode on the point matches the zipcode boundary it is within.

****************************************************************************
Before this code starts I am getting all the zipcodes that I will be looking at which is ZipFeature.
****************************************************************************
       Dim pSpatialQ As ISpatialFilter = New SpatialFilter
            pSpatialQ.SpatialRel = esriSpatialRelEnum.esriSpatialRelIntersects
       Do Until pZipFeature Is Nothing
                If Not (IsDBNull(pZipFeature.Value(pZipFeature.Fields.FindField("Zipcode")))) Then
                    theZipCode = pZipFeature.Value(pZipFeature.Fields.FindField("Zipcode"))
                Else
                    theZipCode = ""
                End If
                pSpatialQ.Geometry = pZipFeature.Shape
                Dim pFeatCur As IFeatureCursor = pAddressesLayer.Search(pSpatialQ, False)
                Dim pFeat As IFeature = pFeatCur.NextFeature
                Do Until pFeat Is Nothing
                    If Not (IsDBNull(pFeat.Value(pFeat.Fields.FindField("Zipcode")))) Then
                        theAddressZipCode = pFeat.Value(pFeat.Fields.FindField("Zipcode"))
                    Else
                        theAddressZipCode = ""
                    End If
                    If theZipCode <> theAddressZipCode Then
                        B.Writeline(pFeat.OID & "," & theAddressZipCode & "," & theAddressStatus & "," & thecounter & ", ")
                        End If
                    pFeat = pFeatCur.NextFeature()
                Loop
                '*******************************************************************
                'Method to clean memory.
                '****************************************************************
                System.Runtime.InteropServices.Marshal.ReleaseComObject(pFeatCur)
                '****************************************************************
                pZipFeature = pZipFeatureCursor.NextFeature()
            Loop

Re: Slow response when doing spatial queries.

VinceAngelo — Mon, 23 Dec 2013 14:08:24 GMT

This really isn't the right forum for posting VB code, but if we're going to be able to
help you at all we'll need to know:

What format(s) your data is in

If ArcSDE, which database(s), with exact version, in which your data resides

What sort of spatial index you've created (with the current index parameters)

How many total features are in each of the layers

An indication of the spatial fragmentation of the points layer (do they draw in

You can improve the legibilty of code by posting it inside a CODE block (the '#'
in the advanced editor UI), which will preserve indentation.

- V

Re: Slow response when doing spatial queries.

DaleShearer — Mon, 23 Dec 2013 14:44:11 GMT

Thanks for the reply, I was unsure where to exactly post my message. I will find out about what you are asking, the database person is out all week so I will have to do some digging. I will be back when I find out something, maybe not everything, but at least it will be a start.

Thank you again. dale,

Re: Slow response when doing spatial queries.

DaleShearer — Mon, 23 Dec 2013 15:14:13 GMT

Hopefully I found out something useful:

What format(s) your data is in:

Point layer:

Projected Coordinate System: NAD_1983_StatePlane_Illinois_West_FIPS_1202_Feet
Projection: Transverse_Mercator
False_Easting: 2296583.33333333
False_Northing: 0.00000000
Central_Meridian: -90.16666667
Scale_Factor: 0.99994118
Latitude_Of_Origin: 36.66666667
Linear Unit: Foot_US

Geographic Coordinate System: GCS_North_American_1983
Datum: D_North_American_1983
Prime Meridian: Greenwich
Angular Unit: Degree

Zipcode layer:

Projected Coordinate System: NAD_1983_StatePlane_Illinois_West_FIPS_1202_Feet
Projection: Transverse_Mercator
False_Easting: 2296583.33333333
False_Northing: 0.00000000
Central_Meridian: -90.16666667
Scale_Factor: 0.99994118
Latitude_Of_Origin: 36.66666667
Linear Unit: Foot_US

Geographic Coordinate System: GCS_North_American_1983
Datum: D_North_American_1983
Prime Meridian: Greenwich
Angular Unit: Degree

If ArcSDE, which database(s), with exact version, in which your data resides - Still Looking, not familiar with servers.

What sort of spatial index you've created (with the current index parameters):

Unfamiliar with this, here goes. I am at the Feature Class Properties of the Point layer - Indexes.

Spatial Index: This feature class on each layer has a spatial index with a different name. The following information is the same

Level 1 - Medium

Level 2 - Medium

Level 3 - Medium

Level 4 - Medium

Cells per object - 16

Then a Bounding box with X-Y, the data is different, do you need that?

How many total features are in each of the layers:

Zipcode - Polygon - 39

Points layer - 125,000

Code loops through each zipcode polygon and does the spatial query on the points layer.

An indication of the spatial fragmentation of the points layer (do they draw in
one pass from left to right, or do they randomly sprinke themselves across the
canvas).

I am not drawing them, I am just kicking out the info on the ones that do not match to a text file.

If I looked up the wrong info on spatial index let me know. Thanks for your time. Dale,

Re: Slow response when doing spatial queries.

DaleShearer — Mon, 23 Dec 2013 15:41:55 GMT

Hi, database platform is SQL Server, both geodatabases are on the same server, both are default versions. I hope this is the correct information you are asking for.

Re: Slow response when doing spatial queries.

DaleShearer — Mon, 23 Dec 2013 15:49:13 GMT

Hi, database platform is SQL Server, both geodatabases are on the same server, both are default versions. I hope this is the correct information you are asking for.

Re: Slow response when doing spatial queries.

VinceAngelo — Mon, 23 Dec 2013 16:43:55 GMT

So the format is "SQL-Server GEOMETRY"

Are you using Personal, Workgroup, or Enterprise ArcSDE?

Is the SQL-Server instance Express or Standard or Enterprise?

I really need to know how spatially fragmented the data is to know if there's any chance
of getting better performance. If you did draw all the features, how would they draw?

Given what you've provided so far, it appears your issue is with Microsoft's spatial index,
not with anything that Esri can control at query time. I would recommend you spend
some time learning how to refine the nominal index construction into something that
is appropriate for the data you have loaded.

It might be useful to copy the features to a file geodatabase, and do the spatial query
there, so you get an idea of what is possible, then tune the GEOMETRY index until you
get performance closer to FGDB.

I really don't think it has anything to do with your compiler settings.

- V

Re: Slow response when doing spatial queries.

DaleShearer — Mon, 23 Dec 2013 17:32:12 GMT

Thank you for the information, the server side is new to me so you have gave me plenty to look up. On the compiler, it was a start for me, so I am glad you pointed out things I can do. I will be back when I get a little farther. Dale,

Re: Slow response when doing spatial queries.

DaleShearer — Mon, 30 Dec 2013 15:09:55 GMT

Hi, I created a file geodatabase and it just flies like it should. The file geodatabase is on my machine and it takes 25 seconds to do what was taking 15 minutes with the data on the server. Not sure how to proceed on the server side as that is not anything I deal with.

Thank you for your time and your knowledge. Dale,

Re: Slow response when doing spatial queries.

DaleShearer — Mon, 30 Dec 2013 15:14:06 GMT

Hi Vince, how do I mark this thread as answered by you? Dale,

Re: Slow response when doing spatial queries.

VinceAngelo — Mon, 30 Dec 2013 15:28:11 GMT

If FGDB flies, it should be possible for a SQL-Server GEOMETRY to fly as well (maybe
not as fast, but it should be able to at least get into the air with the right set of index
options).

I don't ask questions, just answer them, so I've never seen it, but the UI should have a
blue checkmark on all posts in a question thread that you started, much the way all posts
have blue "up" (and sometimes "down", if not greyed out) arrows on the upper-right side
of each post, just below the arrows. If you click on it, it should turn green.

- V

Re: Slow response when doing spatial queries.

DaleShearer — Mon, 30 Dec 2013 15:37:34 GMT

Well, there is a checkmark over there right in plain sight...... Thank you very much. Now when I talk to the server side of the house I know a lot more than I did.

If you want to take a look at another problem, I have a similar question on ArcGIS for Desktop - General: I am just trying to count files in folders, runs fast on my machine, slow on the server.

Thank you again for the help, it got me a lot further along. Dale