in reply to DBI placeholders for spatial data

 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.

Replies are listed 'Best First'.
Re^2: DBI placeholders for spatial data
by Bod (Curate) on Jun 26, 2021 at 10:37 UTC
    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(?, ?).
        I think that the Site::Point class will benefit from...

        Agreed.

        The sql method now returns just the POINT code instead of the complete ST_GeomFromText string. This allows me to do something like this:

        my $point = Site::Point->new('SO 1230 4560'); $dbh->do("INSERT INTO Test SET start = ST_GeomFromText( ? , 4326)", un +def, $point->sql);
        As 4326 is a constant (as long as I am only dealing with this planet!) it doesn't need a placeholder.