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 (Saint) on Jun 25, 2021 at 23:42 UTC | |
by Bod (Parson) on Jun 26, 2021 at 00:21 UTC | |
by hippo (Bishop) on Jun 26, 2021 at 08:37 UTC | |
by Bod (Parson) on Jun 26, 2021 at 10:12 UTC | |
by afoken (Chancellor) on Jun 26, 2021 at 13:18 UTC | |
| |
by 1nickt (Canon) on Jun 26, 2021 at 10:27 UTC | |
| |
by LanX (Saint) on Jun 26, 2021 at 11:23 UTC | |
Re: DBI placeholders for spatial data
by erix (Prior) on Jun 26, 2021 at 11:43 UTC | |
by LanX (Saint) on Jun 26, 2021 at 11:57 UTC | |
by erix (Prior) on Jun 26, 2021 at 12:01 UTC | |
by Bod (Parson) on Jun 26, 2021 at 22:00 UTC | |
by LanX (Saint) on Jun 26, 2021 at 22:31 UTC | |
| |
by LanX (Saint) on Jun 26, 2021 at 12:03 UTC | |
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 | |
by Bod (Parson) on Jun 26, 2021 at 10:37 UTC | |
by Anonymous Monk on Jun 26, 2021 at 10:45 UTC | |
by Bod (Parson) on Jun 30, 2021 at 20:35 UTC |