Tell me more ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 1 down vote favorite

I try to select rows in Postgis that have an empty geometry column.

This works but gives me the opposite:

SELECT * FROM foo where geom <> '';

And this fails:

SELECT * FROM foo where geom = '';

>>>ERROR: parse error - invalid geometry
>>>SQL state: XX000
>>>Hint: You must specify a valid OGC WKT geometry type such as POINT, LINESTRING or POLYGON

And this returns nothing:

SELECT * FROM foo where ST_IsEmpty(geom);

Does anyone know the correct syntax?

share|improve this question
add comment (requires an account with 50 reputation)

3 Answers

up vote 5 down vote accepted

how about 

select * from district.airport  where geom is NULL;
share|improve this answer
That gives me ERROR: function isnull(geometry) does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You might need to add explicit type casts. – RickyA Jul 9 at 7:44
you're right - that will teach me to type sql with out testing - I've updated with working code – iant♦ Jul 9 at 7:52
Your update works. Why oh why doesn't it work with an = sign but does work with an is? – RickyA Jul 9 at 8:03
1  
@RickyA Because NULL values and empty strings are different things. you can check a quick explanation here if you're interested! linuxtopia.org/online_books/database_guides/… – nickves Jul 9 at 11:02
@nickves Interesting, but still weird that <> '' works. Maybe because PG treads empty char as NULL. Also this IS vs. = in sql was the real problem here. – RickyA Jul 9 at 11:38
add comment (requires an account with 50 reputation)
up vote 1 down vote

If this works for you, but gives the opposite...

SELECT * FROM foo WHERE geom <> '';

Just put a NOT in the statment:

SELECT * FROM foo WHERE NOT geom <> ''
share|improve this answer
Thats a thought.... – RickyA Jul 9 at 8:05
add comment (requires an account with 50 reputation)
up vote 1 down vote

You can try this :

select id, geom, st_isvalid(geom) from foo where st_isvalid(geom)='false' ;

You must check that your entities are valid, (if not : repaire geometry). Then the 'Iant' answer is correct :

select * from district.airport where geom is NULL;

share|improve this answer
add comment (requires an account with 50 reputation)

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.