locked
Spatial points in one table, polygons in another, finding distance between RRS feed

  • Question

  • Hello everyone, I'm having a tough time grasping the best (or any!) way of doing something here.

    I have a breadcrumb trail of points for a vehicle in one table.
    I have polygon regions in another table.
    I want to find the breadcrumb that is the furthest away from a specific polygon that is also inside one of the other polygons.

    I just need the row that is the furthest and then I can (hopefully) figure out the rest. The goal is to decide when a vehicle is empty or loaded.  The vehicle is considered loaded when it reaches the furthest out polygon from the initial polygon.  There are only a few polygon regions but hundreds of points.

    Can anyone point me in the right direction?  Thanks for your time!
    Thursday, March 5, 2009 2:41 AM

Answers

  • Hi there,

    'Nearest neighbour' queries are very common in spatial applications - i.e. "Find me the feature that is closest to X". If I understand you correctly, you want to perform a 'farthest neighbour' query - which is a little less common but still quite straightforward.

    Assuming that you want to identify the point from the table MyTable that lies furthest away from @Polygon, you can do something like this:

    SELECT TOP 1 * FROM MyTable 
    ORDER BY PointColumn.STDistance(@Polygon) DESC 

    Having identified the appropriate point, you can then use STContains() (STIntersects() if you are using the geography datatype) to test whether that point lies in a second polygon for the second part of your question.

    Does that help?

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by myriad Thursday, March 5, 2009 6:14 PM
    Thursday, March 5, 2009 8:18 AM
    Answerer

All replies

  • Hi there,

    'Nearest neighbour' queries are very common in spatial applications - i.e. "Find me the feature that is closest to X". If I understand you correctly, you want to perform a 'farthest neighbour' query - which is a little less common but still quite straightforward.

    Assuming that you want to identify the point from the table MyTable that lies furthest away from @Polygon, you can do something like this:

    SELECT TOP 1 * FROM MyTable 
    ORDER BY PointColumn.STDistance(@Polygon) DESC 

    Having identified the appropriate point, you can then use STContains() (STIntersects() if you are using the geography datatype) to test whether that point lies in a second polygon for the second part of your question.

    Does that help?

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by myriad Thursday, March 5, 2009 6:14 PM
    Thursday, March 5, 2009 8:18 AM
    Answerer
  • Arigatou Tanoshimi-sensei! 

    I'll get started on  .NET to grab the polygons and set up the query then.  Is it possible to grab only the points that are in _any_ polygon from another table? Or just the furthest point that is in any of the polygons? I think I'm dreaming now, but I should ask anyway.

    Thanks again for your time!

    Thursday, March 5, 2009 5:27 PM
  • Doitashimashite!

    Certainly, you can limit the query to only be those points in *any* polygon - you basically need to do a cross-join between the points and the polygon tables, and go through each one to determine whether the point lies in any polygon. It might not be the best way to do this in terms of performance though...
    What you'll find as you go along is that there is a black art to getting spatial queries to perform effectively - some parts are obvious, others much less so (and spatial indexing is about as black as they come!). I would concentrate on getting something that gets the right result to start with - then you can look at ways to make it perform faster.

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Thursday, March 5, 2009 5:48 PM
    Answerer
  • Tanoshimi-sensei no atama ii desu!

    I used an inner join to replace the @polygon with a geography field from the other table and it works just fine. I can get the point distances from the polygon. Excellent!

    If I can learn the black art of spatial goodness then I'll  have a secure job!
    Thursday, March 5, 2009 6:27 PM
  •  Just to complete this topic, here is an example I'm using now:

    select top 1 GPS_POINT_DATA.STDistance(gpsl.shape_data)  Distance,gps.*

    FROM  gps_points gps

    inner join gps_locations gpsL on gpsL.LOCATION_ID = 9

    where TIME_STAMP between '2009-03-03 07:00:00' and '2009-03-04 06:59:59'

    and gps.LOCATION_ID <> 0 and gps.LOCATION_ID <> 9

    ORDER BY Distance DESC



    The gps.LOCATION_ID is preprocessed to know if it's in a polygon in the gps table.

    Thursday, March 5, 2009 6:42 PM