INSERT INTO POSTGIS TABLE USING PERL

Hello
I am developing a script in Perl that writes points to a spatial table in Postgis. I use :

my $sth = $dbh->prepare("INSERT INTO gtest (geom) VALUES (GeomFromEWKT('SRID=?;POINT(? ? ?)'));")or
     die "Can't prepareSQL: " . $dbh->errstr();;

         $sth->execute($srd, $x, $y, $z)  or  die "Can't  write SQL: " . $sth->errstr();;
When I run the script I get an "Can't write SQL error" that is found near 'SRID='.
And when I use  select  table I get no result.
When I changed the '?' with numbers everything was ok.
Any ideas on how the correct syntax is?
Thank you
George
geomviglaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
earth man2Connect With a Mentor Commented:
O
GeomFromEWKT('SRID=290;POINT(35.2 27.55 12.5)'
function has one string argument. So try something like ...

my $sth = $dbh->prepare(" INSERT INTO gtest (geom) VALUES ( geomFromEWKT( ? ) ) ")or
     die "Can't prepareSQL: " . $dbh->errstr();;
   
     $str = 'SRD=' . $srd . ';POINT( ' . $x . ' ' . $y . ' ' . $z . ')';
     $sth->execute($str)    or die "Can't write sql\n". $sth->errstr();;;

0
 
earth man2Commented:
I guess you are missing commas, try

my $sth = $dbh->prepare( "INSERT INTO gtest (geom) VALUES ( GeomFromEWKT( SRID=?, POINT( ?, ?, ? )))") or
     die "Can't prepareSQL: " . $dbh->errstr();;

         $sth->execute($srd, $x, $y, $z)  or  die "Can't  write SQL: " . $sth->errstr();;
0
 
earth man2Commented:
or even ...

my $sth = $dbh->prepare( "INSERT INTO gtest (geom) VALUES ( GeomFromEWKT( ?, POINT( ?, ?, ? )))") or
     die "Can't prepareSQL: " . $dbh->errstr();;
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
geomviglaAuthor Commented:
Right now my code is:

my $sth = $dbh->prepare(" INSERT INTO gtest (geom) VALUES ( geomFromEWKT(SRID=?,POINT( ? ? ? ))) ")or
     die "Can't prepareSQL: " . $dbh->errstr();;

     $sth->execute($srd,$x,$y,$z)    or die "Can't write sql\n". $sth->errstr();;;

The error I get is
Can't write sql ERROR: syntax error at or near "'35.535295'" at character 79  x=139.38657 y=35.535295 z=18.3

There is no change with or without SRID=

When I have the code with commas I get no point function available.
Any ideas??
0
 
earth man2Commented:
When I have the code with commas I get no point function available.

-- do you have the point function available to this account ?
0
 
earth man2Commented:
there are a number of point functions in pg_calalog but I suspect are specifying too many arguments.
 pg_catalog | point | point            | double precision, double precision | postgres | internal | construct_point | convert x, y to point

That is to say point as implented in a standard installation is 2d

try

my $sth = $dbh->prepare( "INSERT INTO gtest (geom) VALUES ( GeomFromEWKT( ?, POINT( ?, ? )))") or
     die "Can't prepareSQL: " . $dbh->errstr();;
0
 
geomviglaAuthor Commented:
I use postgis standars and this is a geometry field POINT(X Y Z).
I created through AddGeometryColumn function and the dimension is 3.
0
 
geomviglaAuthor Commented:
The sql command that I use directly is:

INSERT INTO gtest (geom) VALUES (GeomFromEWKT('SRID=290;POINT(35.2 27.55 12.5)'));
0
 
geomviglaAuthor Commented:
Finally this is it!
Just a tiny change: it is 'SRID=' so
my $sth = $dbh->prepare(" INSERT INTO gtest (geom) VALUES ( geomFromEWKT( ? ) ) ")or
     die "Can't prepareSQL: " . $dbh->errstr();;
   
     $str = 'SRID=' . $srd . ';POINT( ' . $x . ' ' . $y . ' ' . $z . ')';
     $sth->execute($str)    or die "Can't write sql\n". $sth->errstr();;;

Thank you very much!
George
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.