Bod has asked for the wisdom of the Perl Monks concerning the following question:

There seems to be a shortage of information available about spatial datatypes but I can create and update POINT data without placeholders. I am struggling to find the right way to do it with placeholders.

my $st = Site::Point->new($st_lat, $st_lng); my $ed = Site::Point->new($ed_lat, $ed_lng); print "$uid - ". $st->sql . " - " . $ed->sql . "\n"; $dbh->do("UPDATE Sector SET start = ?, end = ? WHERE uid = ?", undef, +$st->sql, $ed->sql, $uid); if ($dbh->err) { print "ERROR: " . $dbh->errstr; exit; }

In the above code, start and end are both POINT datatypes. The print statement produces this:
NC33-341 - ST_GeomFromText( ' POINT ( -4.75513748886666 58.2553702983331 ) ', 4326 ) - ST_GeomFromText( ' POINT ( -4.67682813534559 58.3335955352018 ) ', 4326 )

If the query is executed directly without placeholders, it works.
UPDATE Sector SET start = ST_GeomFromText( ' POINT ( -4.75513748886666 58.2553702983331 ) ', 4326 ), end = ST_GeomFromText( ' POINT ( -4.67682813534559 58.3335955352018 ) ', 4326 ) WHERE uid = 'NC33-341'

The error is: ERROR: Cannot get geometry object from data you send to the GEOMETRY field

It wouldn't be the end of the world if I didn't use placeholders here for the latitude and longitude as they are internally calculated and checked that they are numeric and within the vague boundaries of the UK. So, if only $uid used a placeholder there would not be a big risk. But I would rather do it properly if placeholders can be used with spatial datatypes.

Replies are listed 'Best First'.
Re: DBI placeholders for spatial data
by LanX (Sage) on Jun 25, 2021 at 23:42 UTC
  • I can't find any Site::Point on CPAN.
  • I don't know which DB you are using.
  • The specification for that "POINT Datatype" is not clear.

    I can only guess that your query should rather include something like ST_GeomFromText(?,4326) with the string 'POINT ( -4.75513748886666 58.2553702983331 )' as argument.

    Placeholders are for values not for nested SQL-Terms like ST_GeomFromText( String, Integer )

    Cheers Rolf
    (addicted to the Perl Programming Language :)
    Wikisyntax for the Monastery

      I can't find any Site::Point on CPAN.

      You won't - it is a module specific to this application. Although, it is quite a general representation of a point on the globe so it might make a useful CPAN addition.

      I don't know which DB you are using.

      Sorry - I should have included that it is MariaDB.
      Spatial datatypes are something that varies quite a bit between databases. Even between MySQL and MariaDB!

      The specification for that "POINT Datatype" is not clear.

      Exactly!

      I had tried substituting just the numeric values through placeholders like so ST_GeomFromText('POINT( ? ? )') which didn't work. I don't recall the error. I shall look tomorrow. I hadn't tried ST_GeomFromText(?,4326) as you suggest. Thanks - I'll try that tomorrow too...

        Sorry - I should have included that it is MariaDB.

        It would also be helpful to know which version of MariaDB you are connecting to and also which DBD driver (and version) you are using to make that connection.


        🦛

        >    ST_GeomFromText('POINT( ? ? )')

        To make my argument clearer: this 'POINT( ? ? )' might look like an SQL function but it's a string which is parsed by ST_GeomFromText() !

        Please note the missing commas, too. Function arguments in SQL are comma separated.

        The MariaDB documentation calls the argument a WKT with T for Text!

        WKT is not SQL, pretty much like SQL is not Perl

        Your string passed here to ST_GeomFromText() will always include literal question marks in your example.

        There is nothing like ? placeholder handling inside strings in SQL!

        The error you get will be along the line "too many arguments for placeholders" because the two ? inside a string are just ignored.

        Cheers Rolf
        (addicted to the Perl Programming Language :)
        Wikisyntax for the Monastery

        ) The Well-Known Text (WKT) representation of Geometry is designed to exchange geometry data in ASCII form

        update

Re: DBI placeholders for spatial data
by erix (Prior) on Jun 26, 2021 at 11:43 UTC

    Have you tried passing a whole point-string for each placeholder?

    So something like:

    $arg =' POINT ( -4.67682813534559 58.3335955352018 ) ' -- then using execute($arg) for a statement with ST_GeomFromText( ? ) -- -- (maybe casting the argument to WKT, if such casting is supported)

    (just curious, it probably won't work either...)

        You think it will work? That'd be nice, I didn't gather that from your reply (but I didn't TL/DR any links of course). Let's see if it helps Bod.

Re: DBI placeholders for spatial data
by Anonymous Monk on Jun 26, 2021 at 08:55 UTC
     UPDATE Sector SET start = ST_GeomFromText( ' POINT ( -4.75513748886666 58.2553702983331 ) ', 4326 ), end = ST_GeomFromText( ' POINT ( -4.67682813534559 58.3335955352018 ) ', 4326 ) WHERE uid = 'NC33-341'
    Placeholders are not designed for the purposes of building strings, they only occupy the places where separate scalar values can occur (which is to say, not inside an SQL string literal). Two options:
    1. Prepare a query like UPDATE Sector SET start = ST_GeomFromText( ?, ? ), end = ST_GeomFromText( ?, ? ) WHERE uid = ?. Use Perl string interpolation to generate strings  POINT ( -4.67682813534559 58.3335955352018 ) from your point objects: " POINT ( $st->{whatever} $st->{something_else} )" and supply those as placeholder values.
    2. Build the strings for ST_GeomFromText on the SQL side of the query: UPDATE Sector SET start = ST_GeomFromText( 'POINT(' || ? || ' ' || ? || ')', ? ), end = ST_GeomFromText( 'POINT(' || ? || ' ' || ? || ')' WHERE uid = ?. Supply individual parts of $st and $ed as placeholder parameters.
    Both options look yucky. Is there another constructor for your POINT objects that accepts coordinates separately? Note that you can't use $st->sql in a placeholder because placeholders only work for individual scalar values, not arbitrary pieces of SQL code. Indeed, that would destroy the entire purpose of placeholders and make SQL injections possible.
      Is there another constructor for your POINT objects that accepts coordinates separately?

      There is ST_PointFromText but it looks like it is just a synonym for ST_GeomFromText.

        I think that the Site::Point class will benefit from an as_wkt method (or similar) returning strings that you could feed to ST_GeomFromText via placeholders like select ST_GeomFromText(?, ?).