Point in Polygon - issue with converting wkt

[Dorota1]

Hi

I am trying to check if my points are within polygons. And I struggle with wkt converting despite reading many post around it.
I have points list (flare) which I manage to convert to spatial and assign projection.

flare.points<-read_excel("flare.xlsx")
poly<-read_excel("poly.xlsx")

coordinates(flare.points)<-c("Longitude","Latitude")
as(flare.points, "SpatialPoints")

proj4string(flare.points)<- CRS("+proj=longlat +datum=WGS84")

now i have a problem with poly data which contains wkt polygons. I followed also this blog post here: read_csv saves geometry point data as character and wrote a code as follow:

poly.df<- as.data.frame(poly)

clean.poly <- poly.df %>%
st_as_sf(wkt = "wkt",
crs = 4326)

This ends up with error:OGR: Corrupt data
Error in CPL_sfc_from_wkt(x) : OGR error

Random rows of my data frame are converted if i try for example st_as_sfc(poly.df[33088,3]) I get the geometry set

Geometry set for 1 feature
Geometry type: POLYGON
Dimension: XY
Bounding box: xmin: 13.43856 ymin: 43.39011 xmax: 13.45856 ymax: 43.41011
CRS: NA
POLYGON ((13.45856 43.39011, 13.45856 43.41011,...

I am new to R and I have no idea how to check maybe which wkt in my file are then invalid? or how this OGR error can be corrected? skip?

Many thanks
Dorota

[etiennebr]

Hi Dorota,

This is very difficult to debug without having access to the data. When I face this kind of mysterious error, I try to identify which line exactly fails. Since you mention that a random line works, you could try all lines until one fails.

for (i in seq_len(nrow(poly.df)) {
  st_as_sfc(poly.df[i, 3])
}

When the loop stops, compare it with the previous lines

poly.df[i - 2:0, 3]

Make sure that the WKT field was not truncated by Excel and that it is complete with matching parenthesis. Polygon WKT can be quite long. If that's the case, then having access to the original data (preferably not in xlsx, but in a spatial format) is the best solution, but you could still work around the issue by finding all the offending lines and filtering them from poly.df.

[Dorota1]

Hi etiennebr

Thank you for your reply and support on this topic. Indeed some rows in my excel data set are not complete with matching parenthesis. I could maybe try to call for API containing data directly from R but it will be still WKT format.

Now a total dummy (which I am with R) question when I use the code you provided for loops and poly.df[i - 2:0, 3] this prints the lines with data but not the number of row in data frame it is... how would I get a list of all number of rows in dataframe which cause issue ?

Kind regards and one more time big thank you!
Dorota

[etiennebr]

The row number is in i.

i  # row number
poly.df[i, ]  # ith row

[Dorota1]

Thanks again for help. I seem to be achieving some results however still confusing. My output seem to only indicate number of row of polygon the point match but not actual ID this seems not that simple excercise after all

coordinates(flare)<-c("Longitude","Latitude")
as(flare, "SpatialPoints")

proj4string(flare)<- CRS("+proj=longlat +datum=WGS84")

poly_sfc = st_as_sfc(poly$elements.wkt, crs=4326)

plot(poly_sfc)

poly_spdf<- as_Spatial(poly_sfc)
#cleanpoly_spdf<-as_Spatial(poly_clean)

pointsinpoly<- over(flare,poly_spdf)

output<-na.omit(cbind(flare$ID,flare$ID_Year,pointsinpoly))