From Geodataframe to PostGIS

Question

I have a geodataframe which I am trying to save to a postgis database.

I have already created a connection to postgis in pgAdmin (username, password, localhost and table)

from geoalchemy2 import Geometry, WKTElement
from shapely import wkt    

engine=create_engine('postgresql://root:root@localhost:xxx/quartiers')

My gdf is as follows:

df_final_quartiers.dtypes
ville_quartier                         object
arrondissement                         object
quartier                               object
geometry                             geometry
weight_Appart                          object
weight_Maison_Dar                      object
weight_Villa                           object
psqm_adj_Appart                        object
psqm_adj_Maison_Dar                    object
psqm_adj_Villa                         object

df_final_quartiers.to_sql('table_name', engine, if_exists='replace', index=False, 
            dtype={'geometry': Geometry(geometry_type='GEOMETRY', srid= 4326)})

here is the error I get:

UndefinedObject: type "geometry" does not exist
LINE 6:  geometry geometry(GEOMETRY,4326),                    ^
The above exception was the direct cause of the following exception:

ProgrammingError                          Traceback (most recent call last)
c:\xxxxnotebooks\notebooks\ProcessingTransactions -v09-NouveauCadgis.ipynb Cell 163 line 1
----> 1 df_final_quartiers.to_sql('table_name', engine, if_exists='replace', index=False, 
      2             dtype={'geometry': Geometry(geometry_type='GEOMETRY', srid= 4326)})
...
    quartier_id TEXT

I am not able to write the geodataframe to PostGIS even though my column df_final_quartiers.geometry is of geometry type

I tried

def store_data_in_postgres(local_file_path):
     # read gpd
    gdf = gpd.read_file(local_file_path)
    
    gdf['geometry'] = gdf.geometry.apply(wkt_loads)
     # remove bad geometries
    gdf= gdf.dropna(subset=['geometry'])
    # adjust columns name
    gdf.columns = gdf.columns.str.lower().str.replace('.', '_').str.replace(' ', '_')

    gdf.to_postgis('geomap', engine, if_exists='replace', index=False, 
                   dtype={'geom': Geometry(geometry_type='GEOMETRY', srid= 4326)})


store_data_in_postgres("./Output data/df_final_quartiers.geojson")

but it did not fix the problem. here is the error:

UndefinedObject: type "geometry" does not exist
LINE 26:  geometry geometry(GEOMETRY,4326)

Answer 1

You shouldnt need dtype={'geom': Geometry(geometry_type='GEOMETRY', srid= 4326)}.

Try without it and use .to_postgis:

import geopandas as gpd
from sqlalchemy import create_engine

gdf = gpd.read_file(r"/home/bera/Desktop/GIStest/distances_houses/house.geojson")
# gdf.dtypes
# houseid       object
# geometry    geometry
# dtype: object

con = create_engine("postgresql://postgres@localhost/data") #postgres is the user, localhost the host, data the database

#If you need to enter a password:
#con = create_engine("postgresql://postgres:yourpassword@localhost/data") 

gdf.to_postgis(name="house789", con=con, schema="test")