2

I have fetched some data (39 objects) from my PostGIS database and converted this data into a matrix:

rs <- dbSendQuery(con, "SELECT ST_AsText(polygon_accumulation) AS ShapeWKT from accumulation;")
obj_wkt_as_m <- as.matrix(fetch(rs))
obj_wkt_m <- matrix(obj_wkt_as_m, ncol=39, nrow=1)
obj_wkt_m_t <- t(obj_wkt_m)

My data is now formatted in this way:

     [1]
[1,] "MULTIPOLYGON(((4459824.3565246 5470840.94935909, ...
[2,] "MULTIPOLYGON(((4458772.75622644 5473352.14772341, ...
 ...

For the next steps I followed the instructions in this question Plotting large numbers of objects on a chart using R and WKT format.

obj_id <- 1:39
objects_1 <- data.frame(ShapeWKT=obj_wkt_m_t, OBJECTID=obj_id)
poly.sp <- SpatialPolygonsDataFrame(readWKT(objects_1$ShapeWKT[1]), data=data.frame(OBJECTID=objects_1$OBJECTID[1]))

Up to here it works and I can plot the polygon poly.sp! This procedure works for point data - getting from my database - as well. But when I want to iterate through the remaining (multi)polygon objects with this commands:

for (n in 2:length(objects_1$OBJECTID)) {
    poly.sp <- rbind(poly.sp, 
                SpatialPolygonsDataFrame(readWKT(objects_1$ShapeWKT[n]), 
                data.frame(OBJECTID=objects_1$OBJECTID[n])))
                                        }

I get the ERROR:

Error in validObject(res) : 
invalid class “SpatialPolygons” object: non-unique Polygons ID slot values

Can I use this iteration for (multi)polygons as well, in some way?

EDIT Here is some sample data from fetching the results from my db query

