locked
1 record per X mile Radius RRS feed

  • Question

  • My table setup.
    --------------------------
    lat (float)
    lon (float)
    location  (varchar)
    GeoLocation (geography)

    The GeoLocation column above is set as:
    ----------------------------------------
    SET [GeoLocation] = geography::STPointFromText('POINT(' + CAST([lon] AS VARCHAR(20)) + ' ' + CAST([lat] AS VARCHAR(20)) + ')', 4326)


    ---------------------
    My Current Query:
    ---------------------
    SELECT * FROM TheTablename WHERE (lat BETWEEN 40.03497548644043 AND 43.78596734966379) AND (lon BETWEEN -74.2606614248359 AND -70.7450364248359)

    Returns all records within the boundries of the Lat and Lon. Works great.
    -------------------------------------
    What I need to figure out is:
    -------------------------------------
    How do I modify the above query to get 1 record per 20 mile radius. It would be a plus if I could get the center Lat,Lon of that radius.

    For example: If I run the above query, 1 get 500 records back. I want to group them on the SQL server by 1 record per 20 mile radius. I would think it is possible to do this with the GeoLocation column but I just don't know how.

    Any help is appreciated.

    Thanks

    Thursday, May 30, 2013 6:29 AM

Answers

  • First of all, you have fallen into the "seductive" trap of treating spherical coordinates (geography data type) as planar in your "current" query.  This can yield excellent query performance but is fundamentally and technically incorrect, since the edges of the implied box are treated as as straight lines.  In the geography type, polygon edges are correctly defined as minimum distance arcs (in SQL Server they are referred to as Great Elliptic Arcs for ellipsoidal data; on the sphere they are called Great Circle minor arcs). To correct this current query for this you will need to create a geography polygon using the "box" corner coordinates.

    Additionally, I'd recommend that you use the Point() constructor instead of the STPointFromText() constructor.  Here is your original T-SQL:

    SET [GeoLocation] = geography::STPointFromText('POINT(' + CAST([lon] AS VARCHAR(20)) + ' ' + CAST([lat] AS VARCHAR(20)) + ')', 4326)

    It can be rewritten as follows:

    SET [GeoLocation] = geography::Point(lat, lon, 4326)
    This form is considerably easier to read and faster to execute.

    Regarding the query refactoring to "...get 1 record per 20 mile radius"... I am unclear on how you wish to aggregate multiple point records into a single record.  It's easy to create a 20 mile radius polygon around a given point.  What I don't yet understanding is what the expected the single (1 record) result is to represent?  Can you provide additional clarification? Maybe an example?

    Thanks,

    -Ed


    Ed Katibah Spatial Ed Blog


    • Edited by Spatial Ed Thursday, May 30, 2013 11:01 PM Addition
    • Proposed as answer by Fanny Liu Friday, May 31, 2013 6:24 AM
    • Marked as answer by Fanny Liu Wednesday, June 5, 2013 1:33 AM
    Thursday, May 30, 2013 10:56 PM

All replies

  • First of all, you have fallen into the "seductive" trap of treating spherical coordinates (geography data type) as planar in your "current" query.  This can yield excellent query performance but is fundamentally and technically incorrect, since the edges of the implied box are treated as as straight lines.  In the geography type, polygon edges are correctly defined as minimum distance arcs (in SQL Server they are referred to as Great Elliptic Arcs for ellipsoidal data; on the sphere they are called Great Circle minor arcs). To correct this current query for this you will need to create a geography polygon using the "box" corner coordinates.

    Additionally, I'd recommend that you use the Point() constructor instead of the STPointFromText() constructor.  Here is your original T-SQL:

    SET [GeoLocation] = geography::STPointFromText('POINT(' + CAST([lon] AS VARCHAR(20)) + ' ' + CAST([lat] AS VARCHAR(20)) + ')', 4326)

    It can be rewritten as follows:

    SET [GeoLocation] = geography::Point(lat, lon, 4326)
    This form is considerably easier to read and faster to execute.

    Regarding the query refactoring to "...get 1 record per 20 mile radius"... I am unclear on how you wish to aggregate multiple point records into a single record.  It's easy to create a 20 mile radius polygon around a given point.  What I don't yet understanding is what the expected the single (1 record) result is to represent?  Can you provide additional clarification? Maybe an example?

    Thanks,

    -Ed


    Ed Katibah Spatial Ed Blog


    • Edited by Spatial Ed Thursday, May 30, 2013 11:01 PM Addition
    • Proposed as answer by Fanny Liu Friday, May 31, 2013 6:24 AM
    • Marked as answer by Fanny Liu Wednesday, June 5, 2013 1:33 AM
    Thursday, May 30, 2013 10:56 PM
  • Hi Ed,

    Thanks for your reply.

    This is my attempt @ doing a server-side clustering. The reason being, my app is suffering from bandwidth and usability issues when the map is zoomed out as it is getting back thousands of records back from the server. Client side clustering is really not working all that good either.

    Currently, I have set Zoom Limits for the map but the most asked feature is the ability to be able to zoom out further. As it sits currently, if I let users zoom out further, the app performance degrades drastically as thousands of records try to push markers into the Bing Map.

    So, If I can check the Zoom level and construct a Query to get 1 record per 20 mile radius/rectangle, it would be a good solution to the problem.

    Hope that makes it a bit clear as to what I am trying to accomplish. 

    Thanks Much.

    Friday, May 31, 2013 4:00 PM
  • OK, this is what I suspected that you were trying to accomplish. Let me contact colleagues on the Bing Maps team regarding clustering.  I may take a couple of days, so please be patient.

    Thanks,

    -Ed


    Ed Katibah Spatial Ed Blog

    Friday, May 31, 2013 4:28 PM
  • Take a look at Bing Maps V7 - Modular design and Client Side Clustering and see if this assists you in your clustering endeavors.

    Thanks,

    -Ed


    Ed Katibah Spatial Ed Blog

    Friday, May 31, 2013 5:24 PM
  • Hi Ed,

    I have tried it before. But client side clustering requires that you still download the full data-set of the result and do the clustering on the device. My idea was to eliminate this on the client side so that the experience is smoother and the performance better. For certain zoom levels, I have over 10,000 pieces of data going back to the client mobile device and have actions on "viewchangeend" event where it will hit the server again to ask for results on the new BoundingBox lat/lon. The database is still growing and could mean tons of results in a fully zoomed out view.

    Do you think there no current method to do what I want server-side?

    Thanks Much.

    Friday, May 31, 2013 6:06 PM
  • This is an interesting area.  I've written some T-SQL-based server-side clustering stored procedures.  While they work as intended, they are still far to compute-intensive.  This may require a rewrite using C#.  When I get a chance (maybe next week), I'll share my code with you and you can see what I have managed to accomplish to date. This approach might work for you if we can get the performance a bit snappier...

    Thanks,

    -Ed


    Ed Katibah Spatial Ed Blog

    Friday, May 31, 2013 6:12 PM
  • Thanks Ed.

    I will be on the lookout for it.

    Friday, May 31, 2013 7:08 PM