Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.
Sign up to join this community
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?
how about
select * from district.airport where geom is NULL;
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.
= sign but does work with an is?
<> '' works. Maybe because PG treads empty char as NULL. Also this IS vs. = in sql was the real problem here.
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
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;
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 <> ''
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');
