[GIS] Column ‘SHAPE’ cannot be null in thesql database even if the query string is correct

mysql-spatialPHPsql

I am trying to write an POLYGON into an column named SHAPE which is a geometry type. And created query which looks 

$sql = "INSERT INTO rokka(zone,district,location,area,SHAPE) 
VALUES ('$zone','$district','$location','$area',PolygonFromText('$polywrite'))";

The variable $polywrite comes as

var polygon = 'Polygon((' + northeastlngLat[0] + ' ' + northeastlngLat[1] + ',' + northwestlngLat[0] + ' ' + northwestlngLat[1] + ',' + southwestlngLat[0] + ' ' + southwestlngLat[1] + ',' + southeastlngLat[0] + ' ' + southeastlngLat[1] + ',' + northeastlngLat[0] + ' ' + northeastlngLat[1] + '))';
$polywrite = "<script>document.write(polygon)</script>";

The arrays contain the coordinates.
And when executing 

if ($connect->query($sql) === TRUE) {
    session_start();
    header('location: admin.html');
    session_destroy();
} else {
        echo "Error: " . $sql . "<br>" . $connect->error;
    }

The error it shows is 

Column 'SHAPE' cannot be nullError: INSERT INTO rokka(zone, district,location,area,SHAPE) VALUES ('Rapti', 'Rolpa','Liwang','603750', PolygonFromText('Polygon((85.06290725100276 28.00935569163021,85.06392404943365 28.0093478254886,85.06393735099435 28.01070145242192,85.06292053985693 28.010709319009717,85.06290725100276 28.00935569163021))'))
Column 'SHAPE' cannot be null

But whenever I run the query 

INSERT INTO rokka(zone, district,location,area,SHAPE)
VALUES ('Rapti', 'Rolpa','Liwang','603750', PolygonFromText('Polygon((85.06290725100276 28.00935569163021,85.06392404943365 28.0093478254886,85.06393735099435 28.01070145242192,85.06292053985693 28.010709319009717,85.06290725100276 28.00935569163021))'))

in my database directly It works exactly as it should. All the values are written into the respective columns. Please help me figure out what i am doing wrong.

Best Answer

It seems you try to persist your entity and its id remains null. That's why your get a constraint violation error.

  • To simply solve this error I suggest you to try create your database table using auto-incrementation option on your SHAPE field. It should solve your problem, I guess.

    OR

  • Reading the documentation on LAST_INSERT_ID() I would suggest that the value is only updated after the last trigger runs. I also created a trigger which inserts the result of LAST_INSERT_ID() into another table and it would always insert the id of the row inserted by the INSERT statement before or 0 if there was no previous INSERT.

From within an insert or update trigger you can always refer to the state after the statement by using NEW.column where column is a column-name of your table. See the documentation for examples

Related Question