GeoPandas data types from PostGIS are always string

Question

I am using GeoPandas from_postgis function to read data from PostGIS. However, the data types of the GeoPandas columns are always object.

Data I store in PostGIS is mixture of text/char, bool, integer and geom (snip below).

I have no issue with geom and text but integer and bool data is not maintained over the process.

Python script I created reads 4 layers from the database, clip it within a polygon and export it as a geopackage.

conn_string = f'postgresql://postgres:abcs@15612:5432/wi_designsdb' #database string
db = create_engine(conn_string) #create engine from the string above
conn = db.connect() #connect to database
df= gpd.GeoDataFrame.from_postgis(f'select * from pia.pole',con=conn, geom_col='geometry')
df = df.clip(build_project)
df = df[columns_list]
df.to_file(merginmaps_output_folder + 'pole.gpkg')

Data types in DB:

data types in DB

Data types in GeoDataFrame:

Data types in GeoDataFrame

Data types in QGIS:

Data types in QGIS

• Ian Turton: please add the code you are using
• foxhq: @IanTurton I've added the code
• Bera: (Your code is working for my tables with int columns)
• D T: Do you have any NaN values? If you do, columns are often set to object. Pandas Gotchas Have you tried converting the dtypes?
• foxhq: @DT most of these columns are NULL values, they don't store any value in it.

Answer 1

Underlying problem is still unknown (I suspect most likely the null values) but I created a function to read data type from the database and save it as schema. I used that schema for fiona to control how the file is written.

def table_data_type(table, schema='pia', geom='Linestring', dbname='wi_designsdb'):
table_schema = {'geometry': geom, 'properties': {}}
conn = psycopg2.connect("host= dbname= user=postgres password=")
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute(f"""SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = '{schema}' AND table_name = '{table}'""")
for row in cur:
 table_schema['properties'].setdefault(row['column_name'], row['data_type'])
 print("column: {col}, type: {col_type}".format(
 col = row['column_name'], col_type = row['data_type']))
 
return table_schema

Another function for matching postgresql datatypes with fiona datatypes

 df_schema = table_data_type(table= x, schema='pia', geom=df.geom_type, dbname='wi_designsdb')
 temp_dict = df_schema['properties'].copy()
 for dfp in temp_dict:
 if dfp not in df_columns_list:
 df_schema['properties'].pop(dfp)
 else:
 if df_schema['properties'][dfp] == 'text' or df_schema['properties'][dfp] == 'character varying':
 df_schema['properties'][dfp] = 'str'
 elif df_schema['properties'][dfp] == 'boolean':
 df_schema['properties'][dfp] = 'bool'
 elif df_schema['properties'][dfp] == 'integer':
 df_schema['properties'][dfp] = 'int'
 df_schema['properties'].pop('geometry')

and saving geopackage

df.to_file(output_folder + x + '.gpkg', schema= df_schema)