locked
Intersect linestring to multipolygon RRS feed

  • Question

  • Hi all,

    i got a problem! I got map of Italy in a multipolygon form. My target is to build a linestring that intersect the map and found the intersection point with the coast. It would be also acceptable obtain a random point of the linestring within the map.

    does anyone got an idea?

    Thanks to all.

    Massimo

     

    Thursday, August 5, 2010 10:55 AM

Answers

  • If you define a linestring that crosses over the multipolygon then you can use STIntersection() to determine the section of that linestring that intersects Italy.

    To then find out the point on that linestring that lies on the coast, you simply need to choose either the start or end point of that section of intersecting linestring, using STStartPoint() / STEndPoint().

    Example code follows:

    -- Declare a multipolygon representing Italy
    DECLARE @Italy geography;
    SET @Italy = geography::STGeomFromText(
    'MULTIPOLYGON(
    ((18.764648437499993 40.39676430557204, 15.908203125 41.4262531950727, 14.150390625000014 42.43967417814943, 13.666992187499996 43.500752435690394, 12.612304687499991 44.13491344375071, 12.348632812499998 45.41387646082107, 13.139648437500009 45.78284835197675, 12.788085937499988 46.513515580597364, 10.19531249999999 46.57396679652779, 7.119140625000007 45.90529985724797, 7.3388671875 43.85037449930261, 8.92089843749999 44.386691502152054, 10.415039062500018 43.882057303905356, 10.766601562500009 42.924251753870685, 13.007812500000012 41.095912056395456, 15.732421875000007 39.79165483525342, 16.34765624999999 38.873928539236296, 15.512695312500012 38.15183740300677, 15.952148437500002 37.84015683604134, 17.226562499999996 39.04478604850142, 17.094726562500004 39.419220736559545, 16.523437499999986 39.82541310342478, 16.918945312500007 40.26276066437182, 18.369140625 39.75787999202175, 18.764648437499993 40.39676430557204)),
    ((14.798583984375007 38.0956597552956, 13.469238281249984 38.05241677186484, 13.04077148437499 38.22523523907684, 12.447509765624988 37.68816746840803, 13.941650390625014 37.05079312980657, 14.271240234374988 37.04202441635082, 14.886474609375004 36.63757008123925, 15.41381835937499 37.05079312980657, 15.128173828125014 37.49665234123336, 15.567626953125002 38.26837588020475, 14.798583984375007 38.0956597552956)),
    ((9.239501953125009 41.21585377825921, 8.47045898437501 40.801335759791996, 8.17382812500001 40.784700818417456, 8.492431640624995 40.14948820651523, 8.481445312500002 39.61838363831913, 8.338623046875016 38.92950241638659, 8.865966796875 38.87820499706148, 9.60205078124999 39.15136267949031, 9.865722656250016 40.47620304302562, 9.613037109375014 40.98404494692811, 9.239501953125009 41.21585377825921))
    )',
    4326);
    
    -- Declare a linestring that crosses the multipolygon
    DECLARE @Linestring geography;
    SET @Linestring = geography::STLineFromText(
    'LINESTRING(8 36, 18 46)',
    4326);
    
    SELECT @Italy AS Shape, 'Italy' AS Name
    UNION ALL SELECT
    @Linestring AS Shape, 'Linestring' AS Name
    UNION ALL SELECT
    @Italy.STIntersection(@Linestring).STStartPoint() AS Shape, 'Point on coast' AS Name;
    


    Generates the following (the coastal point is shown by the purple circle, approximately located at Pescara)

    Linestring intersecting Italy
    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Proposed as answer by Tom Li - MSFT Monday, August 9, 2010 11:42 AM
    • Marked as answer by Tom Li - MSFT Thursday, August 12, 2010 3:02 AM
    Thursday, August 5, 2010 11:53 AM
    Answerer

All replies

  • If you define a linestring that crosses over the multipolygon then you can use STIntersection() to determine the section of that linestring that intersects Italy.

    To then find out the point on that linestring that lies on the coast, you simply need to choose either the start or end point of that section of intersecting linestring, using STStartPoint() / STEndPoint().

    Example code follows:

    -- Declare a multipolygon representing Italy
    DECLARE @Italy geography;
    SET @Italy = geography::STGeomFromText(
    'MULTIPOLYGON(
    ((18.764648437499993 40.39676430557204, 15.908203125 41.4262531950727, 14.150390625000014 42.43967417814943, 13.666992187499996 43.500752435690394, 12.612304687499991 44.13491344375071, 12.348632812499998 45.41387646082107, 13.139648437500009 45.78284835197675, 12.788085937499988 46.513515580597364, 10.19531249999999 46.57396679652779, 7.119140625000007 45.90529985724797, 7.3388671875 43.85037449930261, 8.92089843749999 44.386691502152054, 10.415039062500018 43.882057303905356, 10.766601562500009 42.924251753870685, 13.007812500000012 41.095912056395456, 15.732421875000007 39.79165483525342, 16.34765624999999 38.873928539236296, 15.512695312500012 38.15183740300677, 15.952148437500002 37.84015683604134, 17.226562499999996 39.04478604850142, 17.094726562500004 39.419220736559545, 16.523437499999986 39.82541310342478, 16.918945312500007 40.26276066437182, 18.369140625 39.75787999202175, 18.764648437499993 40.39676430557204)),
    ((14.798583984375007 38.0956597552956, 13.469238281249984 38.05241677186484, 13.04077148437499 38.22523523907684, 12.447509765624988 37.68816746840803, 13.941650390625014 37.05079312980657, 14.271240234374988 37.04202441635082, 14.886474609375004 36.63757008123925, 15.41381835937499 37.05079312980657, 15.128173828125014 37.49665234123336, 15.567626953125002 38.26837588020475, 14.798583984375007 38.0956597552956)),
    ((9.239501953125009 41.21585377825921, 8.47045898437501 40.801335759791996, 8.17382812500001 40.784700818417456, 8.492431640624995 40.14948820651523, 8.481445312500002 39.61838363831913, 8.338623046875016 38.92950241638659, 8.865966796875 38.87820499706148, 9.60205078124999 39.15136267949031, 9.865722656250016 40.47620304302562, 9.613037109375014 40.98404494692811, 9.239501953125009 41.21585377825921))
    )',
    4326);
    
    -- Declare a linestring that crosses the multipolygon
    DECLARE @Linestring geography;
    SET @Linestring = geography::STLineFromText(
    'LINESTRING(8 36, 18 46)',
    4326);
    
    SELECT @Italy AS Shape, 'Italy' AS Name
    UNION ALL SELECT
    @Linestring AS Shape, 'Linestring' AS Name
    UNION ALL SELECT
    @Italy.STIntersection(@Linestring).STStartPoint() AS Shape, 'Point on coast' AS Name;
    


    Generates the following (the coastal point is shown by the purple circle, approximately located at Pescara)

    Linestring intersecting Italy
    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Proposed as answer by Tom Li - MSFT Monday, August 9, 2010 11:42 AM
    • Marked as answer by Tom Li - MSFT Thursday, August 12, 2010 3:02 AM
    Thursday, August 5, 2010 11:53 AM
    Answerer
  • Thanks a lot tanoshimi,

    i studied the same solution, with the same methods used by you, but i allways got an error when i tried to  use the method STGeomFromText.

    I rescribed the function, starting from your example and it works........ :)

    surely a distraction error...

    thank a lot for the help anyway, and sorry for the bad english :)

    Massimo

     

     

    Thursday, August 5, 2010 4:35 PM