From: | Darko J <darkojaramaz(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Variables inside PostgreSQL/PostGIS query in PHP file |
Date: | 2014-04-10 14:30:47 |
Message-ID: | 1397140247638-5799542.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
I'm trying to migrate Google Maps Store Locator Example from MySQL to
PostgreSQL/PostGIS, and also to display polygons that are in certain radius
base on lat/lng (I using ST_Buffer function for this) at my map.
I guess that I have a problem with a PostgreSQL/PostGIS query variables for
the POINT (that are inside my PHP file), but I cannot determine exact what
is a error inside my code (I am new with PostgreSQL).
I think this because when I run query that have values 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 and polygons appear on my map.
And code that I use inside php is:
<?php
$host = "localhost";
$user = "postgres";
$pass = "2907";
$db = "postgis";
// Get parameters from URL
$intptlat = $_GET["intptlat"];
$intptlon = $_GET["intptlon"];
function parseToXML($htmlStr)
{
$xmlStr=str_replace('<','<',$htmlStr);
$xmlStr=str_replace('>','>',$xmlStr);
$xmlStr=str_replace('"','"',$xmlStr);
$xmlStr=str_replace("'",''',$xmlStr);
$xmlStr=str_replace("&",'&',$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))",
pg_escape_string($intptlat),pg_escape_string($intptlon));;
$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>';
?>
Any advice is welcome, thanks.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Variables-inside-PostgreSQL-PostGIS-query-in-PHP-file-tp5799542.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
From | Date | Subject | |
---|---|---|---|
Next Message | Herr, Christian | 2014-04-11 12:57:30 | postmaster.pid |
Previous Message | Tom Lane | 2014-04-09 22:00:13 | Re: Case insensitive unique constraint |