Re: Variables inside PostgreSQL/PostGIS query in PHP file

On 2014-04-10 08:30, Darko J wrote:
> 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.
>
>

I think your problem lies in the PHP. You might want to escape the inner
quotation marks. I'm guessing that your query is getting truncated to
"select ST_AsText (the_geom) as location, name, intptlat, intptlon FROM
tiger_match WHERE ST_INTERSECTS (the_geom,
ST_Buffer((ST_GeomFromText('POINT(".

>
>
> --
> 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.