1

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

2

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.

  • So without a geometry class (which I don't know how to include) it's impossible to insert any spatial data to SQL Server. Perhaps I should migrate this to StackOverflow? – Roy Jan 16 '14 at 14:16
  • You don't need a geometry class in php in this case. You just need to close the quotes around 'geometry::STGeomFromText( coords )' and let sql convert that to geometry objects in the database. – DPierce Jan 16 '14 at 14:21
  • no. Your code just tries to convert that data in to geometry class in php, which you do not have. You need to escape it properly so that whole geography::STGeomFromText... goes into SQL server where it is handled. – simplexio Jan 16 '14 at 14:23
  • Thanks, not sure what I was thinking; you've set me on the correct path. I'll work in the SQL console to get the query working there then double back to get it working with PHP. I'll report back. – Roy Jan 16 '14 at 14:25
  • Were the errors you encountered similar to this: The specified input does not represent a valid geography instance because it exceeds a single hemisphere? – Roy Jan 16 '14 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.