Populating Spatial data via PHP

PHP
1

I was previously unaware of the Spatial data options in MySql, until seeing this post.
In a PHP application I made, I had in my ignorance been using two float columns in the database to store latitiude and longitude coordinates, which to be fair has worked fine.
But on seeing this I was interested in the ability to work with distances and possibly expanding to using other spatial data types within the database.

My first eperimental step was to add a single location column to one of the tables with a Point type.
Next was to write a simple PHP script to take the location data from the lat/lon columns, and update that into the new loc (location) column.

My problem is, I can’t seem to get it to work via the PHP.

$query = "SELECT id, lat, lon FROM changes";

$dbdata = $dbedit->query($query)->fetchAll(); // Get the location data


$updt = "UPDATE changes SET loc = ST_GeomFromText(Point(?)) WHERE id = ?" ; // Numerous variations on this tried

$sql = $dbedit->prepare($updt) ;

foreach($dbdata as $dat){
	$loc = $dat['lon'].', '.$dat['lat'] ; // Location string
	$ins = [$loc, $dat['id']] ;   // Param array
	
	$sql->execute($ins); // Update each row
}

The error I get with this (1305) says the Function Point does not exist.
If I change the update query to:-

$updt = "UPDATE changes SET loc = ST_GeomFromText(?) WHERE id = ?" ;

It runs without error, but the loc column defaults to null when I look at the table after.

If I run a query in PhpMyAdmin like:-

UPDATE changes SET loc = Point(-0.50485, 53.68115) WHERE id = 1

It updates just fine. If I run without a prepared statement it works, which suggests it’s the way I’m handling placeholders, but I have yet to find the right way.
The table is like:-

CREATE TABLE `changes` (
  `id` int(11) NOT NULL,
  `mid` int(11) NOT NULL,
  `date` date NOT NULL,
  `lat` decimal(10,8) NOT NULL,
  `lon` decimal(10,8) NOT NULL,
  `loc` point DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

The database is MariaDB latest.
It’s probably a simple syntax error I’m making, but I’m yet to figure it out.
In the short term I can run with out prepared statements, just to update the table structures to the new system, as I know the data I already have is safe.
But in the long run I will need the application to capture new data from outside sources, so will need to know how to make it work with prepared statements.

1 Like
2

OK, I got this now:-

$updt = "UPDATE changes SET loc = Point(?, ?) WHERE id = ?" ;
...
$ins = [$dat['lon'], $dat['lat'], $dat['id']] ;

I need separate params for the lon and lat, even though it’s a single column.

2 Likes
3

I’d assume it’s more to do with it being 2 number values, rather than a single string “x,y” which in certain locales could be construed as a single float even if cast.

image
image600Ă—305 54.1 KB

(Source: Wikipedia)

The blue countries use a period as the radix point, the green countries use a comma as the radix point, and the red ones use a not-quite-a-comma (U+066B).

4

Yes, the database sees long and lat as two separate values, therefore I can’t use a single parameter in a prepared statement, so the query needed 3 placeholders in total (including ID) to prepare, then an array of the 3 values to execute.