Re: DBI placeholders for spatial data

Replies are listed 'Best First'.
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 (Archbishop) 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
10.2.39-MariaDB I was using DBD::mysql until yesterday when I switched this project to DBD::MariaDB. I'm still using the MySQL driver for other projects that don't require spatial data. However, I cannot find DBD::MariaDB listed amongst the installed modules so I am struggling to find the version of that! DBD::mysql is version 4.050 edit: Running `cpan -D DBD::MariaDB` tells me that the module is not installed! However, `DBI->connect("DBD:MariaDB:database=xxx", $user, $pass);` connects without problem. Further edit `foreach my $driver(DBI->available_drivers) { print "$driver\n"; }` [download] Produces: `DBM ExampleP File Gofer Mem Pg Proxy SQLite SQLite2 Sponge mysql` [download] So I guess that `DBI->connect("DBD:MariaDB:database=...` is using some form of default driver.	[reply] [d/l] [select]
Re^5: DBI placeholders for spatial data by afoken (Chancellor) on Jun 26, 2021 at 13:18 UTC
Re^6: DBI placeholders for spatial data by Bod (Parson) on Jun 26, 2021 at 23:23 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
Re^6: DBI placeholders for spatial data by Bod (Parson) on Jun 26, 2021 at 10:41 UTC
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]

update