1

I have a data set with mixed SDO_GTYPE 2007 and SDO_GTYPE 2003, however for the task I am working on only SDO_GTYPE 2003 is allowed for practical reasons.

I was able to extract the polygons in a querie from the multipolygons, however I don't know how to save the polygons in a new table and I couldn't find any helpful information on how to do it.

My code so far:

SELECT SDO_UTIL.EXTRACT_ALL(t1.GEOMETRY, 0)
FROM table t1
WHERE t1.GEOMETRY.SDO_GTYPE = 2007;

CREATE TABLE "TEST"."POLYGON" 
   (    "GEOINVFL_N" VARCHAR2(16 CHAR), 
    "GEOMETRY" "MDSYS"."SDO_GEOMETRY" 
   )

I am working in Oracle 12.1

  • Are your gtype=2007 multipolygons real, multipart multipolygons? And is your aim to split the multipolygon into many singlepart polygons with the same attributes as the original multipolygon? – user30184 Feb 11 at 7:45
  • Yes they are real multipart polygons and yes, I want to keep the original attributes. Thanks for your interest! – blabbath Feb 11 at 7:47
  • This is not really a spatial question but rather an Oracle SQL question regarding array manipulation. Your use of SDO_UTIL.EXTRACT_ALL will convert your multipolygon into an array of polygon geometries - all good. You have the spatial part of the matter down pat. Well then you need to loop through your array one-by-one and insert the polygons one-by-one into new rows in your table. There are a variety of ways to do this as as the solution needs to know your schema - there is no simple one size fits all procedure. I usually use an Oracle pipelined function for this purpose. Out of space – pauldzy Feb 13 at 0:58
  • Pipelined functions in the old days needed a custom type to model the output but now 18c has the nifty ROW POLYMORPHIC clause to perhaps avoid all that (not going to help you I guess). Perhaps a lot easier is just an anonymous block that reads your table rows one-by-one doing a single insert for each row but "stuttering" when necessary to generate a unique row for each component polygon. You would probably want to adjust your primary key during this process, no? I think I am driving at this not being a quick solution, it needs some logic to make work. Cheers, Paul – pauldzy Feb 13 at 1:08
  • @pauldzy thanks for your thoughts, I too realize this is not going to be a quick solution ;) – blabbath Feb 13 at 6:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.