PostGIS Geometry Type – How to Get the Geometry Type of an Empty PostGIS Table

postgis

I have a web application that needs to know the geometry type of a PostGIS table's geom field before inserting rows into it. I use the following query to determine the geometry type of the geom column:

SELECT GeometryType(geom) FROM my_schema.building LIMIT 1

This returns the geometry type of an actual row, so it does not work when my table is empty. How do I determine the geometry type of the geometry column itself?

Best Answer

The query could be run against the geometry_columns table in this way

SELECT type 
FROM geometry_columns 
WHERE f_table_schema = 'my_schema' 
AND f_table_name = 'building' 
and f_geometry_column = 'geom';

(or, if you are using a geography type, subsititute geometry_columns with geography_columns and f_geometry_column with f_geography_column)

Related Solutions

PostGIS – How to Populate the Geometry Column in a PostGIS Table?

Per comments, in occasions the geom column might not be visible to the client as a human readable string (a geom column is encoded as an EWTB - Extended Well Known Binary ).

There are two ways to visually check if the geom column is populated:

Load the table to your favourite gis client (eg. Qgis)
Count the NULL and NOT NULL values of the geom column :

      SELECT COUNT(geom), 'null_tally' AS narrative 
      FROM 'myTable' 
      WHERE a IS NULL 
      UNION
      SELECT COUNT(geom), 'not_null_tally' AS narrative 
      FROM 'myTable'  
      WHERE a IS NOT NULL;

count sql snippet from : https://stackoverflow.com/questions/1271810/counting-null-and-non-null-values-in-a-single-query )

Select Empty Geometry Columns in PostGIS

how about

select * from district.airport  where geom is NULL;

Best Answer

Related Solutions

PostGIS – How to Populate the Geometry Column in a PostGIS Table?

Select Empty Geometry Columns in PostGIS

Related Question