Good morning,
I am seeking for a general advice if it would be a good idea for a PostgreSQL/PostGIS using application to add a third schema.
I am using postgis/postgis Docker image which comes with "public" schema.
Then I have loaded OpenStreetMap data into the database and have created "osm_schema" for that:
# psql -p 5432 -U postgres
CREATE DATABASE osm_database TEMPLATE=template_postgis;
CREATE USER osm_username WITH ENCRYPTED PASSWORD 'osm_password';
GRANT ALL PRIVILEGES ON DATABASE osm_database TO osm_username; -- TODO read only
# psql -p 5432 -U osm_username osm_database
CREATE SCHEMA osm_schema AUTHORIZATION osm_username;
ALTER ROLE osm_username SET search_path TO osm_schema, public;
After loading OSM data with the osm2pgsql tool I have 3 tables in there:
# psql -p 5432 -U osm_username osm_database
psql (15.2)
osm_database=> \dt
List of relations
Schema | Name | Type | Owner
------------+--------------------+-------+--------------
osm_schema | planet_osm_line | table | osm_username
osm_schema | planet_osm_point | table | osm_username
osm_schema | planet_osm_polygon | table | osm_username
osm_schema | planet_osm_roads | table | osm_username
public | spatial_ref_sys | table | postgres
(5 rows)
And now I need one more table, for the new mapping feature in my application, which would reference the osm_id column in the planet_osm_roads.
I have not used database schemas at all until yet, I am looking for an advice if I should add a third schema here, just for my application?
Would it be a good long term idea and what advantages/disadvantages would that bring with it?
Thank you for any feedback and also I have to note, that I am really amazed by all these software products -
PostgreSQL, PostGIS and also the OpenStreetMap - how smooth the installation has worked for me - just an hour spent and I have the whole planet at my fingertips - this is not coming for free, there are probably decades of effort by many people in the products.
Best regards
Alex