Split map area in rectangles to control data synchronization RRS feed

  • Question

  • Hi,


    I'm working on an application that will return some data from my database based on the user current location (lat/lng) and for this I need to synchronize my database with another datasource (this will be made with C# and WebServices).


    So I have two questions:

    1- Considering that I have two columns in my database that will store lat and lng values, how can I search for registries that their lat/lng are inside a radius of 1 kilometer of a specific lat/lng?

    2- This is more about a strategy I think, but...I need to control data synchronization per map areas, instead of the full map at once. How do you suggest me to split the map so I can sync the data for each area in a sequence? I'm thinking on a map grid, so each rectangle will be an area to be synchronized, but I don't know if there's any pattern (start point, rectangle size, etc).


    Can you help me, please?

    Att., Juliano Nunes
    Saturday, June 11, 2011 10:11 PM

All replies

  • 1.) If you use the geography datatype rather than separate columns for lat/lng, this becomes a simple query:

    SELECT * FROM Registries WHERE location.STDistance(@SpecificPoint) < 1000

    If you want to continue to store separate lat/lng numeric fields, the most common approach for distance calculation is to make an approximation based on the Haversine formula, which gives the great circle distance between two lat/lng points on the surface of a sphere.


    2.) I don't understand what you mean by "data synchronisation" - is your client app trying to update a subset of the data in the database, as a series of discrete rectangular areas? Or is it just reading the data? What exactly are the two data sources being "synchronised"? Perhaps can you explain a bit more what you're trying to do.

    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Sunday, June 12, 2011 7:04 AM
  • Hi,

    First thank you for answering my questions.

    Let me explain better how the second question works.


    My application lets users get information about companies in their current location (lat/lng). These companies are added to my database after another application of mine consume a public webservice that provides data about companies  and is always updated (new companies are added or removed from the webservice).

    I have to keep my database synchronized with this webservice data (for synchronized I mean to have almost the same data, but in my database the performance will be better than consuming the external webservice every time), but the search is limited per radius, so I can't make a search for the entire city or state.

    The idea I had is to split the entire globe map into many rectangles of a fixed size (5 x 5 kilometers, for example), so the other application I've built will work as a synchronization robot and get each of these rectangles coordinates to make a search for updated data from the webservice, add or remove the companies from my database and mark the rectangle as synchronized (using date and time).


    That's the whole idea.


    Can you help me, please?

    Att., Juliano Nunes
    Sunday, June 12, 2011 6:14 PM