SQL Server STpointfromText comes from PHP into javascript as garbage

Question

Sorry for the sloppy title. Here's the scenario - I have a table in SQL Server 2008 R2 It has a geographyColumn, created thusly:

add geographyColumn as geography::STPointFromText('POINT('+convert(varchar(20),x_coord)+' 
'+convert(varchar(20),y_coord)+')',4326)

When I viewed it in the table, it looks fine:

POINT (-116.477654 33.764695)

I have some javascript, and a PHP script, to grab points from said table. I can get any text field without a problem. But I want to get the X,Y vales from the geography column. Instead of a nicely formed "X,Y" or even a long string of numbers, I get this mess:

����zU���f�C@

My php is the following:

<?php $q=$_GET["q"]; 
$serverName = "ServerIP"; 
$connectionInfo = array( "Database"=>"geo_spat2", "UID"=>"u", "PWD"=>"p"); 
$con = sqlsrv_connect($serverName, $connectionInfo); 
if (!$con){ 
 die( print_r( sqlsrv_errors(), true)); 
}; 
$sql="SELECT * FROM EMPTY_REPS WHERE Division_label = '".$q."'"; 
$result = sqlsrv_query($con, $sql); 
while($row = sqlsrv_fetch_array($result)) { 
 echo $row['geographyColumn'] . ", "; 
} 
sqlsrv_close($con); 
?>

Am I missing something simple? Thanks, Evan

• L_Holcombe: The problem could be in your PHP, or in your JavaScript.
• mapexpert: The PHP is pretty simple: <?php $q=$_GET["q"]; $serverName = "ServerIP"; $connectionInfo = array( "Database"=>"geo_spat2", "UID"=>"u", "PWD"=>"p"); $con = sqlsrv_connect($serverName, $connectionInfo); if (!$con){ die( print_r( sqlsrv_errors(), true)); }; $sql="SELECT * FROM EMPTY_REPS WHERE Division_label = '".$q."'"; $result = sqlsrv_query($con, $sql); while($row = sqlsrv_fetch_array($result)) { echo $row['geographyColumn'] . ", "; } sqlsrv_close($con); ?>
• L_Holcombe: Your PHP is simply selecting the geography column from the row. In PostGIS, the geometry column does not store WKT text - it stores a hex hash of the WKB representation. SQLServer probably does the same thing, but displays the WKT when you view the table.
• mapexpert: OK, how do I get the actual record out of it/\
• mapexpert: Ok, how do I get the actual records? echo $row['rep_no'] . ", "; echo $row['NAME'] . ", "; both work fine, I get the actual record. what am I missing? Thanks.

Answer 1

I would suggest adding another varchar column for WKT and returning that column in your PHP. You can do an update statement and use STAsText() to set the WKT column.

UPDATE myTable
SET WKT = geographyColumn.STAsText();

• simpleuser001: Or just add 'SELECT * , geographyColumn.STAsText() as WKT FROM ...' , WKB geography can also be quite big i some cases so best idea is use 'SELECT c1, c3, geographyColumn.STAsText() as WKT FROM ...' Or if you still want to use 'SELECT * ...' create view

Answer 2

You can access the coordinate data using STX and STY:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POINT(-116.477654 33.764695)', 4326);
SELECT @g.STY AS Latitude, @g.STX AS Longitude;