Top 5 Recent Articles

ARTICLES CATEGORIES

Line Merging or Collecting lines together: ST_LineMerger

One activity that is very common when working with linear data is to be able to take a bunch of linestrings and collect them together into a minimal set of maximum length linestrings.

The Java Topology Suite (JTS) has a LineMerger class that does this very nicely.

I have integrated this into my Spatial Companion For Oracle (SC4O) project.

   /**
    * ST_LineMerger
    * Takes set of linestring geometries and constructs a collection of linear components
    * that form maximal-length linestrings. The linear components are returned as a MultiLineString.
    * @param p_resultSet  : RefCur_T : Ref Cursor of Linestring Geometries
    * @param p_precision  : int   : Number of decimal places of precision when comparing ordinates.
    * @return STRUCT      : Collection of linear sdo_geometries as MultiLineString.
    * @throws SQLException
    * @history Simon Greener, January 2012, Original Coding
    * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
    *               http://creativecommons.org/licenses/by-sa/2.5/au/
    */
   FUNCTION ST_LineMerger(p_resultSet IN &&defaultSchema..SC4O.refcur_t,
                          p_precision IN NUMBER)
     RETURN mdsys.sdo_geometry
            Deterministic;
 .
   /**
    * ST_LineMerger
    * Takes set of linestring geometries and constructs a collection of linear components
    * that form maximal-length linestrings. The linear components are returned as a MultiLineString.
    * @param p_resultSet  : T_GeometrySet : Table of Linestring Geometries
    * @param p_precision  : int           : Number of decimal places of precision when comparing ordinates.
    * @return STRUCT      : Collection of linear sdo_geometries as MultiLineString.
    * @throws SQLException
    * @history Simon Greener, January 2012, Original Coding
    * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
    *               http://creativecommons.org/licenses/by-sa/2.5/au/
    */
   FUNCTION ST_LineMerger(p_geomset   IN &&defaultSchema..t_geometrySet,
                          p_precision IN NUMBER)
     RETURN mdsys.sdo_geometry
            Deterministic;

Here is an example.

 -- Show the lines
 --
 WITH LINES AS (
 SELECT sdo_geometry('LINESTRING (220 160, 240 150, 270 150, 290 170)',NULL) AS geom FROM dual UNION ALL
 SELECT sdo_geometry('LINESTRING (60 210, 30 190, 30 160)',NULL) AS geom FROM dual UNION ALL
 SELECT sdo_geometry('LINESTRING (70 430, 100 430, 120 420, 140 400)',NULL) AS geom FROM dual UNION ALL
 SELECT sdo_geometry('LINESTRING (160 310, 160 280, 160 250, 170 230)',NULL) AS geom FROM dual UNION ALL
 SELECT sdo_geometry('LINESTRING (170 230, 180 210, 200 180, 220 160)',NULL) AS geom FROM dual UNION ALL
 SELECT sdo_geometry('LINESTRING (30 160, 40 150, 70 150)',NULL) AS geom FROM dual UNION ALL
 SELECT sdo_geometry('LINESTRING (160 310, 200 330, 220 340, 240 360)',NULL) AS geom FROM dual UNION ALL
 SELECT sdo_geometry('LINESTRING (140 400, 150 370, 160 340, 160 310)',NULL) AS geom FROM dual UNION ALL
 SELECT sdo_geometry('LINESTRING (160 310, 130 300, 100 290, 70 270)',NULL) AS geom FROM dual UNION ALL
 SELECT sdo_geometry('LINESTRING (240 360, 260 390, 260 410, 250 430)',NULL) AS geom FROM dual UNION ALL
 SELECT sdo_geometry('LINESTRING (70 150, 100 180, 100 200)',NULL) AS geom FROM dual UNION ALL
 SELECT sdo_geometry('LINESTRING (70 270, 60 260, 50 240, 50 220, 60 210)',NULL) AS geom FROM dual UNION ALL
 SELECT sdo_geometry('LINESTRING (100 200, 90 210, 60 210)',NULL) AS geom FROM dual
 )
 SELECT rownum AS id, geom
   FROM LINES;
 -- Rsults
 ID GEOM
 -- --------------------------------------------------------------------------------------------------------------------------------
 1  MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(220,160,240,150,270,150,290,170))
 2  MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(60,210,30,190,30,160))
 3  MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(70,430,100,430,120,420,140,400))
 4  MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(160,310,160,280,160,250,170,230))
 5  MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(170,230,180,210,200,180,220,160))
 6  MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(30,160,40,150,70,150))
 7  MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(160,310,200,330,220,340,240,360))
 8  MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(140,400,150,370,160,340,160,310))
 9  MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(160,310,130,300,100,290,70,270))
 10 MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(240,360,260,390,260,410,250,430))
 11 MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(70,150,100,180,100,200))
 12 MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(70,270,60,260,50,240,50,220,60,210))
 13 MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(100,200,90,210,60,210))
 .
  13 ROWS selected

