Slow response when doing spatial queries.

[DaleShearer]

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

[VinceAngelo]

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

one pass from left to right, or do they randomly sprinke themselves across the
canvas).

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

[DaleShearer]

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,

[DaleShearer]

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,

[DaleShearer]

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.

[DaleShearer]

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.

[VinceAngelo]

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

[DaleShearer]

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,

[DaleShearer]

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,

[DaleShearer]

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