Find all spatial data columns in MySQL database

Spatial in MySQL are columns with the following data types: geometry, point,linestring, polygon, multipoint, multilinestring, multipolygon, geometrycollection

Note

If you are using MySQL 8 use this query: MySQL 8 query

Universal

The query below lists all columns with spatial data types.

Query

select col.table_schema as database_name,
       col.table_name,
       col.ordinal_position as column_id,
       col.column_name,
       col.data_type,
       col.is_nullable
from information_schema.columns col
join information_schema.tables tab
     on col.table_schema = tab.table_schema
     and col.table_name = tab.table_name
     and table_type = 'BASE TABLE'
where col.data_type in ('geometry', 'point', 'linestring', 'polygon',
                        'multipoint', 'multilinestring', 'multipolygon',
                        'geometrycollection')
      and col.table_schema not in ('information_schema', 'sys',
                                   'performance_schema', 'mysql')
     -- and table_schema = 'database_name' -- put your database name here
order by col.table_schema,
         col.table_name;

Columns

  • database_name - name of the database (schema)
  • table_name - name of the table
  • column_id - column position in table
  • column_name - name of the column
  • data_type - type of spatial data:
    • GEOMETRY
    • POINT
    • LINESTRING
    • POLYGON
    • MULTIPOINT
    • MULTILINESTRING
    • MULTIPOLYGON
    • GEOMETRYCOLLECTION
  • is_nullable - indicate whether column can contains null values

Rows

  • One row represents one column with a spatial data type
  • Scope of rows: all columns containing spatial data types in the database (schema)
  • Ordered by database name and table name

Sample results

MySQL 8

In MySQL 8 you can use other system view which provides information about columns with spatial types: st_geometry_columns.

Query

select table_schema as database_name,
       table_name,
       column_name,
       geometry_type_name as type,
       srs_name,
       srs_id
from information_schema.st_geometry_columns col
-- where table_schema = 'database_name' -- put database name here
order by table_schema,
         table_name;

Columns

  • database_name - name of the database (schema)
  • table_name - name of the table
  • column_name - name of the column
  • type - type of spatial data:
    • GEOMETRY
    • POINT
    • LINESTRING
    • POLYGON
    • MULTIPOINT
    • MULTILINESTRING
    • MULTIPOLYGON
    • GEOMETRYCOLLECTION
  • srs_name - name of the used spatial reference system
  • srs_id - id of the used spatial reference system

Rows

  • One row represents one column with a spatial data type
  • Scope of rows: all columns containing spatial data types in the database (schema)
  • Ordered by database name and table name

Sample results