This looks like this….

 -- Now do the Line Merging.
 --
 WITH stuff AS (
 SELECT CAST(
 multiset(
 SELECT sdo_geometry('LINESTRING (220 160, 240 150, 270 150, 290 170)',NULL) AS geom FROM dual UNION ALL
 SELECT sdo_geometry('LINESTRING (60 210, 30 190, 30 160)',NULL) AS geom FROM dual UNION ALL
 SELECT sdo_geometry('LINESTRING (70 430, 100 430, 120 420, 140 400)',NULL) AS geom FROM dual UNION ALL
 SELECT sdo_geometry('LINESTRING (160 310, 160 280, 160 250, 170 230)',NULL) AS geom FROM dual UNION ALL
 SELECT sdo_geometry('LINESTRING (170 230, 180 210, 200 180, 220 160)',NULL) AS geom FROM dual UNION ALL
 SELECT sdo_geometry('LINESTRING (30 160, 40 150, 70 150)',NULL) AS geom FROM dual UNION ALL
 SELECT sdo_geometry('LINESTRING (160 310, 200 330, 220 340, 240 360)',NULL) AS geom FROM dual UNION ALL
 SELECT sdo_geometry('LINESTRING (140 400, 150 370, 160 340, 160 310)',NULL) AS geom FROM dual UNION ALL
 SELECT sdo_geometry('LINESTRING (160 310, 130 300, 100 290, 70 270)',NULL) AS geom FROM dual UNION ALL
 SELECT sdo_geometry('LINESTRING (240 360, 260 390, 260 410, 250 430)',NULL) AS geom FROM dual UNION ALL
 SELECT sdo_geometry('LINESTRING (70 150, 100 180, 100 200)',NULL) AS geom FROM dual UNION ALL
 SELECT sdo_geometry('LINESTRING (70 270, 60 260, 50 240, 50 220, 60 210)',NULL) AS geom FROM dual UNION ALL
 SELECT sdo_geometry('LINESTRING (100 200, 90 210, 60 210)',NULL) AS geom FROM dual
 ) AS t_geometrySet) AS mLine
 FROM dual
 )
 SELECT rownum AS Id, t.*
 FROM stuff s,
      TABLE(geom.ExplodeGeometry(geom.ExtractLine(SC4O.st_linemerger(s.mline,3)))) t;
 -- Results
 ID                     GEOMETRY
 -- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 1  MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(60,210,30,190,30,160,40,150,70,150,100,180,100,200,90,210,60,210))
 2  MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(160,310,130,300,100,290,70,270,60,260,50,240,50,220,60,210))
 3  MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(70,430,100,430,120,420,140,400,150,370,160,340,160,310))
 4  MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(160,310,200,330,220,340,240,360,260,390,260,410,250,430))
 5  MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(160,310,160,280,160,250,170,230,180,210,200,180,220,160,240,150,270,150,290,170))

This looks like this….

I hope this is useful to someone.