3

I need to figure out if a particular SRID is used by any geometry in a database having plenty of different schemas, each of them having also plenty of tables, some having more than one geometry field.

How could I figure out the SRID of all the geometries (including the geometry field name, the table and the schema it belongs to) which are hold in a PostGIS database (pg:15 postgis:3.3)?

This is basically listing all columns having a USER-DEFINED data types, which in a PostGIS enabled database correspond to the geometry columns, but in case one has other USER-DEFINED data types, this won't perfectly work:

SELECT
  table_schema,
  table_name,
  column_name,
  data_type
FROM information_schema.columns
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
AND data_type = 'USER-DEFINED'
ORDER BY table_schema, table_name;

1 Answer 1

4

That's exactly what the public.geometry_columns & public.geography_columns Views are for:

SELECT
  *
FROM
  public.geometry_columns
-- WHERE
  -- srid = <srid>
;

Your Answer

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

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