Top 5 Recent Articles

ARTICLES CATEGORIES

Building Polygons from Incomplete Linestrings using ST_PolygonBuilder

The ST_PolygonBuilder function in the Spatial Companion for Oracle (SC4O) package provides a mechanism via which linestring can be compiled into polygons.

I have given some simple examples in a post I made a few years ago.

I will now give a much more complex example that I wrote in response to a request on the Oracle Technology Network Spatial forum. In this example, the set of linestrings do not of themselves close forming a proper polygon.

The first thing that needs to be done is to “close” the linestrings by creating small additional linestrings that bridge the gaps. Then the who set of linestrings can be used to construct the polygon.

 WITH outerLines AS (
   SELECT SDO_GEOMETRY(2002, 8292, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(-49.6878936628642, -18.4995538840232, -49.6875224063873, -18.4994400734344, -49.6882811644211, -18.4960292763006, -49.6886251490431, -18.4958803030445, -49.6906307026931, -18.4950115281619)) AS geom FROM dual UNION ALL
   SELECT SDO_GEOMETRY(2002, 8292, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(-49.6884083531774, -18.4917625230274, -49.6886528151554, -18.4922451253898, -49.6898503241639, -18.4946091658756, -49.690098431643, -18.4950991112698, -49.6902068702432, -18.4950498009285, -49.6905958643821, -18.4948724904445)) AS geom FROM dual UNION ALL
   SELECT SDO_GEOMETRY(2002, 8292, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(-49.6883413960309, -18.4918992292229, -49.6885491287687, -18.4923048595269, -49.6849860033691, -18.4934740072225, -49.6855427926656, -18.4945793006294, -49.6857982402423, -18.4950861997106, -49.6860079716291, -18.4955025995491, -49.6836133980005, -18.4962994912622, -49.6834396716227, -18.4957578264012)) AS geom FROM dual UNION ALL
   SELECT SDO_GEOMETRY(2002, 8292, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(-49.6792385551751, -18.5000418095053, -49.6797119992317, -18.4998345697814, -49.6783397446724, -18.4980535328215, -49.6810365599698, -18.4971609552001, -49.6810385833744, -18.4971564511947, -49.6835051531636, -18.4963355157698, -49.6833312619652, -18.4958030666281)) AS geom FROM dual UNION ALL
   SELECT SDO_GEOMETRY(2002, 8292, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(-49.6878678853177, -18.4996181336176, -49.6861731626715, -18.4991017919552, -49.6834080082784, -18.4982775371379, -49.6833583465082, -18.4983066497055, -49.6832975737928, -18.4983520405102, -49.6831594052426, -18.4984987309426, -49.6830586383606, -18.4986094463067, -49.6829994956301, -18.4986769870823, -49.6829194669485, -18.4987385992949, -49.6828337343539, -18.4987905933782, -49.6827167582476, -18.4988411048671, -49.6825978712605, -18.4988566328165, -49.6824876108137, -18.4988587567866, -49.6824343436196, -18.4988638980162, -49.6823495710101, -18.498901802054, -49.6820036074112, -18.4990727977958, -49.6815739264209, -18.4993045655044, -49.6815394467332, -18.4993194542279, -49.6815037571481, -18.4993313944568, -49.681467095582, -18.4993402576461, -49.6814297528519, -18.4993459734892, -49.6813920251781, -18.4993484966453, -49.6813542118344, -18.4993478070996, -49.6813166127745, -18.4993439103221, -49.6812795262518, -18.4993368372241, -49.6812432464542, -18.4993266439127, -49.6812080820875, -18.4993134018902, -49.6793284309429, -18.5001537628556)) AS geom FROM dual
 ), connected_lines AS (
 SELECT ROW_NUMBER() OVER (ORDER BY 1) AS rid,
         f.geom
   FROM (SELECT sdo_geometry(2002,8292,NULL,sdo_elem_info_array(1,2,1),sdo_ordinate_array(sx,sy,ex,ey)) AS geom
           FROM (SELECT DISTINCT sx,sy,ex,ey,dist,
                        MIN(dist) OVER (partition BY sx,sy) AS mdist
                   FROM (SELECT a.x AS sx,a.y AS sy,b.x AS ex,b.y AS ey,
                                sdo_geom.sdo_distance(sdo_geometry(2001,8292,sdo_point_type(a.x,a.y,NULL),NULL,NULL),
                                                      sdo_geometry(2001,8292,sdo_point_type(b.x,b.y,NULL),NULL,NULL),
                                                      0.005) AS dist
                           FROM (SELECT p.x AS x, p.y AS y
                                   FROM outerLines g,
                                        TABLE(geom.getVector(g.geom)) v,
                                        TABLE(geom.getPointSet(v.AsSdoGeometry(8292))) p
                                 GROUP BY p.x, p.y
                                 HAVING COUNT(*) = 1
                                ) a,
                                (SELECT p.x AS x, p.y AS y
                                   FROM outerLines g,
                                        TABLE(geom.getVector(g.geom)) v,
                                        TABLE(geom.getPointSet(v.AsSdoGeometry(8292))) p
                                 GROUP BY p.x, p.y
                                 HAVING COUNT(*) = 1
                                ) b
                           WHERE a.x != b.x AND a.y != b.y
                         )
                 )
                 WHERE dist = mdist
         UNION ALL
         SELECT g.geom
           FROM outerLines g
       ) f
 )
 SELECT sc4o.ST_PolygonBuilder(CAST(COLLECT(f.geom) AS mdsys.sdo_geometry_array),8) AS polygon
   FROM (
         SELECT sdo_geometry(2002,8292,NULL,sdo_elem_info_array(1,2,1),sdo_ordinate_array(SUM(x1),SUM(y1),SUM(x2),SUM(y2))) AS geom
           FROM (SELECT rid,
                        CASE WHEN rid=NVL((lag(rid,1) OVER (partition BY rid ORDER BY x)),rid)
                             THEN CASE WHEN vertexId = 1 THEN x ELSE NULL END
                         END AS x1,
                        CASE WHEN rid=NVL((lag(rid,1) OVER (partition BY rid ORDER BY x)),rid)
                             THEN CASE WHEN vertexId = 1 THEN y ELSE NULL END
                         END AS y1,
                        CASE WHEN rid=NVL((lag(rid,1) OVER (partition BY rid ORDER BY x)),rid)
                             THEN CASE WHEN vertexId = 2 THEN x ELSE NULL END
                         END AS x2,
                        CASE WHEN rid=NVL((lag(rid,1) OVER (partition BY rid ORDER BY x)),rid)
                             THEN CASE WHEN vertexId = 2 THEN y ELSE NULL END
                         END AS y2
                 FROM   (SELECT rid,
                                ROW_NUMBER() OVER (partition BY rid ORDER BY x) AS vertexId,
                                x, y
                           FROM (SELECT MIN(m.rid) rid,
                                        t.x, t.y
                                   FROM connected_lines m,
                                        TABLE(sdo_util.getVertices(m.geom)) t
                                  WHERE sdo_util.GetNumVertices(m.geom) = 2
                                  GROUP BY t.x,t.y
                                  ORDER BY t.x )
                        )
                   )
                 GROUP BY rid
     UNION ALL
     SELECT m.geom AS geom
       FROM connected_lines m
      WHERE sdo_util.GetNumVertices(m.geom) > 2
     ) f;

The linestrings (yellow) and the resulting polygon (green with blue boundary) can be seen in the following image.

I hope this helps someone out there.