17

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?

Tanner
  • 7,375
  • 14
  • 62
  • 109

2 Answers2

26

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)

JJD
  • 1,511
  • 1
  • 17
  • 30
atlefren
  • 4,307
  • 20
  • 35
  • The sql in the answer will return geometry for the type, while we can still not know if the geometry is point or line. – giser Sep 12 '18 at 04:26
  • 1
    That means that your geometry column is set to geometry type "geometry" (i.e. it accepts all kinds of geometries). In this case you'll have to check each geometry induvidually using ST_GeometryType – atlefren Sep 13 '18 at 11:06
  • SELECT GeometryType(geom) FROM my_schema.building LIMIT 1 like this as the OP post? It does not work if the table is empty. – giser Sep 15 '18 at 01:45
  • And one more thing, if all the geometry column is set to geometry type for all the table, can I create a view contains all the tables which may contain points lines polyogn? – giser Sep 15 '18 at 01:46
  • 2
    Right. An analogy: declaring that a geom column is of type "geometry" is like designating a drawer for socks of any color. Checking what color of socks is in this drawer is impossible when it is empty. – atlefren Sep 15 '18 at 09:12
  • Create view my_schema.building_point AS SELECT * FROM my_schema.building WHERE GeometryType(geom) = 'Point' ; etc – atlefren Sep 15 '18 at 09:14
  • I may not make me clear enough, when I say I want to create view I do no mean for only one table. In my application I have to query almost 10 tables which contain point line and polyon, so I wonder if I can create a view contains all the tables together. – giser Sep 15 '18 at 12:02
  • Start a new question – atlefren Sep 16 '18 at 14:34
3

Look for a table geometry_columns. All geo columns with srid and geometry type are registered here. If there is a column with type GEOMETRY there all geometry types can be stored.

wumpz
  • 266
  • 2
  • 4