From: | Brent Dombrowski <brent(at)bdombrowski(dot)us> |
---|---|
To: | Darko J <darkojaramaz(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org, pgsql-novice-owner(at)postgresql(dot)org |
Subject: | Re: Variables inside PostgreSQL/PostGIS query in PHP file |
Date: | 2014-04-11 14:29:07 |
Message-ID: | 82feb9f265c6885046d7ea1a5c8b126c@server.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | James David Smith | 2014-04-14 08:46:41 | Don't understand error? |
Previous Message | Herr, Christian | 2014-04-11 14:02:06 | Re: postmaster.pid |