Hi All,
I am after some assistance with getting ArcGIS Desktop to use a spatial index I have set up on a large database with 3 million records.
There query ArcGIS uses (picking up via SQL profiler) is
declare @bb geometry
select @bb = geometry::STGeomFromText('POLYGON((338293500 126983400,338538336 126983400,338538336 126808775,338293500 126808775,338293500 126983400))',0)
select ST_NUM, FEANME, FEATYP, DIRSUF, MUN, COM_NME, ESZ, MSG, MSLINK, TEXT_ID, X_CORD, Y_CORD, FEA_MSLINK, VICMAP_ADDRESS_PFI, VICMAP_ROAD_PFI, CAD_STRING, GEOM, 1 as "ESRI_OID" from (select * from LIVE_MAPDATA.dbo.ADDRESS) a
where GEOM.STIntersects(@bb) = 1
This takes about 29 seconds to run.
I re-write the query with an index hint it returns almost instantly
select ST_NUM, FEANME, FEATYP, DIRSUF, MUN, COM_NME, ESZ, MSG, MSLINK, TEXT_ID, X_CORD, Y_CORD, FEA_MSLINK, VICMAP_ADDRESS_PFI, VICMAP_ROAD_PFI, CAD_STRING, GEOM, 1 as "ESRI_OID" from lIVE_MAPDATA.dbo.ADDRESS a with(index(IX_ADDRESS_SPATIAL))
where GEOM.STIntersects(@bb) = 1
Does anybody know how I can get ArcGIS to make use of the spatial indexing in SQL 2008 for query layers?
Thanks
Andrew Wise
GIS Specialist
Emergency Services Telecommunications Authority
Melbourne, VIC, Australuia