1 2 Previous Next 15 Replies Latest reply: Apr 11, 2013 6:03 AM by Simon Greener RSS

    How to create a polygon that covers a set of geometries?

    Luis A. Paolini
      Hi Folks,

      I have a spatial table with parcels data. Some are polygons and some are lines.
      What I need to do is to create a single polygon that covers all these parcels.
      I've tried some spatial functions but the result is not perfect.
      Could you guys give some tips about how to do that using Oracle Spatial Functions?

      Thanks,
      Luis
        • 1. Re: How to create a polygon that covers a set of geometries?
          Simon Greener
          Luis,

          How about giving us a bit more to work on!

          You say:
          I've tried some spatial functions but the result is not perfect.
          Can you show us what you tried?

          For example, did you try SDO_AGGR_CONVEXHULL? (Are you on 11gR2?)

          Or did you try using SDO_AGGR_UNION (if not licensed for Spatial did you try SDO_AGGR_SET_UNION?)?

          Did you try SDO_GEOM.SDO_CONVEX_HULL in combination with an aggregate union?

          How about some actual SDO_GEOMETRY data to work with?

          regards
          Simon
          • 2. Re: How to create a polygon that covers a set of geometries?
            Simon Greener
            Luis,

            To help you provide us with some more pertinent information I did this to demonstrate the sort of information we need to help you.
            with geoms as (
            select SDO_GEOMETRY(2002, 2154, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(-14877749.412, 11543213.651, -11853343.195, 15653304.152, -9526876.874, 14645168.746)) as geom from dual union all
            select SDO_GEOMETRY(2002, 2154, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(-12318636.459, 10302431.614, -9449327.996, 14645168.746)) as geom from dual union all
            select SDO_GEOMETRY(2002, 2154, NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(-12396185.336, 10379980.491, -15653238.186, 12163604.670)) as geom from dual union all
            select SDO_GEOMETRY(2003, 2154, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), SDO_ORDINATE_ARRAY(-12473734.214, 10845273.755, -10224816.770, 14722717.623, -6657568.411, 13637033.340, -8363643.713, 9759589.472, -10224816.770, 9449393.963, -12473734.214, 10845273.755)) as geom from dual 
            )
            select sdo_aggr_convexhull(sdoaggrtype(geom,0.005)) as coverGeom
            from geoms;
            COVERGEOM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
            ----------
            SDO_GEOMETRY(2003, 2154, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), SDO_ORDINATE_ARRAY(-1.022481677E7, 9449393.963, -8363643.713, 9759589.472, -6657568.411, 1.363703334E7, -1.1853343195E7, 1.5653304152E7, -1.5653238186E7, 1.216360467E7, -1.4877749412E7, 1.1543213651E7, -1.2318636459E7, 1.0302431614E7, -1.022481677E7, 9449393.963))
            regards
            Simon
            • 3. Re: How to create a polygon that covers a set of geometries?
              Luis A. Paolini
              Hi Simon,

              First of all, thanks for the response.
              Yes, I'm running Oracle 11R2 and I tried the SDO_AGGR_SET_UNION function.
              The resultant geometry is perfect, because it covers all the input geometries, respecting the boundaries of the outter parcels. But the problem is that I need a single polygon with GTYPE = 2003 and
              what I got is a heterogeneous collection (GTYPE = 2004).

              I tried your suggestion using SDO_AGGR_CONVEX_NULL and it did the trick, returning a 2003 polygon, but it does not fit perfectly in relation with the input geometries.
              I mean, the polygon covers some areas where does not exist any parcel.
              What I need is kind of a mix from these two functions.

              Regards,
              Luis
              • 4. Re: How to create a polygon that covers a set of geometries?
                Simon Greener
                Luis,

                You don't help me because you don't provide me with any data that shows the problem you are trying to solve.

                Using English (or any language) to describe spatial problems is, ahem, problematic! Best to give me real data or a picture.

                Here is my processing extended to show you how to use convex hull and concave hull processing. You probably don't need to
                "explode" the geometries into a point set but in doing so it may be a useful technique to help you do what you need to do or
                to show you problems (eg two geometries may intersect but at the intersection point there is not vertex - you might need to
                form a vertex to get what you want by intersecting geometries before processing via convex/cave hull.
                with geoms as (
                select SDO_GEOMETRY(2002,2154,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(-14877749.412, 11543213.651, -11853343.195, 15653304.152, -9526876.874, 14645168.746)) as geom from dual union all
                select SDO_GEOMETRY(2002,2154,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(-12318636.459, 10302431.614, -9526876.874, 14645168.746)) as geom from dual union all
                select SDO_GEOMETRY(2002,2154,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(-12318636.459, 10302431.614, -14877749.412, 11543213.651)) as geom from dual union all
                select SDO_GEOMETRY(2003,2154,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(-12318636.459, 10302431.614, -9526876.874, 14645168.746, -6657568.411, 13637033.340, -8363643.713, 9759589.472, -12318636.459, 10302431.614)) as geom from dual 
                )
                select 'component geometries' as ctype, g.geom
                  from geoms g
                union all
                select 'SDO_AGGR_CONVEXHULL' as ctype, 
                        sdo_aggr_convexhull(SDOAGGRTYPE(sdo_geometry(2001,2154,sdo_point_type(t.x,t.y,t.z),null,null),0.005))as coverGeom 
                  from geoms g,
                       table(sdo_util.getVertices(g.geom)) t
                union all
                select 'SDO_GEOM.SDO_CONCAVEHULL' as ctype, 
                       SDO_GEOM.SDO_CONCAVEHULL(
                         SDO_AGGR_UNION(SDOAGGRTYPE(sdo_geometry(2001,2154,sdo_point_type(t.x,t.y,t.z),null,null),0.005)),0.005) as coverGeom 
                  from geoms g,
                       table(sdo_util.getVertices(g.geom)) t;
                -- Results .... only last two...
                SDO_AGGR_CONVEXHULL  MDSYS.SDO_GEOMETRY(2003,2154,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(-8363643.713,9759589.472,-6657568.411,13637033.34,-11853343.195,15653304.152,-14877749.412,11543213.651,-12318636.459,10302431.614,-8363643.713,9759589.472))
                SDO_GEOM.SDO_CONCAVEHULL MDSYS.SDO_GEOMETRY(2003,2154,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(-12318636.459,10302431.614,-8363643.713,9759589.472,-6657568.411,13637033.34,-9526876.874,14645168.746,-11853343.195,15653304.152,-14877749.412,11543213.651,-12318636.459,10302431.614))
                If you use GeoRaptor within SQL Developer you can visualise this result easily. Sadly, the Oracle forum technology doesn't seem to allow me to add an image of the result.

                regards
                Simon
                • 5. Re: How to create a polygon that covers a set of geometries?
                  Luis A. Paolini
                  Simon,

                  You are right.
                  Please, take a look is this real data.


                  SELECT MDSYS.SDO_GEOMETRY(2002, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1), MDSYS.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 MDSYS.SDO_GEOMETRY(2002, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1), MDSYS.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 MDSYS.SDO_GEOMETRY(2002, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1), MDSYS.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 MDSYS.SDO_GEOMETRY(2002, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1), MDSYS.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 MDSYS.SDO_GEOMETRY(2002, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1), MDSYS.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 union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6876253016183, -18.498440566575, -49.6873986392309, -18.4980078759509, -49.6877532900365, -18.4978518399262, -49.6876253016183, -18.498440566575)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6879544382987, -18.4969712981419, -49.6877928411322, -18.4976827751589, -49.6873428417867, -18.4978852287196, -49.6870763001683, -18.4973665073198, -49.6879544382987, -18.4969712981419)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6872374525802, -18.4979317571816, -49.6862174403815, -18.4984001651865, -49.6859550139076, -18.4978762299323, -49.6869738154286, -18.4974172124668, -49.6872374525802, -18.4979317571816)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6862720259436, -18.4985201837893, -49.6873023121491, -18.498058352879, -49.6875838208957, -18.4986142263718, -49.6875756897042, -18.4986496823717, -49.6865739616478, -18.4991045741825, -49.6862720259436, -18.4985201837893)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6893182093476, -18.4938172505988, -49.6886391551652, -18.4940400798111, -49.6882446534647, -18.4941695464115, -49.6880385384283, -18.4937627522657, -49.6886427736849, -18.4935646179693, -49.689110191112, -18.4934113479352, -49.6893182093476, -18.4938172505988)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6808617305994, -18.4990648414148, -49.6809642867156, -18.4992648951266, -49.6801104949081, -18.4996559930113, -49.6795636928376, -18.4985603332641, -49.6804143740759, -18.498179065353, -49.6808601205885, -18.4990648301916, -49.6808617305994, -18.4990648414148)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6868469065491, -18.4991064683989, -49.6868449177069, -18.4991064545979, -49.6875456523548, -18.4987758005729, -49.6874170484046, -18.4994078062248, -49.68667387648, -18.4991799068788, -49.6866745346052, -18.4991805439822, -49.6868469065491, -18.4991064683989)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6899982604618, -18.4951447739072, -49.6889748775266, -18.4956111869419, -49.6887205196437, -18.4951090883726, -49.689739099718, -18.4946387577324, -49.6899982604618, -18.4951447739072)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6864244991732, -18.4935902614162, -49.6853483767335, -18.4939433359973, -49.6851418915515, -18.4935363557843, -49.686218104371, -18.4931835539605, -49.6864244991732, -18.4935902614162)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6866805363554, -18.4940945430026, -49.6856043164154, -18.4944476181999, -49.6853978293973, -18.4940407285693, -49.6864740457032, -18.4936878351145, -49.6866805363554, -18.4940945430026)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6813059078058, -18.4971868685029, -49.6815001289637, -18.4975744295522, -49.680475193908, -18.4980410546723, -49.6802303365364, -18.497550397849, -49.6813059078058, -18.4971868685029)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6869364809437, -18.4945987332952, -49.6858601628139, -18.4949518994662, -49.685671590428, -18.4945801950398, -49.6856536746495, -18.4945450100604, -49.6867299884565, -18.4941921159897, -49.6869314412802, -18.4945889392013, -49.6869364809437, -18.4945987332952)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6879244112595, -18.4938004555649, -49.6881323323148, -18.4942063587147, -49.6870587719874, -18.4945586474831, -49.6868526594373, -18.4941518522073, -49.6879244112595, -18.4938004555649)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6820963680704, -18.4987335917621, -49.6810588154021, -18.4992022101527, -49.6808158364697, -18.4987255731808, -49.6810273426361, -18.4986245768116, -49.6810273357175, -18.4986254803846, -49.6818515278862, -18.498252334544, -49.6820963680704, -18.4987335917621)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6863417238157, -18.4931430259681, -49.687413375361, -18.4927916312001, -49.6876212941188, -18.4931975348025, -49.6865477393734, -18.4935498210349, -49.6863417238157, -18.4931430259681)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6864626496752, -18.4961516653963, -49.6874859932097, -18.4956792119853, -49.6877278204533, -18.4961722792794, -49.6867162861321, -18.496635869927, -49.6864626496752, -18.4961516653963)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6795175839978, -18.4984718181384, -49.6792110807038, -18.4978688617286, -49.6801168070172, -18.4975713833655, -49.6803709076419, -18.498091743546, -49.6795175839978, -18.4984718181384)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6886184423562, -18.4951562732135, -49.6886304831996, -18.4951794894083, -49.6888767968539, -18.4956558694444, -49.6886259874211, -18.4957701571194, -49.6878512230647, -18.4961231644851, -49.6875997610842, -18.4956266871158, -49.6886184423562, -18.4951562732135)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6869860273353, -18.4946963972431, -49.6871947594552, -18.4951075479667, -49.6861205836375, -18.495465067705, -49.6859097086638, -18.4950495636606, -49.6869860273353, -18.4946963972431)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6876688449049, -18.493296088372, -49.6878768595152, -18.4937019924046, -49.6868032079632, -18.4940541888906, -49.6865971905728, -18.4936474844054, -49.6876688449049, -18.493296088372)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6888071649859, -18.4928084294883, -49.6886481311205, -18.4928606416395, -49.6877336146845, -18.4931607227647, -49.6875275966404, -18.4927539288271, -49.6885992437517, -18.4924025270278, -49.6886508506611, -18.4925032771748, -49.6888071649859, -18.4928084294883)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6883901817097, -18.4947097472198, -49.6873162959366, -18.4950670953237, -49.6871082237362, -18.494656310746, -49.6873054285624, -18.4945915332955, -49.6881822595009, -18.4943038442987, -49.6883332546529, -18.4945986588168, -49.6883901817097, -18.4947097472198)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6896202974326, -18.4944069689564, -49.6896973832113, -18.4945574138587, -49.6895880876548, -18.4946073505232, -49.6886773192076, -18.4950236677101, -49.6886323441836, -18.4949349816985, -49.6885437015602, -18.4947600584918, -49.6886339011709, -18.4947304120502, -49.6890211871649, -18.4946034248694, -49.6896202974326, -18.4944069689564)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6885021805217, -18.4946779928404, -49.6884624313917, -18.4945995539544, -49.6882941058105, -18.4942672094033, -49.6886382866216, -18.4941542014924, -49.6893680431117, -18.4939146448952, -49.689576062501, -18.4943205473299, -49.6887323388242, -18.494601424009, -49.6886346356437, -18.494633910112, -49.6885021805217, -18.4946779928404)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6888547163787, -18.4929069827993, -49.6890626387617, -18.4933128850334, -49.6886436428834, -18.493450405923, -49.6879890863572, -18.4936650892201, -49.6877830671691, -18.4932582955065, -49.688647261267, -18.4929749440507, -49.6888547163787, -18.4929069827993)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6837720399367, -18.4963487576802, -49.6860498628528, -18.4955856622418, -49.686312309348, -18.4961066154899, -49.6841429581001, -18.4970884216489, -49.6837720399367, -18.4963487576802)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6872336695187, -18.4951841740232, -49.6874394162598, -18.4955895206257, -49.6864201010696, -18.4960554053215, -49.6861598665847, -18.4955424194446, -49.6872336695187, -18.4951841740232)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6791012123332, -18.497906408428, -49.6794148900972, -18.4985123970104, -49.6789686315609, -18.4987196452528, -49.6784993306633, -18.4981031717015, -49.6791012123332, -18.497906408428)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6797875904015, -18.4997960607587, -49.6793815913577, -18.4992608144483, -49.6790277578918, -18.4988026489182, -49.6794580283655, -18.4986054095337, -49.6800187076117, -18.4996945699201, -49.6797875904015, -18.4997960607587)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6815499224909, -18.4976642349862, -49.681794066334, -18.4981496443384, -49.6807665270777, -18.4986220359001, -49.6805208012499, -18.4981332709395, -49.6810328863445, -18.4979005436941, -49.6810328884199, -18.4979002726222, -49.6815499224909, -18.4976642349862)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6843314699552, -18.4977159425374, -49.6835628821572, -18.4980640946734, -49.6833169188677, -18.4981754266482, -49.6830681929106, -18.4976941437982, -49.6840872681918, -18.4972372224791, -49.6843314699552, -18.4977159425374)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6847881519832, -18.4986233718125, -49.6845151661507, -18.4985401483767, -49.6847004174138, -18.498453875025, -49.6847881519832, -18.4986233718125)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6846417882246, -18.4983302134345, -49.6843128270063, -18.4984828075223, -49.6835467207654, -18.4982455199209, -49.6835370612174, -18.4982206031191, -49.6843884161104, -18.4978367009161, -49.6846417882246, -18.4983302134345)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6866069905355, -18.4966855336504, -49.6855881774671, -18.4971465372487, -49.6853482752211, -18.496662517129, -49.6863596782199, -18.4962041741148, -49.6866069905355, -18.4966855336504)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6840389574875, -18.4971391146284, -49.683020427443, -18.4975991118256, -49.682779783431, -18.4971139089647, -49.6837950198561, -18.4966508176963, -49.6840389574875, -18.4971391146284)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6879048506934, -18.4962322421815, -49.6881425547653, -18.4961227442612, -49.6880550598397, -18.4965184670595, -49.6879048506934, -18.4962322421815)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6884343234729, -18.4947958973602, -49.6885749605993, -18.4950704891315, -49.6875543205436, -18.4955370036615, -49.6873578178502, -18.4951489804877, -49.6884343234729, -18.4947958973602)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6827317472772, -18.4970171581659, -49.6825922832246, -18.4967512454672, -49.6836594220886, -18.496386920557, -49.6837500719106, -18.4965588778021, -49.6827317472772, -18.4970171581659)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6819497022146, -18.4982081987051, -49.6829728451096, -18.4977402878911, -49.6832249595502, -18.4982243052964, -49.6821956016788, -18.4986872041324, -49.6819497022146, -18.4982081987051)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.681648158746, -18.4976243467395, -49.6826796528952, -18.4971538750089, -49.6829229090505, -18.4976442468068, -49.6818983135542, -18.4981040146677, -49.681648158746, -18.4976243467395)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6826306137678, -18.4970644365433, -49.6816036134059, -18.4975294272471, -49.6814103798675, -18.4971489214212, -49.6824916281706, -18.4967855149536, -49.6826306137678, -18.4970644365433)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.685216054355, -18.4967128340375, -49.6854542642342, -18.4972075956607, -49.6844347235561, -18.4976638887881, -49.6841888839594, -18.4971887719509, -49.685216054355, -18.4967128340375)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6855121794867, -18.4973255592425, -49.6857561563176, -18.4978218967643, -49.684745506687, -18.4982793374741, -49.6844900376897, -18.4977875273864, -49.6855121794867, -18.4973255592425)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6858169644228, -18.4979455731813, -49.6860712954278, -18.4984629463148, -49.6853586645402, -18.4987885422339, -49.6849401917988, -18.4986693384513, -49.6848050816214, -18.4984031863334, -49.6858169644228, -18.4979455731813)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6855551680643, -18.498853612627, -49.6861457540963, -18.4985850908994, -49.6863933717884, -18.4990763927271, -49.6863595721224, -18.4990871822987, -49.6855551680643, -18.498853612627)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.6866682617837, -18.4967981888318, -49.6869115416115, -18.4972869297613, -49.6858888412539, -18.4977480885526, -49.6856398677264, -18.4972611141576, -49.6866682617837, -18.4967981888318)) as geom from dual union all
                  SELECT MDSYS.SDO_GEOMETRY(2003, 8292, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(-49.687787931061, -18.4962881791314, -49.6880131409429, -18.4967249255285, -49.6879943140866, -18.4968092838094, -49.6870171815487, -18.4972447405915, -49.6867708305951, -18.4967488398498, -49.687787931061, -18.4962881791314)) as geom from dual

                  Regards,
                  Luis

                  Edited by: user12108970 on Jun 18, 2012 4:34 AM
                  • 6. Re: How to create a polygon that covers a set of geometries?
                    Simon Greener
                    Luis,

                    Ahh, most useful.

                    Can you email me an image of what you want the result to look like? A rough sketched polygon over the geometries will do. simon at spatialdbadvisor dot com

                    Also, can the roads around the outside of the land parcels be clipped or shortened so that you don't get the side roads being taken into the solution?

                    Simon

                    Edited by: Simon Greener on Jun 18, 2012 10:00 PM
                    • 7. Re: How to create a polygon that covers a set of geometries?
                      Luis A. Paolini
                      Simon,

                      I sent you a picture via e-mail.
                      By the way, the lines around the outside parcels are not roads. They are also parcels, but where are missing segments.
                      I know that this is odd, but I have the same situation in different cities.

                      Regards,
                      Luis
                      • 8. Re: How to create a polygon that covers a set of geometries?
                        Paul Dziemiela
                        Hey Luis and Simon,

                        I thought this was an interesting post. Did you all come to any conclusions? I was looking at Luis' geometry collection - it is an oddball thing - but on the other hand a valid geometry is a valid geometry. I believe what Luis is asking for a is a "very very tight" concave hull which I think is not an outrageous request.

                        As Simon says its tough to illustrate the issue without using graphics. You would never believe that Oracle is a multibillion dollar company from this lame forum software. Anyhow, we do the best we can. So here is Luis's collection of polygons and lines:
                        http://www.dziemiela.com/luis_1.PNG
                        As I mentioned it's a valid geometry collection.

                        We can slap a convex hull around it nicely enough
                        http://www.dziemiela.com/luis_2.PNG
                        but that is not what Luis wants.

                        So lets try with a concave hull.
                        http://www.dziemiela.com/luis_3.PNG
                        That is just terrible. Its like the process is totally confused about what is the outer boundary of the collection.
                        We can also try calling concave hull boundary and the results are worse
                        http://www.dziemiela.com/luis_4.PNG
                        playing about with the length parameter can change the shape but does not avoid the underlying issue of an incorrect boundary selection
                        http://www.dziemiela.com/luis_5.PNG (with a length of 200).

                        I believe this is all about how the triangulation is generated. Here are the results via SDO_GEOM.SDO_TRIANGULATE
                        http://www.dziemiela.com/luis_6.PNG
                        Yeah its a mess but I think you can see in those areas where the outer lines are disrespected in the hull that the triangles also ignore them.

                        One may state that with Oracle Spatial 11.2.0.4 you are probably best to avoid using SDO_GEOM.CONCAVE_HULL with geometry collections.

                        I thought perhaps Luis could toss out all the interior polygons and deal only with the exterior multstrings.
                        http://www.dziemiela.com/luis_7.PNG
                        but running concavehull just produced silliness
                        http://www.dziemiela.com/luis_8.PNG
                        concavehull_boundary was no better.

                        So I might also state that SDO_GEOM.CONCAVE_HULL doesn't work all that great with multistrings either.

                        So I am not quite sure what Luis can do. He should really submit this as an SR.

                        What about doing the opposite? Toss out the multistrings leaving the interior polygons and then apply a small buffer to cover the lines (well, kinda does what Luis wants but you lose those extensions)?
                        http://www.dziemiela.com/luis_9.PNG
                        Well, so I was wrong. The "outer edges" problem occurs with just polygons as well. SDO_GEOM.CONCAVE_HULL does not seem to be cutting the mustard.

                        Cheers,
                        Paul
                        • 9. Re: How to create a polygon that covers a set of geometries?
                          Simon Greener
                          Paul and Luis,

                          Sorry for having been quiet. I have been working on this on and off as it has a few issues that, from what I can see (or the limit of my Oracle skills) it can't be done easily using pure Oracle functionality.

                          I too splayed around with the alpha shape, concave hull functionality and found that I couldn't easily get a polygon around the outside.

                          In the end I decided that the inner polygons are not needed so I stopped using them.

                          The best I can do at the moment is:

                          1. Take the original linestrings and DENSIFY them. (I use my free plsql GEOM.DENSIFY function but I have also tried it using my SC4O.ST_Densify function based on JTS.)
                          2. I took the linestrings and turned them into a pointset via SDO_UTIL.GETVERTICES and SDO_AGGR_UNION.
                          3. I then pumped in various radii/distances.

                          A simplified version of the SQL is shown (I removed the generation of various distances/radii).
                          -- Concave Boundary + densified points + multilinestring of boundary
                          --
                          With outerLines as (
                                    select MDSYS.SDO_GEOMETRY(2002,29182,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(638516.880000357,7954033.95004268,638556.170000352,7954046.26004268,638478.800000361,7954424.30004268,638442.60000036,7954441.05004268,638231.54000035,7954538.73004268)) as geom from dual
                          union all select MDSYS.SDO_GEOMETRY(2002,29182,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(638468.80000036,7954896.58004267,638442.600000352,7954843.36004267,638314.260000356,7954582.66004268,638287.670000349,7954528.63004267,638276.260000353,7954534.17004268,638235.330000348,7954554.09004267)) as geom from dual
                          union all select MDSYS.SDO_GEOMETRY(2002,29182,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(638475.760000356,7954881.40004267,638453.500000356,7954836.67004267,638828.780000365,7954704.55004267,638769.100000365,7954582.66004268,638741.720000357,7954526.76004268,638719.240000363,7954480.84004268,638971.430000363,7954390.81004267,638990.210000362,7954450.62004267)) as geom from dual
                          union all select MDSYS.SDO_GEOMETRY(2002,29182,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(639430.320000375,7953973.29004269,639380.500000375,7953996.59004269,639526.830000371,7954192.63004268,639242.810000369,7954293.49004267,639242.600000371,7954293.99004268,638982.830000363,7954386.74004268,639001.620000363,7954445.53004268)) as geom from dual
                          union all select MDSYS.SDO_GEOMETRY(2002,29182,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(638519.550000353,7954026.82004269,638698.900000361,7954082.66004269,638991.520000368,7954171.75004268,638996.74000037,7954168.49004268,639003.120000365,7954163.42004268,639017.590000364,7954147.08004268,639028.140000369,7954134.75004268,639034.330000367,7954127.23004269,639042.730000366,7954120.35004269,639051.740000366,7954114.53004268,639064.050000371,7954108.85004268,639076.59000037,7954107.04004268,639088.230000369,7954106.72004268,639093.850000367,7954106.11004268,639102.770000372,7954101.85004268,639139.160000373,7954082.66004269,639184.340000364,7954056.68004269,639187.9684555,7954055.00578486,639191.727038192,7954053.65689096,639195.590732453,7954052.64776993,639199.528889825,7954051.98642649,639203.510271183,7954051.67810673,639207.503294547,7954051.72525637,639211.47628555,7954052.12750139,639215.397728773,7954052.88165101,639219.236517671,7954053.98172301,639222.960000375,7954055.42004269,639420.74000037,7953960.97004269)) as geom from dual
                          )
                          select t.geom, 
                                 t.dpoints,
                                 (select sdo_aggr_union(sdoaggrtype(c.geom,0.005)) as geom from outerLines c) as lines
                            from (select SDO_GEOM.SDO_CONCAVEHULL_BOUNDARY(f.geom,0.005,10.0) as geom,
                                         f.geom as dpoints
                                    from (select sdo_aggr_union(sdoaggrtype(sdo_geometry(2001,29182,sdo_point_type(p.x,p.y,p.z),null,null),0.005)) as geom
                                            from outerLines ol,
                                                 table(sdo_util.getVertices(SC4O.ST_Densify(ol.geom,2,5.0))) p
                                          ) f
                                 ) t
                           where t.geom is not null;
                          I then screen grabbed the result using GeoRaptor and cut in the triangular issues in the corners.

                          The image can be seen at:

                          http://www.spatialdbadvisor.com/files/OuterPolygonConcaveHull_BoundaryPointsAfterDensify.png

                          I am looking at my SC4O.ST_Snap functions to snap the triangles to the points but it all takes a lot of unpaid-time.

                          Also, I have managed to create some SQL that closes the outer linear boundary and am looking to form a polygon via my SC4O.ST_PolygonBuilder code but am having issues with debugging in an upgraded version of JDeveloper.....

                          Again, when I get results for the latter 2 I will post.

                          regards
                          Simon
                          • 10. Re: How to create a polygon that covers a set of geometries?
                            Simon Greener
                            Luis etc,

                            Using the ST_PolygonBuilder (JTS) that implements my SC4O Java-in-the-database code, and some tricky coding, I have managed to product a polygon that correctly covers the data.

                            Note that I only use the outer linestrings that Luis provides.
                            -- First create a table holding only the outer lines.
                            --
                            create table 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;
                            
                            -- Firstly, fill in the gaps between all the outer lines by generating small 2 point linestrings....
                            -- This is what the CTE, connected_lines, produces
                            --
                            with 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
                            )
                            /* Now ask SC4O's ST_PolygonBuilder to try and create a polygon from the set of lines */
                            select sc4o.ST_PolygonBuilder(CAST(COLLECT(f.geom) as mdsys.sdo_geometry_array),8) as polygon
                              from ( /* Now, the WITH connected_lines produces 2 copies of each linestring with the coordinates reversed.
                                            These have to be removed/de-duped before being passed into the ST_PolygonBuilder */
                                    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 and pass into ST_PolygonBuilder the original outer lines */
                                select m.geom as geom
                                  from connected_lines m
                                 where sdo_util.GetNumVertices(m.geom) > 2
                                ) f;
                            -- Result
                            --
                            POLYGON
                            -----------------------
                            MDSYS.SDO_GEOMETRY(2003,8292,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(-49.6906307026931,-18.4950115281619,-49.6886251490431,-18.4958803030445,-49.6882811644211,-18.4960292763006,-49.6875224063873,-18.4994400734344,-49.6878936628642,-18.4995538840232,-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,-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,-49.6834396716227,-18.4957578264012,-49.6836133980005,-18.4962994912622,-49.6860079716291,-18.4955025995491,-49.6857982402423,-18.4950861997106,-49.6855427926656,-18.4945793006294,-49.6849860033691,-18.4934740072225,-49.6885491287687,-18.4923048595269,-49.6883413960309,-18.4918992292229,-49.6884083531774,-18.4917625230274,-49.6886528151554,-18.4922451253898,-49.6898503241639,-18.4946091658756,-49.690098431643,-18.4950991112698,-49.6902068702432,-18.4950498009285,-49.6905958643821,-18.4948724904445,-49.6906307026931,-18.4950115281619))
                            The result is a nice clean polygon.

                            http://www.spatialdbadvisor.com/files/PolygonCoveringGeometrySet.png

                            This, plus my last post is the best I can do. It has taken me many, many hours to implement. Please award points as this is a correct result. Also, consider making a PayPal donation on my website for the hours I have spent.

                            NOTE: The Spatial Companion 4 Oracle (SC4O) package is available as a FREE download from my website. Again, donations accepted if the code does what you want.

                            regards
                            Simon

                            Edited by: Simon Greener on Apr 11, 2013 9:53 AM Changed where a.x b.x and a.y b.y etc to where a.x != b.x and a.y != b.y
                            • 11. Re: How to create a polygon that covers a set of geometries?
                              Luis A. Paolini
                              Hi Simon,

                              Sorry for the late response, but first of all I'd like to thank you to help me on this.
                              The result, as you said, is a nice clean polygon, as I expected.
                              Certainly I will use this solution and your Oracle Companion package.

                              I'll send you a pvt email.

                              Thanks.
                              Luis
                              • 12. Re: How to create a polygon that covers a set of geometries?
                                Luis A. Paolini
                                Simon,

                                I was revisiting this post, since it can be a solution for a new problem, and I noticed that in one line of code, there are two operators missing.

                                In the line: where a.x b.x and a.y b.y

                                Do you remember this case?

                                Luis
                                • 13. Re: How to create a polygon that covers a set of geometries?
                                  Simon Greener
                                  Luis A. Paolini wrote:
                                  I was revisiting this post, since it can be a solution for a new problem, and I noticed that in one line of code, there are two operators missing.

                                  In the line: where a.x b.x and a.y b.y

                                  Do you remember this case?
                                  Luis,

                                  Yes, I'm sorry, I failed to check the SQL once I entered it into the code .. /code elements.

                                  The problem is that the != operator in its traditional form of LEFTANGLEBRACKET .... RIGHTANGLEBRACKET is interpreted as a null HTML tag and is removed.

                                  So, to fix, replace by:
                                  where a.x != b.x and a.y != b.y
                                  Or, of course its normal representation. I don't know how to "escape" this operator but here goes:
                                  where a.x <\> b.x and a.y <\> b.y
                                  You get the idea (remove the \)

                                  PS. I have edited the original SQL to fix the problem.

                                  regards
                                  Simon
                                  • 14. Re: How to create a polygon that covers a set of geometries?
                                    Stefan Jager
                                    Hi Simon,

                                    try &amp;lt; and &amp;gt; next time:
                                    1 &lt;&gt; 2
                                    Cheers,
                                    Stefan
                                    1 2 Previous Next