Dataframe of points to PostGIS

Question

I have a csv file with some info and coordinates and I want to create a table in PostGIS with that CSV file. This is what I have so far:

import pandas as pd
import geopandas
from datetime import datetime
#import psycopg2
from geoalchemy2 import Geometry, WKTElement
from sqlalchemy import *

ruta=('csv_file')
df= pd.read_csv(ruta,sep=';')
df['Tiempo'] = df['Fecha'].map(str)+ " "+ df['Hora']

now = datetime.utcnow()
tiempos=[]
registro=[]
datetime_str = df["Tiempo"]

for i in datetime_str:
    datetime_object = datetime.strptime(i,'%Y%m%d %H:%M:%S.%f')
    check = now - datetime_object
    check.total_seconds()
    if (check.total_seconds() > 300):
        registro.append(1)
    else:
        registro.append(0)
df['Ocurrencia']=registro

gdf=geopandas.GeoDataFrame(df, geometry=geopandas.points_from_xy(df.lng, df.lat))

this is the new geodataframe

#database connection
engine = create_engine('postgresql://postgres:postgres@localhost:5432/Rayos')
#conn = psycopg2.connect("dbname='Rayos' user='postgres' host='localhost' 
#password='postgres'")

gdf.to_sql('table_name', engine, if_exists='append', index=False, 
                     dtype={'geometry': Geometry('POINT', srid= 4326)})

this is the error:

#old error
ValueError: geometry (geometry(POINT,4326)) not a string
#new error
ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'Point'

I made some changes according to this post.What I'm doing wrong?

snowman2 · Accepted Answer

I believe you are probably looking for geopandas-postgis (https://github.com/awburgess/geopandas-postgis; https://pypi.org/project/geopandas-postgis/)

import geopandas as gpd
from sqlalchemy import create_engine

import geopandas_postgis

engine = create_engine("postgresql+psycopg2://postgres:postgres/localhost:test")
gdf.postgis.to_postgis(con=engine, table_name='table_name', if_exists='append', index=False, geometry='Point')

Martín Coll · Answer

I tested every combination I could think of and wasn't able to use to_sql when using geopandas.points_from_xy. The data frame contains shapely  Point instances that can't be translated.

So I completely dropped GeoPandas and instead of

gdf = geopandas.GeoDataFrame(df, geometry=geopandas.points_from_xy(df.lng, df.lat))

I used

df['location'] = "POINT(" + df['lng'].astype(str) + " " + df['lat'].astype(str) + ")"

Then to_sql works as expected when using the dtype

df.to_sql('table_name', engine, dtype={'location': Geometry(geometry_type='POINT', srid=4326)})

Dataframe of points to PostGIS

2 Answers

Add your own answers!

Ask a Question