Geodataframe to PostGIS or shapefile

Question

I have a GeoDatFrame df :

df.geometry.name
>>>'geometry'

df.loc[:, 'geometry']
>>>
Identifiant
0       MULTIPOLYGON (((2.827 42.691, 2.828 42.691, 2....
1       MULTIPOLYGON (((2.226 48.859, 2.226 48.861, 2....
2       MULTIPOLYGON (((2.226 48.859, 2.226 48.861, 2....
3       MULTIPOLYGON (((5.556 43.188, 5.557 43.190, 5....
4       MULTIPOLYGON (((3.933 43.629, 3.933 43.629, 3....
                              ...                        
2998    MULTIPOLYGON (((5.195 43.382, 5.195 43.383, 5....
2999                             GEOMETRYCOLLECTION EMPTY
3000    MULTIPOLYGON (((5.015 44.060, 5.017 44.060, 5....
3001    MULTIPOLYGON (((4.913 43.824, 4.915 43.824, 4....
3002    MULTIPOLYGON (((4.740 43.925, 4.741 43.926, 4....
Name: geometry, Length: 3003, dtype: geometry

I wanted to store the table in PostGIS. I tried :

from sqlalchemy import create_engine
engine = create_engine('my_specifications')

df.to_sql('my_name', con=engine, index=True, if_exists='replace')
>>> AttributeError: 'GeometryDtype' object has no attribute 'base'

Since I could not find a solution I tried to save the GeoDataFrame as a shapefile with the following :

df.to_file('my_file.shp')
>>> TypeError: data type not understood

I searched a way to convert my geometry column to WKT format but it failed since I am working with MultiPolygon.

1. Move the GeoDataFrame to PostGIS

if not possible :

2. Save as a shapefile

EDIT 1 :

I want to know how to successfully move my GeoDataFrame to PostGIS.

EDIT 2: As mentioned in the comments, I tried to save the dataframe as a geopackage with :

df.to_file("test.gpkg", driver="GPKG")
>>>TypeError: data type not understood

How can I save the GeoDataFrame and keep rows which have no geometries ?

EDIT 3: I tried to understand what the different geometry types were, here is what I get :

df1.loc[df1.geometry.map(lambda x: x.geom_type)=='GeometryCollection', 'geom']
>>> 
Index
15      ()
16      ()
19      ()
20      ()
21      ()
        ..
2989    ()
2990    ()
2991    ()
2993    ()
2999    ()

I also tried what @BERA suggested :

df1 = df[df.geometry.map(lambda x: x.geom_type)=='MultiPolygon']
df1.geometry.geom_type.unique()
>>> array(['MultiPolygon'], dtype=object)

df1.to_file("test.shp")
>>>
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-73-e3cc1dfd7764> in <module>
      1 os.chdir(r'Z:\SUNR_POWER\5_Business Development\Veille marché\Concurrence\06 - Lauréats CRE\02 - Cartographie')
----> 2 df2.to_file("test.shp")

~\Anaconda3\lib\site-packages\geopandas\geodataframe.py in to_file(self, filename, driver, schema, **kwargs)
    502         from geopandas.io.file import to_file
    503 
--> 504         to_file(self, filename, driver, schema, **kwargs)
    505 
    506     def to_crs(self, crs=None, epsg=None, inplace=False):

~\Anaconda3\lib\site-packages\geopandas\io\file.py in to_file(df, filename, driver, schema, **kwargs)
    123     """
    124     if schema is None:
--> 125         schema = infer_schema(df)
    126     with fiona_env():
    127         with fiona.open(

~\Anaconda3\lib\site-packages\geopandas\io\file.py in infer_schema(df)
    157         [
    158             (col, convert_type(col, _type))
--> 159             for col, _type in zip(df.columns, df.dtypes)
    160             if col != df._geometry_column_name
    161         ]

~\Anaconda3\lib\site-packages\geopandas\io\file.py in <listcomp>(.0)
    158             (col, convert_type(col, _type))
    159             for col, _type in zip(df.columns, df.dtypes)
--> 160             if col != df._geometry_column_name
    161         ]
    162     )

~\Anaconda3\lib\site-packages\geopandas\io\file.py in convert_type(column, in_type)
    143             # numpy datetime type regardless of frequency
    144             return "datetime"
--> 145         out_type = type(np.zeros(1, in_type).item()).__name__
    146         if out_type == "long":
    147             out_type = "int"

TypeError: data type not understood

I get the same error if I wanted to save my GeoDataFrame to a GeoPackage.

Answer 1

Try to specify your datatype.

df.to_sql('my_name', con=engine, index=True, if_exists='replace',  dtype=({'geometry':'geometry'}))

Answer 2

########### writing data to ESRI shapefile 

#############################################################################
########## change if any column has data as date/time type to string 
########## as, shapefile does not support datetime datatype #################

gdf_final['forecast_date']=gdf_final['forecast_date'].astype(str)


##### write the shapefile using geopandas as #################


shape_file_name='outfile.shp'

gdf_final.to_file(shape_file_name)

prjname_m='outfile.prj'

prj_m = open(prjname_m, "w")

epsg="GEOGCS["+"GCS_WGS_1984"+",DATUM["+"D_WGS_1984"+",SPHEROID["+"WGS_1984"+",6378137.0,298.257223563]],PRIMEM["+"Greenwich"+",0.0],UNIT["+"Degree"+",0.0174532925199433]]"

prj_m.write(epsg)

prj_m.close()