none
Testing of coordinates fall within a buffered Linestring.

    Question

  • Hi Folks

    I want to determine if some points, represented by Longitude and latitude coordinates fall within a 3 km buffer around a line string. 

    /* Define the road with a line string & Longitudes and Latitudes */
    DECLARE @Test_road geography;
    SET @Test_road = geography::STLineFromText ('LINESTRING(175.01991 -37.234095, 175.111213 -37.395281, 175.182932 -37.608155)', 4326);
    
    /* Add 3 Km buffer to each side of the line string */
    DECLARE @Routebuffer geography;
    SET @Routebuffer = @Test_road.STBuffer ('3000');
    

    I have a table with  Latitudes and longitudes (see CREATE TABLE  code for table below).  I need to determine which of the coordinates fall with the area if the  buffered on the line string. 

    From what I have read, I think this is a " STcontains () scenario". However, I am struggling to get this to work.  Can someone help me out?

    Regards

    Steve

    CREATE TABLE [dbo].[Coordinates](
         [machine_id] [bigint] NULL,
         [event_timestamp] [datetime] NULL,
         [latitude] [numeric](9, 6) NULL,
         [longitude] [numeric](9, 6) NULL
    ) ON [PRIMARY]
    
    GO
    INSERT [dbo].[Coordinates] ([machine_id], [event_timestamp], [latitude], [longitude]) VALUES (17183, CAST(0x0000A1390009CBD0 AS DateTime), CAST(-37.232541 AS Numeric(9, 6)), CAST(175.019043 AS Numeric(9, 6)))
    GO
    INSERT [dbo].[Coordinates] ([machine_id], [event_timestamp], [latitude], [longitude]) VALUES (17183, CAST(0x0000A1390009D788 AS DateTime), CAST(-37.234595 AS Numeric(9, 6)), CAST(175.020223 AS Numeric(9, 6)))
    GO
    INSERT [dbo].[Coordinates] ([machine_id], [event_timestamp], [latitude], [longitude]) VALUES (17183, CAST(0x0000A1390009E340 AS DateTime), CAST(-37.236866 AS Numeric(9, 6)), CAST(175.020545 AS Numeric(9, 6)))
    GO
    INSERT [dbo].[Coordinates] ([machine_id], [event_timestamp], [latitude], [longitude]) VALUES (17183, CAST(0x0000A1390009EEF8 AS DateTime), CAST(-37.239121 AS Numeric(9, 6)), CAST(175.021053 AS Numeric(9, 6)))
    GO
    INSERT [dbo].[Coordinates] ([machine_id], [event_timestamp], [latitude], [longitude]) VALUES (17181, CAST(0x0000A1390009D530 AS DateTime), CAST(-37.233996 AS Numeric(9, 6)), CAST(175.019976 AS Numeric(9, 6)))
    GO
    INSERT [dbo].[Coordinates] ([machine_id], [event_timestamp], [latitude], [longitude]) VALUES (17181, CAST(0x0000A1390009E0E8 AS DateTime), CAST(-37.236229 AS Numeric(9, 6)), CAST(175.020500 AS Numeric(9, 6)))
    GO
    

    Monday, January 14, 2013 3:10 AM

Answers

  • I'm not sure, but I believe you want

    Select *, 
      @Routebuffer.STContains(geography::Point(latitude, longitude, 4326)) As InRouteBuffer 
    From dbo.Coordinates;

    Tom

    • Marked as answer by Steve_Fox Monday, January 14, 2013 4:23 AM
    Monday, January 14, 2013 3:49 AM