CODEWITHSUNDEEP
phpsqlpostgresql
briane
briane

Reputation: 1

Return boolean type in PostgreSQL

I am using ST_Intersects() to know whether two geometries intersect or not. This is my code:

$var_name = pg_query($conn,"SELECT ST_Intersects(p1.column_name, 
p2.column_name)
FROM table_name AS p1, table_name AS p2
WHERE p1.id < p2.id");
if(pg_fetch_assoc($var_name) == 'false')
{
echo "accepted";
}
else
{
echo "rejected";
}

I know that the geometries are not intersecting and when I run the same query in Postgres the result is false as expected.

But here it is executing the else statement. Where am I wrong?

Upvotes: 0

Views: 536

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656616

The same query can return a different result every time you call it, since it's not deterministic. Any number of rows can qualify in each table, producing a Cartesian product of results. And since there is not unambiguous ORDER BY in your query, an arbitrary row will be the first to be returned.

Either add an ORDER BY (plus LIMIT 1) or more WHERE conditions to return a single row (deterministically).

Alternative query:

SELECT EXISTS (
   SELECT 1
   FROM   table_name p1
   JOIN   table_name p2 ON p1.id < p2.id
   WHERE  ST_Intersects(p1.column_name, p2.column_name)
   );

This returns exactly 1 row with true or false (never null, and never 0 or multiple rows). It's true if at least one combination intersects.

But also look to Richard's answer for the PHP part of the test.

Upvotes: 0

Richard Huxton
Richard Huxton

Reputation: 22893

Erwin's advice is (as always) good, but in your particular case I think the problem is in your test.

if(pg_fetch_assoc($var_name) == 'false')

pg_fetch_assoc returns an associative array which you are comparing to a single string. Comparing mixed types is a complicated process in php, but in this case it knows that a string is not an array and so they aren't equal.

You probably want something like the following:

$row = pg_fetch_assoc($var_name) if ($row['ST_Intersects'] == false)

Don't guess though, do something like print_r($row) to confirm you have the column-names and values you think you do.

Upvotes: 1

Related Questions