CODEWITHSUNDEEP
phpmysqlapispatialspatial-query
Ryuta
Ryuta

Reputation: 31

How to insert spatial data in MySQL with PHP?

I want to save my coordinate from my Android application to a MySQL database. For this I created an API in PHP, but my code is not working.

Here is my PHP code:

<?php
  include_once 'db.php';

  $nop = $_POST['nop'];
  $plot_bng = $_POST['plot_bng'];

  $result = mysqli_query($con, "INSERT INTO sp_house (geom, d_nop)
                                VALUES (STGeomFromText('POINT($plot_bng)'), '$nop')");

  echo json_encode(array("value"=>1));

  mysqli_close($con);
?>

When I try an INSERT query in phpMyadmin, the data is successfully stored in the database.

Upvotes: 1

Views: 2856

Answers (2)

squancy
squancy

Reputation: 565

Check the right property for the columns

First of all, make sure you have created the right spatial columns in the database by using the GEOMETRY keyword.

CREATE TABLE sp_house (geom GEOMETRY, d_nop VARCHAR(255));

Insert data into the database with authentication

After you created the columns with the right property you can insert the data into your database. However, your code is widely open to SQL Injection and other kind of database hackings since you insert data directly without any kind of authentication. In order to avoid it, use prepared statements and the mysqli_real_escape_string function. Also, check that you have the right syntax for the query and replace STGeomFromText to ST_GeomFromText.

<?php
  include_once 'db.php';

  $nop = $_POST['nop'];
  $plot_bng = $_POST['plot_bng'];

  // You can also check that the variables are empty or not ...

  // Clean the variables and prepare for inserting
  $plot_bng = mysqli_real_escape_string($con, $plot_bng);
  $nop = mysqli_real_escape_string($con, $nop);
  $sql = "INSERT INTO sp_house (geom, d_nop)
                                VALUES (ST_GeomFromText(POINT(?)), ?)";

  // Prepared statement for inserting
  $stmt = $conn->prepare($sql); // prepare statement for inserting
  $stmt->bind_param("ss",$plot_bng,$nop); // replace question marks with values
  $stmt->execute(); // execute command
  $stmt->close(); // close connection

  echo json_encode(array("value"=>1));

  mysqli_close($con);
?>

Reference and further reading

Creating Spatial Columns in MySQL
Populating Spatial Columns
How to avoid SQL Injection?
How to use prepared statements?

Upvotes: 2

FixFaier
FixFaier

Reputation: 237

You're not writing your var correctly, try like this

$result = mysqli_query($con, "INSERT INTO sp_house (geom, d_nop)
VALUES (STGeomFromText('POINT(".$plot_bng.")'), '".$nop."')");

As you writing it, the vars are just normal text...

Upvotes: 0

Related Questions