2

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?

7

how about

select * from district.airport  where geom is NULL;
  • 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 '13 at 7:44
  • you're right - that will teach me to type sql with out testing - I've updated with working code – Ian Turton Jul 9 '13 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 '13 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 '13 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 '13 at 11:38
1

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 <> ''
  • Thats a thought.... – RickyA Jul 9 '13 at 8:05
1

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;

1

SELECT * FROM foo WHERE ST_IsEmpty(geom);

There has to be an empty geometry like MULTIPOLYGON EMPTY, otherwise this will return nothing like in your case.

See the documentation for more details at https://postgis.net/docs/ST_IsEmpty.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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