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?

is your table set up with the projection and spatial geometry set? https://gis.stackexchange.com/questions/233209/copy-geometry-data-as-hexewkb-from-csv-to-table-in-postgis — Mapperz, Sep 18 '19 at 01:27
Sorry - what is a CDV file? It seems that you don't have geometry in this table, just columns of lat/lon, etc. If that is the case, you don't need postgis to do the import, but you will need it to do the conversion of lat and lon columns to geometry. I would think that you need an existing table in the same schema as the CDV (sqlite?) table. — DPSSpatial_BoycottingGISSE, Sep 18 '19 at 02:52
Maybe you can provide us a part of the file you want to import, as actually stated it is unclear what you expect and what is the incoming format — Maximilien jaffrès, Sep 18 '19 at 09:26
I recently update the post with new code lines and now with the new lines I´m getting a new error in the last part — Luis Medina, Sep 18 '19 at 18:55
if i read this : https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html?highlight=to_sql it could be a syntax problem because 'Geometry('POINT', srid= 4326)' should be a string — Maximilien jaffrès, Sep 19 '19 at 08:26
However, i don't really understand why you want to use geopandas to import long\lat to Postgres Postgis. You can do it directly for exemple like this : https://www.dataiku.com/learn/guide/other/geo/convert-coordinates-with-PostGIS.html — Maximilien jaffrès, Sep 19 '19 at 08:29
@Maximilienjaffrès I made some changes to the code and also i preffer doing with python since I don`t know how to do what you said automaticly in PostGIS — Luis Medina, Sep 20 '19 at 01:31
I mean, you can use python, connect to the database as you did, but instead of going throug geopandas to write your queries, with a bit of text management, regexp and some SQL, you would succeed to write them. But geopandas should do this job, that's right =) Concerning your last error, are you sure you have the right column, maybe try to convert it in text (Hex or WKB) before running the "to_sql" command — Maximilien jaffrès, Sep 20 '19 at 07:30
I did what you said in the comment from Sept 19, I wrote the SQL queries in python and i works pretty fine, I also tried the answer from @snowman2 and it works too — Luis Medina, Sep 28 '19 at 01:43

snowman2 · Accepted Answer · 2023-06-07T21:46:05.813

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')

UPDATE: geopandas has a to_postgis method now.

from sqlalchemy import create_engine
engine = create_engine("postgresql://myusername:mypassword@myhost:5432/mydatabase")
gdf.to_postgis("my_table", engine)

score 1 · Answer 2 · answered May 03 '20 at 01:03

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)})

This answer worked for me, except I had to add the SRID to df['location'] — jgm_GIS, Jun 18 '21 at 18:04

Dataframe of points to PostGIS

2 Answers2