1

I am building a C# program that needs to determine if a journey passes through a zone.

I have a database storing 50+ polygons (the zones) in the format of:

Lat  |  Lng  |  ZoneName

I'm using the Google Directions API and decoding the overview_polyline response to receive a collection of Lat & Long coordinates that represent 'the journey'.

So, I need to check if the route (the journey) intersects with any of the polygon zones I have stored within the database.

I wonder if I should take the following approach:

  1. Pull the zone coordinates out of the database to dynamically represent my polygon zone.
  2. For each journey step, determine if the lat & long coordinate is inside that polygon

I see point 2 could be achieved here.

But that could be quite some processing.

A short journey returned about 300 odd lat/long coordinate 'steps' for the polyline & each polygon has about 10 or more lat/long coordinates that comprise the shape boundaries.

Is there a more efficient way of doing this please?

Community
  • 1
  • 1
ChrisCurrie
  • 1,589
  • 6
  • 15
  • 36
  • Can't you use SQL Server spatial types and then use `STIntersects` between your polyline (which you need to pass to a SP or similar) and the polygons? – Marcel N. Aug 07 '14 at 17:24
  • Thanks. To be honest I hadn't even thought about it. I'll have a look into it. Many thanks. – ChrisCurrie Aug 07 '14 at 17:54
  • No problem. You'll get good performance out of it as well. – Marcel N. Aug 07 '14 at 18:41
  • @MarcelN. your suggestion has taken me on an interesting journey and is easily the best approach for my needs. Could you please post post it as an answer so I can accept it? Many thanks. – ChrisCurrie Aug 10 '14 at 13:27

1 Answers1

1

You can use SQL Server's spatial data types (like geometry or geography) to store the zones.

You can then use a function like STIntersects to test an existing polyline against whatever is in the database.

In ADO.NET you can use spatial types via the Microsoft.SqlServer.Types NuGet package.

Marcel N.
  • 13,726
  • 5
  • 47
  • 72