3

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?

9
  • is your table set up with the projection and spatial geometry set? gis.stackexchange.com/questions/233209/… – Mapperz Sep 18 '19 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_BoycottingGISSE Sep 18 '19 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 '19 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 '19 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 '19 at 8:26
2

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

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)})
1
  • This answer worked for me, except I had to add the SRID to df['location'] – jgm_GIS Jun 18 at 18:04

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.