7

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?

5 Answers 5

8

how about

select * from district.airport  where geom is NULL;
5
  • 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, 2013 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, 2013 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, 2013 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, 2013 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, 2013 at 11:38
4

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

3

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;

2

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

I have thought about listing tables without geometry which should also be helpful:

select tablename from pg_tables where schemaname='yourschemaname'
    except 
    (select f_table_name from geometry_columns where f_table_schema = 'yourschemaname');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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