Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

share|improve this question
    
The problem could be in your PHP, or in your JavaScript. –  L_Holcombe Dec 10 '12 at 19:55
    
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); ?> –  mapexpert Dec 10 '12 at 20:18
    
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. –  L_Holcombe Dec 10 '12 at 21:09
    
OK, how do I get the actual record out of it/\ –  mapexpert Dec 10 '12 at 21:12
    
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. –  mapexpert Dec 10 '12 at 21:13

2 Answers 2

up vote 2 down vote accepted

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();
share|improve this answer
    
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 –  simplexio Dec 11 '12 at 8:00

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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