locked
Separate XY from line midpoint? RRS feed

  • Question

  • I'm using

    selECT TLU_STREAMS.SHAPE.STPointN(TLU_STREAMS.SHAPE.STNumPoints()/2).ToString() 
     from dbo.TLU_STREAMS
      

    where shape is a geometry (line) column on SQL 2008 R2 SP1, and it returns

    POINT (446187.16249999963 4106814.0823999997)
    POINT (422699.66179999989 4107873.7686)
    POINT (432696.38549999986 4105476.2820999995)
    POINT (432710.16449999996 4102557.9557000007)
    POINT (411228.72059999965 4104947.6870000008)
    POINT (429062.23110000044 4097940.5995000005)
    POINT (433141.72030000016 4096615.8533999994)
    POINT (434277.07309999969 4092462.7106999997)
    POINT (418405.86220000032 4091137.3268999998)
    POINT (394743.67920000013 4091663.7971)
    POINT (392993.51999999955 4090712.7621)
    POINT (407832.59190000035 4085435.0735)

    However, what'd I like to do with it is use the XY of a line midpoint and parse the values to their own X_Col and Y_Col. Any ideas?

    Sunday, May 20, 2012 11:48 PM

Answers

  • There's many ways to define the "middle" of a line, but if you're happy with the approach that you're taking at the moment and just want to separate out the X and Y coordinate values, try:

    SELECT
     TLU_STREAMS.SHAPE.STPointN(TLU_STREAMS.SHAPE.STNumPoints()/2).STX AS X_Col,
     TLU_STREAMS.SHAPE.STPointN(TLU_STREAMS.SHAPE.STNumPoints()/2).STY AS Y_Col
    FROM dbo.TLU_STREAMS


    twitter: @alastaira blog: http://alastaira.wordpress.com/

    • Proposed as answer by amber zhang Tuesday, May 22, 2012 2:05 AM
    • Marked as answer by amber zhang Monday, May 28, 2012 7:47 AM
    Monday, May 21, 2012 10:08 AM
    Answerer
  • @tanoshimi that worked PERFECTLY! Wow, very succinct explanation! Now....how to use that in a trigger? For example, I'm using

    INSTEAD OF INSERT   
    AS   
    BEGIN   
      SET NOCOUNT ON;  
      INSERT dbo.TBL_LOCATIONS(TOPO_NAME) 
      SELECT b.NAME
      FROM  
      (  
        SELECT  
      --spatial query, what topo quad is this point in? 
     LEFT OUTER JOIN  dbo.TLU_TOPO_BOUNDS AS b  
          ON b.Shape.STContains(TBL_LOCATIONS.Shape) = 1  
    GO

    to update a column on one table based on point occurring within the polygon (table 2). How could I do the same with the line, that is, I want to populate column [topo_name] in the line table with the name of the [topo] polygon that the midpoint of the line occurs in? This is straightforward if I'm dealing with a polygon like

    update GRSM_WETLANDS_POLY set GRSM_WETLANDS_POLY.QuadName = atbi.dbo.TLU_TOPO_BOUNDS.Name from GRSM_WETLANDS_POLY

    inner join ATBI.dbo.TLU_TOPO_BOUNDS on GRSM_WETLANDS_POLY.GIS_Location_ID = GRSM_WETLANDS_POLY.GIS_Location_ID where (TLU_TOPO_BOUNDS.shape.STContains(GRSM_WETLANDS_POLY.Shape.STCentroid())= 1);

    but not sure how to use the numpoints()/2 in the same context. Thanks for your previous answers!


    • Edited by tpcolson Wednesday, May 23, 2012 2:31 AM
    • Marked as answer by amber zhang Monday, May 28, 2012 7:47 AM
    Wednesday, May 23, 2012 2:30 AM

All replies

  • There's many ways to define the "middle" of a line, but if you're happy with the approach that you're taking at the moment and just want to separate out the X and Y coordinate values, try:

    SELECT
     TLU_STREAMS.SHAPE.STPointN(TLU_STREAMS.SHAPE.STNumPoints()/2).STX AS X_Col,
     TLU_STREAMS.SHAPE.STPointN(TLU_STREAMS.SHAPE.STNumPoints()/2).STY AS Y_Col
    FROM dbo.TLU_STREAMS


    twitter: @alastaira blog: http://alastaira.wordpress.com/

    • Proposed as answer by amber zhang Tuesday, May 22, 2012 2:05 AM
    • Marked as answer by amber zhang Monday, May 28, 2012 7:47 AM
    Monday, May 21, 2012 10:08 AM
    Answerer
  • If you want to get the exact middle of the line, it's easy combining STLength and LocateAlongGeom from Sql Server Spatial Tools
    Monday, May 21, 2012 10:32 AM
  • @tanoshimi that worked PERFECTLY! Wow, very succinct explanation! Now....how to use that in a trigger? For example, I'm using

    INSTEAD OF INSERT   
    AS   
    BEGIN   
      SET NOCOUNT ON;  
      INSERT dbo.TBL_LOCATIONS(TOPO_NAME) 
      SELECT b.NAME
      FROM  
      (  
        SELECT  
      --spatial query, what topo quad is this point in? 
     LEFT OUTER JOIN  dbo.TLU_TOPO_BOUNDS AS b  
          ON b.Shape.STContains(TBL_LOCATIONS.Shape) = 1  
    GO

    to update a column on one table based on point occurring within the polygon (table 2). How could I do the same with the line, that is, I want to populate column [topo_name] in the line table with the name of the [topo] polygon that the midpoint of the line occurs in? This is straightforward if I'm dealing with a polygon like

    update GRSM_WETLANDS_POLY set GRSM_WETLANDS_POLY.QuadName = atbi.dbo.TLU_TOPO_BOUNDS.Name from GRSM_WETLANDS_POLY

    inner join ATBI.dbo.TLU_TOPO_BOUNDS on GRSM_WETLANDS_POLY.GIS_Location_ID = GRSM_WETLANDS_POLY.GIS_Location_ID where (TLU_TOPO_BOUNDS.shape.STContains(GRSM_WETLANDS_POLY.Shape.STCentroid())= 1);

    but not sure how to use the numpoints()/2 in the same context. Thanks for your previous answers!


    • Edited by tpcolson Wednesday, May 23, 2012 2:31 AM
    • Marked as answer by amber zhang Monday, May 28, 2012 7:47 AM
    Wednesday, May 23, 2012 2:30 AM