rs <- dbSendQuery(con, "SELECT ST_AsText(polygon_accumulation) AS ShapeWKT from accumulation LIMIT 2;")
obj_wkt <- fetch(rs)
dput(obj_wkt)
structure(list(shapewkt = c("MULTIPOLYGON(((4460657.83139692 5472732.9336513,4460660.21687119 5472737.52110183,4460662.60234546 5472740.27357215,4460664.98781974 5472741.92505434,4460668.29078412 5472744.67752465,4460670.85975641 5472746.51250486,4460671.96074454 5472747.24649695,4460675.63070496 5472748.89797914,4460678.93366934 5472750.54946133,4460682.0531357 5472751.83394747,4460688.47556644 5472753.85242571,4460692.14552686 5472755.68740592,4460695.44849124 5472756.78839404,4460699.11845166 5472758.80687227,4460702.05442 5472761.00884853,4460704.99038833 5472762.84382874,4460708.29335271 5472765.22930301,4460711.77981511 5472767.24778124,4460713.98179136 5472768.53226739,4460716.55076366 5472770.00025156,4460719.11973595 5472771.46823573,4460722.23920231 5472772.9362199,4460725.54216669 5472773.67021198,4460728.84513107 5472775.32169417,4460731.59760139 5472776.4226823,4460734.3500717 5472778.07416449,4460736.18505191 5472778.99165459,4460738.75402421 5472779.17515261,4460740.58900442 5472778.25766251,4460740.95600046 5472774.95469813,4460739.85501234 5472771.28473771,4460737.8365341 5472768.89926343,4460736.55204796 5472766.33029114,4460734.90056577 5472763.02732676,4460734.3500717 5472760.45835446,4460734.16657368 5472758.07288019,4460733.24908358 5472755.68740592,4460730.8636093 5472753.11843362,4460728.47813503 5472751.09995539,4460724.44117857 5472749.81546924,4460721.68870825 5472749.4484732,4460718.75273991 5472749.4484732,4460716.18376762 5472749.4484732,4460713.79829334 5472747.61349299,4460712.5138072 5472746.51250486,4460709.39434084 5472744.86102267,4460709.39434084 5472739.72307808,4460709.39434084 5472738.43859194,4460709.57783886 5472736.4201137,4460708.66034875 5472734.58513349,4460706.09137646 5472732.19965922,4460703.33890614 5472730.73167505,4460701.32042791 5472729.63068692,4460700.58643583 5472727.24521265,4460699.11845166 5472725.0432364,4460697.09997343 5472723.39175421,4460696.18248332 5472720.82278191,4460696.73297738 5472718.43730764,4460697.28347145 5472716.05183336,4460698.01746353 5472713.29936305,4460697.09997343 5472710.54689273,4460696.18248332 5472707.42742637,4460694.34750311 5472705.40894814,4460691.77853081 5472703.39046991,4460688.29206841 5472701.73898772,4460684.43860997 5472700.63799959,4460679.4841634 5472699.35351345,4460675.997701 5472699.17001543,4460674.52971683 5472700.08750553,4460672.32774058 5472701.73898772,4460668.29078412 5472705.40894814,4460666.27230589 5472709.44590461,4460664.07032963 5472710.73039075,4460661.86835338 5472711.83137888,4460659.11588306 5472712.56537096,4460656.17991473 5472713.84985711,4460653.61094243 5472715.5013393,4460651.22546816 5472717.15282149,4460649.75748399 5472719.17129972,4460649.75748399 5472721.18977795,4460649.94098201 5472723.39175421,4460651.22546816 5472724.67624035,4460653.97793847 5472727.61220869,4460654.89542858 5472729.81418495,4460656.54691077 5472732.0161612,4460657.83139692 5472732.9336513)))", 
"MULTIPOLYGON(((4460668.76954594 5474783.75542964,4460673.87013239 5474788.30460134,4460674.28369345 5474794.64587098,4460671.25091232 5474804.84704388,4460668.63169225 5474811.4640209,4460668.49383856 5474817.80529054,4460670.97520494 5474823.31943806,4460673.7322787 5474829.38500032,4460675.52437664 5474833.52061096,4460676.35149877 5474842.34324698,4460673.04301026 5474852.95798095,4460669.87237544 5474857.78286002,4460665.18535005 5474864.26198335,4460660.91188573 5474869.91398455,4460657.18983616 5474879.70159639,4460649.47002964 5474885.49145128,4460642.02593049 5474890.31633036,4460633.34114816 5474894.17623362,4460617.90153512 5474896.93330737,4460602.59977577 5474898.863259,4460592.95001762 5474899.41467375,4460576.1318677 5474893.76267255,4460568.54991487 5474889.62706192,4460564.55215792 5474886.04286603,4460558.07303459 5474879.15018164,4460553.52386289 5474869.77613087,4460552.69674076 5474865.08910548,4460550.35322807 5474860.2642264,4460546.76903218 5474854.6122252,4460542.63342155 5474850.47661456,4460536.15429822 5474846.75456499,4460531.60512652 5474845.23817443,4460526.090979 5474843.58393017,4460522.36892943 5474840.82685641,4460519.61185568 5474834.76129415,4460517.13048929 5474827.45504869,4460515.33839135 5474824.69797494,4460513.27058603 5474822.90587699,4460510.09995121 5474822.35446224,4460506.24004795 5474823.73299912,4460502.38014469 5474824.97368231,4460499.07165618 5474826.62792657,4460497.00385087 5474827.317195,4460487.21623903 5474825.24938969,4460480.46140832 5474822.49231593,4460474.39584606 5474819.59738848,4460463.50540472 5474811.4640209,4460460.3347699 5474800.02216481,4460456.8884277 5474797.26509105,4460452.61496338 5474796.16226155,4460449.85788962 5474794.23230992,4460448.61720643 5474792.02665091,4460446.13584005 5474789.40743084,4460444.75730317 5474786.09894234,4460443.51661998 5474782.51474645,4460442.55164416 5474779.48196532,4460440.20813147 5474776.44918419,4460436.21037452 5474772.72713461,4460432.90188601 5474771.07289036,4460427.80129956 5474768.72937767,4460426.284909 5474767.62654816,4460423.38998155 5474764.59376703,4460420.63290779 5474761.28527852,4460418.56510248 5474758.1146437,4460416.77300453 5474754.6683015,4460415.67017503 5474750.25698349,4460416.22158978 5474746.94849498,4460414.98090659 5474743.50215279,4460413.05095496 5474740.74507903,4460411.39671071 5474737.43659052,4460411.25885702 5474733.85239464,4460411.81027177 5474730.54390613,4460411.12100333 5474726.54614918,4460410.84529596 5474722.54839223,4460410.56958858 5474719.92917216,4460412.6373939 5474716.34497628,4460415.25661397 5474710.96868245,4460418.01368772 5474707.66019395,4460422.42500574 5474702.28390012,4460425.31993318 5474699.52682636,4460428.490568 5474697.32116736,4460430.8340807 5474694.28838622,4460433.72900814 5474690.84204403,4460435.10754502 5474687.94711658,4460435.65895977 5474683.12223751,4460435.79681346 5474679.67589531,4460436.4860819 5474676.3674068,4460438.41603353 5474672.64535723,4460440.34598516 5474668.78545397,4460443.10305891 5474665.20125809,4460447.51437693 5474661.06564745,4460451.78784125 5474656.93003681,4460458.12911089 5474652.24301143,4460465.57321004 5474648.79666923,4460471.77662599 5474646.45315654,4460478.94501776 5474644.79891228,4460484.45916527 5474643.97179016,4460491.21399598 5474644.52320491,4460495.90102136 5474646.8667176,4460499.89877831 5474648.79666923,4460501.96658363 5474652.65657249,4460507.89429221 5474656.93003681,4460513.95985447 5474663.27130646,4460517.13048929 5474667.95833184,4460521.12824624 5474672.09394248,4460524.29888106 5474675.12672361,4460526.36668638 5474676.91882155,4460533.67293184 5474679.813749,4460541.39273836 5474685.74145758,4460544.70122686 5474690.97989772,4460546.63117849 5474696.63189892,4460548.56113013 5474702.55960749,4460550.07752069 5474705.73024232,4460552.83459445 5474709.86585295,4460555.72952189 5474712.89863408,4460560.83010834 5474716.62068366,4460565.24142636 5474720.06702585,4460570.75557387 5474723.65122174,4460576.54542876 5474727.511125,4460582.05957627 5474729.85463769,4460585.50591847 5474731.78458932,4460598.7398725 5474744.74283598,4460608.25177697 5474749.15415399,4460614.04163186 5474752.32478881,4460620.3829015 5474756.04683838,4460626.44846376 5474759.49318058,4460633.34114816 5474763.62879122,4460639.13100305 5474766.93727972,4460645.19656531 5474770.38362192,4460652.64066446 5474773.41640305,4460658.43051935 5474776.03562312,4460664.49608161 5474778.79269688,4460666.56388693 5474781.54977064,4460668.76954594 5474783.75542964)))"
)), .Names = "shapewkt", row.names = 1:2, class = "data.frame")
  • I sympathise, but tricky to help without any data. Can you not provide or link to poly.sp, perhaps using dput or Dropbox? – SlowLearner Aug 26 '13 at 20:39
  • @SlowLearner: I'm sorry that I don't follow your exact instructions. I have posted the fetched results from the query and limited it to two objects. I hope this will help. I never used dput before. – Stefan Aug 27 '13 at 12:30
