3

I have original data as a 700000's rows excel, with geometry data in hexwkb format. I want to plot it and make some spatial operations on it like intersect, distance and buffer, on a jupyter notebook. Half of the geometries are null. Part of the remaining ones have apparently invalid geometries.

I had succeed in plotting the first 100 lines, apparently with fine geometries, but I'm struggling to plot all the data.

This code worked for lines with no geometry problems:

Data example

id (int64) | geom (object)

1 | '0105000020E610000001000...

import pandas as pd
import geopandas as gpd
from shapely import wkb
from shapely.geometry import MultiLineString


df=pd.read_excel(r'trips.xlsx')
trips= df.dropna(subset=['geom'])


# create column with converted geometry

def convert_to_hexwkb(geomString):
    geometry = wkb.loads(geomString, hex=True)
    return geometry
trips['geometry']=trips['geom'].map(convert_to_hexwkb)
trips.drop('geom', axis=1, inplace=True)


# convert to GeoDataFrame

gtrips=gpd.GeoDataFrame(trips)

print(gtrips['geometry'][0])
>> MULTILINESTRING ((-9.1474504470825 38.744022369385, -9.147529602050801 38.744121551514, -9.14...


# show trips map

gtrips.plot()
>> got a nice map here

But when I run this to the entire data I get the following error given from the shapely -> geos library, during conversion of string to hexwkb:

WKBReadingError: Could not create geometry because of errors while reading input.

IllegalArgumentException: point array must contain 0 or >1 elements

I couldn't find how to apply Geopandas.GeoSeries.is_empty function to find empty geometries, because it was not a geodataframe at that point. Same for is_valid function from shapely library, since the field was not geometry type.

I've tried to delete exceptional rows with this:

for i, item in enumerate(trips['geom']):
   try:
    trips.at[i,'geometry'] = wkb.loads(item, hex=True)
      #wkb.loads(item,hex=True)
   except:
    trips.drop([i], inplace=True)

And got the errors:

IllegalArgumentException: point array must contain 0 or >1 elements

WKBReadingError: Could not create geometry because of errors while reading input.

another exception occurred: KeyError: '[791] not found in axis' - This related with how the for cycle was constructed probably.

If I run this for debug:

def convert_to_hexwkb(geomString):
    try:
        geometry = wkb.loads(geomString, hex=True)
        return geometry
    except:
        return np.nan

trips['geometry']=trips['geom'].map(convert_to_hexwkb)

I get these errors, multiplied for several lines:

ParseException: Unexpected EOF parsing WKB

ParseException: Premature end of HEX string

Any clue of what should I do to clean geometries of my dataset?

3
  • At least you can filter out trips which have exactly one point.
    – user30184
    May 11, 2020 at 20:27
  • What do you get when using trips = df.dropna(subset=['geom']).reset_index(drop=True) and then for loop? May 11, 2020 at 21:29
  • @KadirŞahbaz, made no difference, I've got the same errors.
    – danalif
    May 11, 2020 at 22:51

1 Answer 1

1

Try this:

import pandas as pd
import geopandas as gpd
import shapely
excel = r"C:\TEMP\ovrig_vag.xlsx" #Roads as multilinestrings wkb's, and various attributes
df = pd.read_excel(excel) #Create a pandas df
# df.shape
# Out: (474399, 11)

#I have a column named wkb. Modify some rows to make them invalid:
dfempty = df.sample(100)
dfempty["wkb"] = '' #Empty strings
df.update(dfempty)

dfnone = df.sample(100)
dfnone["wkb"] = None #None
df.update(dfnone)

dfabc = df.sample(100)
dfabc["wkb"] = 'abc' #Invalid string
df.update(dfabc)

def trywkb(wkb):
    """A function which tries to create a shapely geometry from a wkb,
        if the geometry is valid, it returns it and the string 'valid',
        if not returns the input wkb and the exception message"""
    try:
        geometry = shapely.from_wkb(wkb)
        return pd.Series([geometry, "valid"])
    except Exception as e:
        return pd.Series([wkb, str(e)])

df[["geometry", "validity"]] = df.apply(lambda x: trywkb(x.wkb), axis=1)
# df[["geometry", "validity"]].head(3)
# Out: 
#                                             geometry validity
# 0  MULTILINESTRING ((395958.5480000004 6133594.20...    valid
# 1  MULTILINESTRING ((396293.9639999997 6133965.64...    valid
# 2  MULTILINESTRING ((396325.72300000023 6133982.2...    valid

#There are valid geometries, and two types of invalid geometries:
# df.validity.unique()
# array(['valid', 'ParseException: Unexpected EOF parsing WKB',
#        'ParseException: Premature end of HEX string'], dtype=object)

#Create a geodataframe of the valid
dfvalid = df.loc[df["validity"]=="valid"]
gdf = gpd.GeoDataFrame(data=dfvalid, geometry=dfvalid["geometry"])

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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