none
New To Bing Maps Need Advise Creating Geo Fences RRS feed

  • Question

  • Hi I new to the world of developing a mapping apps I am creating my own live tracking portal to track the location of gps tracking devices 

    I have been able to get the mapping of the locations working in my development mapping portal i am creating The data is taken form location reocrds stored in my sql database simply using long /lat values recorded for each record

    The next challenge is Geo Fence

    Despite spending considerable time searching the web i am not able to find how i 

    1) Create a Circular Geo Fence that is X distance radius from a location

    2) Store that information into a Geo Fence Table in my SQL DB

    3) Be able to take the GeoFence data created and have it displayed on the Big Map

    4) Trigger off events when a location record is deemed to have either entered or existed a geo fence


    Sunday, June 2, 2013 4:16 AM

Answers

  • Ok. Here is an example using the STIntersects method. Lets say you have a database with a table called MyFences that has three columns, Latitude, Longitude & Radius. The first thing you will want to do is create a new column, say "Fence", which is a SQL Geography type. Once you have that created you can update the table to populate this new column with a circle polygon. You would do this by creating a point geography object using the latitude and longitude values and then buffering it using the radius using a command something like this:

    UPDATE [MyFences] SET [Fence] = geography::Point([Latitude], [Longitude], 4326).STBuffer([Radius]);

    Note that the radius value should be in meters. If you are storing the radius in a different unit of measurement then multiply it by the appropriate conversion (i.e. multiply KM's by 1000). Now that you have a table with all your fences as a Geography type you can easily check to see if a point is within a geofence or not. This type of query would look like this:

    DECLARE @g geography;
    SET @g = geography::Point(testLatitude, testLongitude, 4326);

    SELECT * FROM [MyFences] Where @g.STIntersects([Location]) == 1;

    Note that you will need to put actual numbers in the test latitude & longitude locations. Now you have the basics for the database stuff for doing tests. You should be able to extend this to add in the rest of your functionality.

    Now to connect this to Bing Maps is a bit more work. You will first need to create a web service to connect the database server to the end client application. To keep things simple create a Generic handler (asmx) and in it add your code for connecting to your database. I recommend using Entity Framework 5.0 as it will make things much easier for you. You can then use Linq to SQL to query the database. With the data you get you will need to simplify the response so create a simple class that you will send back from the service. If you want to send the circle fences back then add a property in the class that is a string called WKT. For example:

    public class Response
    {
           public List<Result> Results { get; set; }
    }
    
    public class Result
    {
            public string WKT { get; set; }
    }
    When you go to populate the Result you will need to take the Fences value and use the ToString or the STAsText method to convert the geography object to a string. You can then loop through all the results and do this to create your Response. Then convert your Response to a JOSN string using a JSON Serializer and return it from your service. In your client application you can connect to your service and read in your data. When you get the response loop through it and pass the result WKT values to the WKT Reader/Writer module to render the shapes on a map. http://bingmapsv7modules.codeplex.com/wikipage?title=Well%20Known%20Text%20Reader%2fWriter

    If you send me an email at ricky_brundritt at Hotmail.com I'll send you a copy of a blog post I haven't pushed out yet that shows how to create a nice spatial web service.


    http://rbrundritt.wordpress.com


    Tuesday, June 4, 2013 2:56 PM
  • Are you using MySQL or SQL Server/SQL Azure?  If using SQL Server of SQL Azure then you can make use of the spatial functionality built into the database. This would make this whole process easy. Assuming this is what you are using you can create a new column that is an SQL Geography type and then take your lat/long values and populate this column. Take a look at this documentation on how to do this: http://technet.microsoft.com/en-us/library/bb933811.aspx Note, you will likely want to use an SRID of 4326.

    Once you have this you can create your geofences by either storing a radius in a separate column or by creating a circle polygon for the radius and storing that as an SQL Geography. If you store the radius you can then do a distance search o see if an asset is outside of that radius. http://technet.microsoft.com/en-us/library/bb933808.aspx

    If you are using a circle polygon than you can use the STIntersects method to see if a point intersects with the circle or not. To create the circle you can create a buffer around your point with the radius. Here are some useful documents:

    http://technet.microsoft.com/en-us/library/bb933965.aspx

    http://technet.microsoft.com/en-us/library/bb933962.aspx

    To display the geofence on Bing Maps you can use the STAsText method on the geofence to turn it into a Well Known Text (a way to represent spatial data as text). http://technet.microsoft.com/en-us/library/bb933970.aspx

    You can send this information to your application and display it on Bing Maps using the WKT Reader/Writer module:http://bingmapsv7modules.codeplex.com/wikipage?title=Well%20Known%20Text%20Reader%2fWriter

    As for the trigger you would need to have a method that checks to see if a point is in a geofence when the point updates and then trigger the appropriate event.

     

     


    http://rbrundritt.wordpress.com

    Monday, June 3, 2013 8:43 AM
  • Yes. SQL 2008 has spatial functionality. Although it is not as complete as what's in SQL 2012 and SQL Azure. That said the only thing you will really need to look out for is that your geofence's are not overly large (larger than half a hemisphere) as that is a limitation in SQL 2008.

    http://rbrundritt.wordpress.com

    Tuesday, June 4, 2013 8:45 AM

All replies

  • Are you using MySQL or SQL Server/SQL Azure?  If using SQL Server of SQL Azure then you can make use of the spatial functionality built into the database. This would make this whole process easy. Assuming this is what you are using you can create a new column that is an SQL Geography type and then take your lat/long values and populate this column. Take a look at this documentation on how to do this: http://technet.microsoft.com/en-us/library/bb933811.aspx Note, you will likely want to use an SRID of 4326.

    Once you have this you can create your geofences by either storing a radius in a separate column or by creating a circle polygon for the radius and storing that as an SQL Geography. If you store the radius you can then do a distance search o see if an asset is outside of that radius. http://technet.microsoft.com/en-us/library/bb933808.aspx

    If you are using a circle polygon than you can use the STIntersects method to see if a point intersects with the circle or not. To create the circle you can create a buffer around your point with the radius. Here are some useful documents:

    http://technet.microsoft.com/en-us/library/bb933965.aspx

    http://technet.microsoft.com/en-us/library/bb933962.aspx

    To display the geofence on Bing Maps you can use the STAsText method on the geofence to turn it into a Well Known Text (a way to represent spatial data as text). http://technet.microsoft.com/en-us/library/bb933970.aspx

    You can send this information to your application and display it on Bing Maps using the WKT Reader/Writer module:http://bingmapsv7modules.codeplex.com/wikipage?title=Well%20Known%20Text%20Reader%2fWriter

    As for the trigger you would need to have a method that checks to see if a point is in a geofence when the point updates and then trigger the appropriate event.

     

     


    http://rbrundritt.wordpress.com

    Monday, June 3, 2013 8:43 AM
  • Thanks for your detailed advise,

    I am using SQL 2008 however its not run on Azure its the SQL on my hosted VPS account totally independent to Azure or Microsoft hosted solutions as the front end of the application is written in Cold Fusion.

    Can i assume the advise you have provided is the same regardless of whether its running on Azure or not ??

    Tuesday, June 4, 2013 3:18 AM
  • Yes. SQL 2008 has spatial functionality. Although it is not as complete as what's in SQL 2012 and SQL Azure. That said the only thing you will really need to look out for is that your geofence's are not overly large (larger than half a hemisphere) as that is a limitation in SQL 2008.

    http://rbrundritt.wordpress.com

    Tuesday, June 4, 2013 8:45 AM
  • Thanks for your comments

    I am still in the same place though of not knowing how to actually build the functionality i need

    so in short i am seeking to learn how to 

    1: create a geofence from any given point based on a radus selected by the user

    2. have that created geo fence appear visually on the bing maps api

    3. have each geo fence created stored into the geo fence table of the sql database

    4. then have each new location point sent to the tracking server from the mobile gps devices validate against the list of geo fences to determine if the fence has been intersected or not

    Tuesday, June 4, 2013 10:22 AM
  • Ok. Here is an example using the STIntersects method. Lets say you have a database with a table called MyFences that has three columns, Latitude, Longitude & Radius. The first thing you will want to do is create a new column, say "Fence", which is a SQL Geography type. Once you have that created you can update the table to populate this new column with a circle polygon. You would do this by creating a point geography object using the latitude and longitude values and then buffering it using the radius using a command something like this:

    UPDATE [MyFences] SET [Fence] = geography::Point([Latitude], [Longitude], 4326).STBuffer([Radius]);

    Note that the radius value should be in meters. If you are storing the radius in a different unit of measurement then multiply it by the appropriate conversion (i.e. multiply KM's by 1000). Now that you have a table with all your fences as a Geography type you can easily check to see if a point is within a geofence or not. This type of query would look like this:

    DECLARE @g geography;
    SET @g = geography::Point(testLatitude, testLongitude, 4326);

    SELECT * FROM [MyFences] Where @g.STIntersects([Location]) == 1;

    Note that you will need to put actual numbers in the test latitude & longitude locations. Now you have the basics for the database stuff for doing tests. You should be able to extend this to add in the rest of your functionality.

    Now to connect this to Bing Maps is a bit more work. You will first need to create a web service to connect the database server to the end client application. To keep things simple create a Generic handler (asmx) and in it add your code for connecting to your database. I recommend using Entity Framework 5.0 as it will make things much easier for you. You can then use Linq to SQL to query the database. With the data you get you will need to simplify the response so create a simple class that you will send back from the service. If you want to send the circle fences back then add a property in the class that is a string called WKT. For example:

    public class Response
    {
           public List<Result> Results { get; set; }
    }
    
    public class Result
    {
            public string WKT { get; set; }
    }
    When you go to populate the Result you will need to take the Fences value and use the ToString or the STAsText method to convert the geography object to a string. You can then loop through all the results and do this to create your Response. Then convert your Response to a JOSN string using a JSON Serializer and return it from your service. In your client application you can connect to your service and read in your data. When you get the response loop through it and pass the result WKT values to the WKT Reader/Writer module to render the shapes on a map. http://bingmapsv7modules.codeplex.com/wikipage?title=Well%20Known%20Text%20Reader%2fWriter

    If you send me an email at ricky_brundritt at Hotmail.com I'll send you a copy of a blog post I haven't pushed out yet that shows how to create a nice spatial web service.


    http://rbrundritt.wordpress.com


    Tuesday, June 4, 2013 2:56 PM
  • thanks for your assistance

    i am working though it however each time i run 

    UPDATE [MyFences] SET [Fence] = geography::Point([Latitude], [Longitude], 4326).STBuffer([Radius]);

    sql studio manager crashes (yes i have modified the tablename and column names to reflect my own table) yet it still crashes

    looking forward to your reply

    Wednesday, June 5, 2013 2:27 AM
  • Hi 

    In relation yo connecting to the database using a web service this is not needed as i am programming in Cold Fusion which allows me to query the sql database and populate the values onto the bing map using the bing api,

    so grabbing the values from the sql table for use on the mapping api is a sinch the question is what steps i need to use and what sections of the api scripts i will need in order to have the circle displayed on the map

    Wednesday, June 5, 2013 2:49 AM
  • If cold fusion does the DB connection for you then that should make things easier. As for the crash, are you certain you are using SQL Server 2008 or above?


    http://rbrundritt.wordpress.com

    Wednesday, June 5, 2013 9:07 AM
  • Yes its actually 2012

    i tried similar code to your the other day and it created geomoetry binary entries however that code did not have radius where as yours does and radius is important to me as i want to make circular zones of x metres from given locations

    thanks for all your help so far and looking forward to your reply

    Wednesday, June 5, 2013 1:04 PM
  • Hi Richard,

    I just tried the same using a different SQL database connecting via Studio Manager 2008R2 and had the same issue studio manager crashed when i try and run your update code

    UPDATE [MyFences] SET [Fence] = geography::Point([Latitude], [Longitude], 4326).STBuffer([Radius]);

    Wednesday, June 5, 2013 3:12 PM
  • The update statement works correctly in all versions of SQL I've tested. Maybe try adding a where clause to ensure that all the values are not null.

    http://rbrundritt.wordpress.com

    Monday, June 10, 2013 11:52 AM
  • Hi 

    Its been a while since i revisited this post however my sql manager keeps crashing when i try and run the update query

    do you have an actual update query with actual values that i can transpose and test

    Sunday, August 25, 2013 3:51 PM