2

Running into this issue is a real pain. The reason is that the slot ID's (and rownames in the @data slot) are not unique in the objects that you are attempting to rbind. You can use spChFIDs to reassign the slot ID's so you can use rbind. I usually add code to my loop that incrementally adds ID's based on the number of features. You may want to also try spRbind in the maptools package.

  • I wonder that this method works perfect with point data. Do I have to use spChFIDs on the objects_1 dataframe (posted in my question)? I tried it like this: objects_2 <- spChFIDs(objects_1, as.character(objects_1$OBJECTID)). But this failed. – Stefan Aug 26 '13 at 22:26
1

I had to face the same errors when trying to import PostGIS geometries into R. I could manage to iterate readWKT() through the polygons with the help of the apply-function-family:

All credits to: https://spacetimecereal.com/2015/01/13/loading-postgis-geometries-into-r-without-rgdal-an-approach-without-loops/

Connect to PostgreSQL DB:

drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname = DBname_1, user = DBuser, host = DBhost, port = DBport, password = DBpassword)

Determine EPSG (Is optional. This code also works without the p4s-arguments below)

EPSG = make_EPSG()
p4s = EPSG[which(EPSG$code == 31467), "prj4"]

SQL query which returns the PostGIS geometry as WKT

rs <- dbSendQuery(con, 'SELECT name, area, use, ST_AsText(geom) FROM views.table;' )
res <- fetch(rs, -1)
dbClearResult(rs)

I added a 'gid' column 'cause I didn't really have one beforehands. i. e. 5th column:

res$gid <- row.names(res)

APPLYs readWKT() on the geom-column

sp_tmp <- lapply(res[,4], "readWKT", p4s)

APPLYs spChFIDs() on my 'gid' column:

sp_tmp_ID <- mapply(spChFIDs, sp_tmp, as.character(res[,5]))

Create a SpatialPolygonsDataFrame with the geometry and the attribute table

row.names(res) <- res[,5] 
data <- SpatialPolygonsDataFrame(SpatialPolygons(unlist(lapply(sp_tmp_ID, function(x) x@polygons)), proj4string=(CRS(p4s))), data=res[,-ncol(res)])
  • In my case it also works without the row.names(res) <- res[ ,5] 'cause (I guess) my gid column aka res[ ,5] is consecutively numbered. If I were trying sp_tmp_ID <- mapply(spChFIDs, sp_tmp, as.character(res[ ,1])), I'd have to use row.names[ ,1]. row.names[ ,1] is my 'name' column. – andrasz Jun 24 '16 at 8:21
0

Something like this has worked for me in the past:

objects_2 <- spChFIDs(objects_1, paste("newid", 1:nrow(objects_1), sep = ""))
  • I used my point data to learn something about the spChFIDs-method: This is how the object_1 data looks like (first the ShapeWKT, second the OBJECTID) POINT(4460582.78553552 5474706.7445423) 1 When I use your command, the following error appears: Error in (function (classes, fdef, mtable) : unable to find an inherited method for function ‘spChFIDs’ for signature ‘"data.frame", "character"’ – Stefan Aug 27 '13 at 10:36
  • objects_1 is an sp object, right? If you type class(objects_1) what do you get in return? Incidentally if you had given us reproducible code and data we would have resolved this by now... – SlowLearner Aug 27 '13 at 10:42
  • The return is [1] "data.frame". The iteration with point data produces (first coordinates, second OBEJCTID): (4460580, 5474710) 1 and the return [1] "SpatialPointsDataFrame" attr(,"package") [1] "sp". The spChFIDs-method doesn't work here as well. – Stefan Aug 27 '13 at 10:55
  • As far as I know, you can't use spChFIDs on a data.frame only an sp object. – SlowLearner Aug 27 '13 at 10:57
  • In the spChFIDs doc there is nothing to read about a SpatialPointsDataFrame. So the method is not intended for point data? You say the code in your post worked for you in the past. Do you have an example for the objects_1 data? – Stefan Aug 27 '13 at 11:18

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.