[postgis-users] Using DBI Placeholders for GeomFromText

[Stuart Gralton]

Thank you Steve et al.  That worked perfectly.

I did have some trouble with the single quotes when substituting 'POINT(1
1)' with variables. However the following works, if anyone else ever needs
to know:

$sth = $dbh->prepare("INSERT INTO table (gid, the_geom)  VALUES (?,
GeomFromText(?, 4326))");

$some_geometry = "POINT(1 1)";

$sth->execute(1, $some_geometry);

The single quotes are all sorted by DBI.

Cheers,
Stuart




On Sat, Jun 12, 2010 at 1:03 PM, P Kishor <punk.kish at gmail.com> wrote:

> On Fri, Jun 11, 2010 at 11:48 PM, Stephen Woodbridge
> <woodbri at swoodbridge.com> wrote:
> > Stuart Gralton wrote:
> >>
> >> Hello Everybody,
> >>
> >> Is it possible to use Perl DBI placeholders to insert a geometry into a
> >> table using GeomFromText.
> >>
> >> When I try the following;
> >>
> >>     $sth = $dbh->prepare("INSERT INTO table (gid, the_geom) VALUES
> >> (?,?)");
> >>     $sth->execute(1, GeomFromText('POINT(1 1)', 4326));
> >
> > You might try something like this.
> >
> >      $sth = $dbh->prepare("INSERT INTO table (gid, the_geom)
> >                            VALUES (?, GeomFromText(?, 4326))");
> >      $sth->execute(1, 'POINT(1 1)');
> >
> > I'm not sure it is valid to pass arbitrary SQL via a place holder.
> > GeomFromText() is a function and not a variable.
> >
>
>
> Steve is correct. I just tested his suggested syntax, and it works.
>
>
> > -Steve
> >
> >>
> >> an error is produced:
> >>
> >>     ERROR:  parse error - invalid geometry
> >>     HINT:  You must specify a valid OGC WKT geometry type such as POINT,
> >> LINESTRING or POLYGON
> >>
> >> The following statement works fine:
> >>
> >>     INSERT INTO table (gid, the_geom) VALUES (1, GeomFromText('POINT(1
> >> 1)', 4326));
> >>
> >>
> >> I am not entirely sure if this is more of a DBI question or a POSTGIS
> >> question, but maybe someone can help?
> >>
> >> Thanks,
> >>
> >> Stuart
> >>
> >>
> >> ------------------------------------------------------------------------
> >>
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at postgis.refractions.net
> >> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> -----------------------------------------------------------------------
> Assertions are politics; backing up assertions with evidence is science
> =======================================================================
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100612/2d60902b/attachment.html>