I have a web mapping application built on Bootleaf (Leaflet + Bootstrap). Using the leaflet Draw plugin the user can create a polygon on the map. I'd like to insert that polygon into a geometry field to an SQL Server 2012 using PHP. I have a field called Boundary with spatial type Geography in a table called tblProjects.
There are hard coded values here just to get things working. They'll come from a jQuery post once I have things on the server side ironed out.
<?php
include_once("connection.php");
$connectionInfo = array("Database"=>"$dbname", "UID"=>$user, "PWD"=>$pass);
$DBH = sqlsrv_connect($server, $connectionInfo);
if( !$DBH ) {
echo "Connection could not be established.<br />";
if( ($errors = sqlsrv_errors() ) != null) {
foreach( $errors as $error ) {
echo "SQLSTATE: ".$error[ 'SQLSTATE']."<br />";
echo "code: ".$error[ 'code']."<br />";
echo "message: ".$error[ 'message']."<br />";
}
}
}
$sql = "INSERT INTO tblProjects (ProjectID, Boundary) VALUES (1, geography::STGeomFromText( "POLYGON (-93.30388806760311 27.994401411046173, -94.62224744260311 33.37641235124676, -79.70281384885311 31.80289258670676, -93.30388806760311 27.994401411046173)",4326));";
$stmt = sqlsrv_query($DBH, $sql);
if( $stmt === false ) {
die( print_r( sqlsrv_errors(), true));
} else {
echo "SUCCESS!";
}
It doesn't like my geography type, and I'm not sure how to include it; here's the PHP error:
Best Answer
first , you don't have geometry class in php (i don't know how to get one).
Second georaphy is not geometry type in database , those two types are different. WKT for POLYGON seems to be wrong, should be like 'POLYGON ((35 10, 45 45, 15 40, 10 20, 35 10)) (thats how i got my test case working)
$params = 'geometry::STGeomFromText('POLYGON (-93.30388806760311 27.994401411046173 ....')' might work , im not familiar with php but i think you need to escape that whole value. INSERT clause should be
INSERT INTO tblProjects (ProjectID, Boundary) VALUES ('123' , geography::ST_GeomFromtext('POLYGON ((35 10, 45 45, 15 40, 10 20, 35 10))', 4326)
I had problems with '-' sign when i tested code so you might need to use only positive values.