Variables inside PostgreSQL/PostGIS query in PHP file

Question

I'm trying to use variables inside PostgreSQL/PostGIS query in PHP file.

Variables need to put lat and lng values inside POINT geometry, but something does not work well inside my code and I receive error message:

Warning: pg_query(): Query failed: ERROR: parse error - invalid geometry HINT: "POINT(" <-- parse error at position 6 within geometry

When I run query that have values (instead of variables) for the POINT

SELECT ST_AsText (the_geom) as location, name, intptlat, intptlon FROM tiger_match WHERE ST_INTERSECTS (the_geom, ST_Buffer((ST_GeomFromText('POINT(-85.1043 34.315)',4326)), 0.1));

everything works fine.

I read some articles and post on the net but I can not find solution:

• http://www.techrepublic.com/blog/how-do-i/how-do-i-use-php-with-postgresql/#.

• Whats does pg_escape_string exactly do?

Entire code that I use in php file is below:

<?php
$host = "localhost"; 
$user = "postgres"; 
$pass = "2907"; 
$db = "postgis";

$intptlon = pg_escape_string($_GET['intptlon']);
$intptlat = pg_escape_string($_GET['intptlat']);

function parseToXML($htmlStr) 
{ 
$xmlStr=str_replace('<','&lt;',$htmlStr); 
$xmlStr=str_replace('>','&gt;',$xmlStr); 
$xmlStr=str_replace('"','&quot;',$xmlStr); 
$xmlStr=str_replace("'",'&#39;',$xmlStr); 
$xmlStr=str_replace("&",'&amp;',$xmlStr);
return $xmlStr; 
} 

// Opens a connection to a PostgreSQL
$con = pg_connect("host=$host dbname=$db user=$user password=$pass") or die ("Could not connect to server\n"); 

$query = "select ST_AsText (the_geom) as location, name, intptlat, intptlon FROM tiger_match WHERE ST_INTERSECTS (the_geom, ST_Buffer((ST_GeomFromText('POINT('$intptlon' '$intptlat')',4326)), 0.1))";
$rs = pg_query($con, $query) or die("Cannot execute query: $query\n");

header("Content-type: text/xml");
// Start XML file, echo parent node
echo '<markers>';

// Iterate through the rows, printing XML nodes for each
while ($row = @pg_fetch_assoc($rs)){
  // ADD TO XML DOCUMENT NODE
  echo '<marker ';
  echo 'name="' . parseToXML($row['name']) . '" ';
  echo 'intptlat="' . $row['intptlat'] . '" ';
  echo 'intptlon="' . $row['intptlon'] . '" ';
  echo 'the_geom="' . $row['location'] . '" ';
  echo '/>';
}
echo '</markers>';
?>

Answer 1

Don't mix parameters for WKT with SQL. Either create the WKT (T for text) using conventional string utilities, then feed that string parameter to the query, or use an alternative geometry constructor that takes numeric parameters, such as ST_MakePoint(x, y).

The query should look something like this:

SELECT ST_AsText(the_geom) as location, name, intptlat, intptlon
FROM tiger_match
WHERE ST_DWithin(the_geom, ST_SetSRID(ST_MakePoint($intptlon, $intptlat), 4326), 0.1);

In general, don't buffer geometries to do proximity searches. In this case it was one point, but some folks also do this to whole tables, which is unnecessarily expensive. Also, your distance is 0.1 degree, which is a non-sense distance unit. If you use a geography type, then it will use metric distances.