[GIS] How to insert a geometry as WKT to SQL Server Spatial with PHP

bootstrap-frameworkPHPsqlsql server

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:
enter image description here

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.

Related Question