How to write from pandas Dataframe to PostgreSQL with a geometry type

atimetobesmall
Level 1
How to write from pandas Dataframe to PostgreSQL with a geometry type

Hello,



In my python receipt, I would like to write in my PostreSQL database (with PostGis extension) data with a geometry type. Therefore, I declared a schema and created my table with this specific schema 



 




schema = [{"name": 'id', "type": 'int'}, {"name": 'value', "type": 'string'}, {"name": 'the_geom', "type": 'geometry'}]

test_schema = dataiku.Dataset("test_schema")
test_schema.write_schema(schema, dropAndCreate=True)

with test_schema.get_writer() as writer:
writer.write_dataframe(df_test)


However there's no way "Dataiku" recognizes any geometry type I tried. Is it possible to have the list of types that Dataiku can handle?

0 Kudos
5 Replies
Clรฉment_Stenac
Hi,

At the moment, it's unfortunately not possible to directly write Postgis types directly from Python. What we would recommend is to write the WKT as a string column, and then use a SQL Script recipe to copy it into a geometry column.

Adding real support for Postgis is on our roadmap, but we do not yet have an ETA for this.
0 Kudos
atimetobesmall
Level 1
Author
Ok, thank you for your anwser
0 Kudos
Mingo
Level 2

Hello Clรฉment,

Have you any news on that topic? I have the same issue with visual recipes. Do we still have to add a SQL scripts to our Workflow?

Thanks in advance

Romain

 

0 Kudos
CoreyS
Dataiker Alumni

Hi @Mingo I can confirm that there is currently no update to this as Clement's reply above is still accurate. I hope this helps!

Looking for more resources to help you use Dataiku effectively and upskill your knowledge? Check out these great resources: Dataiku Academy | Documentation | Knowledge Base

A reply answered your question? Mark as โ€˜Accepted Solutionโ€™ to help others like you!
0 Kudos
Mingo
Level 2

Thanks Corey, 

I don't need to look for another solution anymore. So it helps.