0

I'm using SQL Azure and Entity Framework 6 and need to find which of 5000 areas (polygons) intersects with a point.

My code looks a bit like this

List<object> res = context.Areas
                .Where(a => a.Polygon.Intersects(usersLoction))
                .ToList();

and this translates into SQL that looks a bit like this

SELECT *
FROM Areas a
WHERE a.Polygon.STIntersects(0xE6100000010CBD14289E87AA4A403CACC9610DEB18C0) = 1

Performance on this query is absymal, taking 7+ seconds to search through 5000 areas on SQL Azure. The polygons are generally circuilar.

I'm not a specialist in this area but so far I've worked out that one can create a spatial index that will pre-filter the results set before a complete intersect check in performed on the area. I think that this index would work really well in my case as the areas are generally quite simple and if SQL were to treat each polygon as a rectangle, it would filter down the results very well.

But after a couple of hours researching I've no idea how to create and use a spatial index in Azure.

Could some kind soul please provide the command to add the index that I need?

Mr. Flibble
  • 26,564
  • 23
  • 69
  • 100
  • Is your question about Azure in particular or SQL Server in general? – Michael Diomin May 11 '15 at 14:01
  • 1
    Spatial indexes are supported on SQL Azure V12 just like on SQL Server. Please use the following link for reference. https://msdn.microsoft.com/en-us/library/bb934196.aspx – ckarst May 11 '15 at 15:17
  • @Yuri Diomin. Just Azure. – Mr. Flibble May 11 '15 at 18:46
  • @ckarst - thanks for that. Any tips on creating an index that will do the equivalent of creating a bounding rectangle around the area which the 4 points would be used as an index to match intersecting points? – Mr. Flibble May 11 '15 at 18:49

0 Answers0