This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Apr 11, 2013 4:03 AM by Simon Greener RSS

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

Luis A. Paolini Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    Hi Simon,

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points