[resolved] How to insert row in PostgreSQL using ST_GeographyFromText - Postgis?

Four Stars

[resolved] How to insert row in PostgreSQL using ST_GeographyFromText - Postgis?

Hi all, 
I have a job like two pictures below.
I have :
+ input: excel file (id, longitude, latitude)
+ output: postgresql's table (id , location ) and data type of "location"column is "geometry"
When inserting new record  I have to call function "ST_GeographyFromText" in database ( ex: INSERT INTO out_put_table VALUES (1,ST_GeographyFromText('POINT(106.667010 10.776100)')::geometry);  )
My question: can I call function "ST_GeographyFromText" in tmap (when mapping data) ? if not, could you give me several solutions ?
I reviewed: tPostgresqlSP, tPostgresqlRow component (but I think It can not use in this scenario)
Many thanks.


Accepted Solutions
Highlighted
Four Stars

Re: [resolved] How to insert row in PostgreSQL using ST_GeographyFromText - Postgis?

I resolved by using tPostgresqlRow (do not use tMap) 
t_connection   ------(subjob ok)------ t_excel_input  ------(main)------ t_PostgresqlRow  ------(subjob ok)------ t_PostgresqlCommit
t_PostgressqlRow query (basic setting) : "insert into out_put_value (id, location) values (" + row1.id + "," + "ST_GeographyFromText(" + " ' " + "POINT(" + row1.longitude + " " + row1.latitude + ")" + " ' " + ")::geometry" + " ) ;"
 Thanks for reading.

All Replies
Highlighted
Four Stars

Re: [resolved] How to insert row in PostgreSQL using ST_GeographyFromText - Postgis?

I resolved by using tPostgresqlRow (do not use tMap) 
t_connection   ------(subjob ok)------ t_excel_input  ------(main)------ t_PostgresqlRow  ------(subjob ok)------ t_PostgresqlCommit
t_PostgressqlRow query (basic setting) : "insert into out_put_value (id, location) values (" + row1.id + "," + "ST_GeographyFromText(" + " ' " + "POINT(" + row1.longitude + " " + row1.latitude + ")" + " ' " + ")::geometry" + " ) ;"
 Thanks for reading.
One Star

Re: [resolved] How to insert row in PostgreSQL using ST_GeographyFromText - Postgis?

hi mrjody,
are you tested you solution ? how t_PostgressqlRow  can know row1.id?
Tanks.
Four Stars

Re: [resolved] How to insert row in PostgreSQL using ST_GeographyFromText - Postgis?

@nono1: yes, of course I tested.
t_excel_input component: it is refered to an excel file with three columns (id, longitude, latitude)
"How t_PostgresqlRow can know row1.id?" --> it depend on Talend mechanism.
You can read from: 
1/ link 1
2/ link 2
Regard.

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 3

Read about some useful Context Variable ideas

Blog

Talend Studio Improvements for API Services

Taken a look at the Talend Studio improvements for API Services

Watch Now

Data Integration Success Stories

Take a look at some Data Integration success stories

Read

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch