0

I have a polygon file, taken from an Oracle database, that looks something like this:

   ID FMC_AREA_TYPE_NAME DESCRIPTION
1 162            HARBOUR     Esbjerg
2 164            HARBOUR    Thyborøn
                                                                                                                                                                 POLYGON
1 MDSYS.SDO_GEOMETRY(2003, 8307, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1), MDSYS.SDO_ORDINATE_ARRAY(8.38, 55.46, 8.45, 55.46, 8.45, 55.49, 8.38, 55.49, 8.38, 55.46))
2    MDSYS.SDO_GEOMETRY(2003, 8307, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1), MDSYS.SDO_ORDINATE_ARRAY(8.2, 56.68, 8.26, 56.68, 8.26, 56.72, 8.2, 56.72, 8.2, 56.68))
                                                                                            POLYGON_AS_CLOB     NAME
1 8.380000 55.460000 \n8.450000 55.460000 \n8.450000 55.490000 \n8.380000 55.490000 \n8.380000 55.460000 \n  Esbjerg
2 8.200000 56.680000 \n8.260000 56.680000 \n8.260000 56.720000 \n8.200000 56.720000 \n8.200000 56.680000 \n Thyborøn

How can i make that into polygons, preferably using R. I have never worked with Oracle before. I tried using the sf package.

st_as_sfc(h$POLYGON_AS_CLOB)

But it seems that sf it doesnt support that geometry type:

OGR: Unsupported geometry type
#

Used the answer from Spaceman, and got this. Not pretty, but it works.

clob <- as.character(h$POLYGON_AS_CLOB)

mp <- list()
for(y in 1:nrow(h)){
x <- st_polygon(list(matrix(as.numeric(unlist(strsplit(strsplit(clob,"\n")[[y]]," "))),ncol=2, byrow=TRUE)))

mp = list(x, unlist(mp,recursive=FALSE))
}

mp1 = st_multipolygon(mp)

xx <- as(mp1, "Spatial")

proj4string(xx) = "+proj=longlat +ellps=WGS84"

mapview(xx)

havne <- st_as_sf(xx)

havne$SI_HARB <- 1

st_write(havne, "Q:/Havne.shp", delete_layer = T)
  • How many of them have you got? How complex might they get? Islands and holes? Does 2003 and 8307 mean anything, like possibly the coordinate system? Are the coordinates in lat-long? If the things aren't too complex then it should be doable with some string processing (chop bits off, separate the coordinates, rebuild into a matrix etc). – Spacedman Mar 15 at 15:09
  • if you split POLYGON_AS_CLOB on \n using strsplit(), then split each of those on a space character, you might get values for an x-y matrix that you can make into a simple polygon with st_polygon.... – Spacedman Mar 15 at 15:10
  • I could do it with some labour, but would prefer if there was some automatic way of doing it. It seems that the columns are in some form of well-known text format, so thought that there would be a function or something for this. – Jeppe Olsen Mar 15 at 15:11
  • Some info on the format here: docs.oracle.com/cd/B10500_01/appdev.920/a96630/… - the chop and split approach will work if all the rows have the same format, but if you have complex polygonal shapes with islands then it might get tricky... I don't suppose you can share the entire dataset? – Spacedman Mar 15 at 15:14
  • I could do that, but not sure how. Its something like 900 lines – Jeppe Olsen Mar 15 at 15:14
1

It looks like all your features have five coordinates, and are rectangles. The POLYGON_AS_CLOB looks like \n separated coordinate pairs, with the coordinates space-separated. Given one of those strings:

> clob
[1] "8.380000 55.460000 \n8.450000 55.460000 \n8.450000 55.490000 \n8.380000 55.490000 \n8.380000 55.460000 \n"

Then this mess will splin on \n, split on space, then form a 2-column matrix and make it into a list for st_polygon to make a polygon feature:

> st_polygon(list(matrix(as.numeric(unlist(strsplit(strsplit(clob,"\n")[[1]]," "))),ncol=2, byrow=TRUE)))
POLYGON ((8.38 55.46, 8.45 55.46, 8.45 55.49, 8.38 55.49, 8.38 55.46))

loop or apply that over your dataset and you should be able to create a spatial data frame.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.