[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
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.

View solution in original post


All Replies
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.

View solution in original post

Highlighted
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.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 3

Read about some useful Context Variable ideas

Blog