DBI placeholders for spatial data

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;
}
[download]

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.

Comment on DBI placeholders for spatial data Select or Download Code

Replies are listed 'Best First'.
Re: DBI placeholders for spatial data by LanX (Saint) 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}	[reply] [d/l] [select]
Re^2: DBI placeholders for spatial data by Bod (Parson) on Jun 26, 2021 at 00:21 UTC
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...	[reply] [d/l] [select]
Re^3: DBI placeholders for spatial data by hippo (Bishop) on Jun 26, 2021 at 08:37 UTC
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. 🦛	[reply]
Re^4: DBI placeholders for spatial data by Bod (Parson) on Jun 26, 2021 at 10:12 UTC
Re^5: DBI placeholders for spatial data by afoken (Chancellor) on Jun 26, 2021 at 13:18 UTC
Some notes below your chosen depth have not been shown here
Re^5: DBI placeholders for spatial data by 1nickt (Canon) on Jun 26, 2021 at 10:27 UTC
Some notes below your chosen depth have not been shown here
Re^3: DBI placeholders for spatial data by LanX (Saint) on Jun 26, 2021 at 11:23 UTC
> `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* https://mariadb.com/kb/en/st_geomfromtext/ https://mariadb.com/kb/en/wkt/ update WP: Well-known text representation of geometry	[reply] [d/l] [select]
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)` [download] (just curious, it probably won't work either...)	[reply] [d/l]
Re^2: DBI placeholders for spatial data by LanX (Saint) on Jun 26, 2021 at 11:57 UTC
> it probably won't work either... I'm pretty sure it will, see doc links in Re^3: DBI placeholders for spatial data Cheers Rolf _{(addicted to the Perl Programming Language :) Wikisyntax for the Monastery}	[reply]
Re^3: DBI placeholders for spatial data by erix (Prior) on Jun 26, 2021 at 12:01 UTC
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.	[reply]
Re^4: DBI placeholders for spatial data by Bod (Parson) on Jun 26, 2021 at 22:00 UTC
Re^5: DBI placeholders for spatial data by LanX (Saint) on Jun 26, 2021 at 22:31 UTC
Some notes below your chosen depth have not been shown here
Re^4: DBI placeholders for spatial data by LanX (Saint) on Jun 26, 2021 at 12:03 UTC
Re^4: DBI placeholders for spatial data by Bod (Parson) on Jun 26, 2021 at 16:25 UTC
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: 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. 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.	[reply] [d/l] [select]
Re^2: DBI placeholders for spatial data by Bod (Parson) 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.	[reply] [d/l]
Re^3: DBI placeholders for spatial data by Anonymous Monk on Jun 26, 2021 at 10:45 UTC
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(?, ?)`.	[reply] [d/l] [select]
Re^4: DBI placeholders for spatial data by Bod (Parson) on Jun 30, 2021 at 20:35 UTC


go ahead... be a heretic
	PerlMonks