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.