Re: 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:

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.

Comment on Re: DBI placeholders for spatial data Select or Download Code

Replies are listed 'Best First'.
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
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);` [download] As 4326 is a constant (as long as I am only dealing with this planet!) it doesn't need a placeholder.	[reply] [d/l] [select]


No such thing as a small change
	PerlMonks