1

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

enter image description here

#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? gis.stackexchange.com/questions/233209/… – Mapperz Sep 18 at 1:27
  • 1
    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 Sep 18 at 2:52
  • 1
    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 at 9: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 at 18:55
  • if i read this : pandas.pydata.org/pandas-docs/stable/reference/api/… it could be a syntax problem because 'Geometry('POINT', srid= 4326)' should be a string – Maximilien jaffrès Sep 19 at 8:26
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.