SQL Server STpointfromText comes from PHP into javascript as garbage

manpreet Best Answer 2 years ago

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

manpreet 2 years ago

UPDATE myTable
SET WKT = geographyColumn.STAsText();