CODEWITHSUNDEEP
sqlpostgresqlpostgis
nate777
nate777

Reputation: 174

PostgreSQL only returns result when adding additional AND clause

I have a query like this:

SELECT boroughs.name 
FROM boroughs, uniroads 
WHERE uniroads.normalizedName='6 AVENUE' 
AND st_intersects(boroughs.geometry, uniroads.way) 
AND boroughs.name='Brooklyn'

0 results

But when I run it, it returns no results. However, I'm able to find the specific row in the table I'd like it to return, and when I add a clause asking for that particular row, it works fine:

SELECT boroughs.name 
FROM boroughs, uniroads 
WHERE uniroads.normalizedName='6 AVENUE' 
AND st_intersects(boroughs.geometry, uniroads.way) 
AND boroughs.name='Brooklyn' 
AND uniroads.osm_id='23334071'

1 result

I'm using Postgres 9.2.2.0 with PostGIS through Postgres.app.

Upvotes: 3

Views: 89

Answers (1)

vyegorov
vyegorov

Reputation: 22895

A guess.

uniroads.osm_id looks like a Key, therefore it is most likely to be indexed.

AND uniroads.osm_id='23334071' clause is causing (another, perhaps?) index to be used, thus this might mean (some?) originally used indexes are corrupted.

Maybe the following might help? 

REINDEX TABLE boroughs;
REINDEX TABLE uniroads;

In any case, EXPLAIN ANALYZE wanted for both queries, as well as complete definitions of tables involved.

Upvotes: 1

Related Questions