Thread: PostGIS Schema

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    4
    Rep Power
    0

    PostGIS Schema


    Hi,

    I have created a database in postgresql that has been created using a postgis template. The public schema in the database has geometry and geography tables and loads of other functions. I created a new schema because i would like to add my data in the new schema and not the public one. The new schema does not have the same table and functions like the public schema.

    What is the best way to deal with this problem?

    Thanks
    Susan
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    11
    Create a database from a blank template (like template1) and run the extension creation command yourself:
    Code:
    CREATE EXTENSION postgis;
    But this will install at least a bunch of functions into public (I don't remember if tables always wind up there or not).

    To put it somewhere else you can specify a schema:
    Code:
    CREATE EXTENSION postgis SCHEMA my_schema;
    It is important to note that complex extensions like postgis are often used by outside projects/other extensions that you might want to include later. Some of these other extensions may not be written in a way that takes into account non-standard postgis extension installation locations/schemas -- so beware of that.

    If you already have existing data you need to copy over, you could simply rename the public schema:
    Code:
    ALTER SCHEMA public RENAME TO my_schema;
    But beware that a great many assumptions made by other tools (like pg_admin) are broken when you do this because they assume the presence of public at the base of the search path. There also may be existing tables, sequences, views, functions, etc. you don't want moved, and separating them out from the PostGIS stuff might be a pain.

    Anyway, there are plenty of ways to get what you want, which way is right depends on your situation.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    4
    Rep Power
    0
    Hi zxq9,

    Thanks for your response.
    I have all the postgis functions in public and the spatial reference table in public.
    I would like to have the public schema the way it is and not rename it, for similar reasons you have mentioned.
    I was wondering if i could have all the functions and the spatial reference table (like the public schema has) in the same database but in multiple schemas?
    i have created a schema called 'Test' in the spatially enabled (postgis) database and i uploaded a shapefile into it. Now when i connect this 'test' schema to Geoserver, i cannot see the shapefile when i click 'open layers' to preview the layer.
    I can however view the shapefile in Geoserver if i upload it to 'public' schema (which is in the same database as my test schema). As i mentioned earlier i suspect this is because the spatiall reference table is missing in the test schema. I have no idea how to include the spatial reference table into several schemas.
    I want to arrange my data into different schemas and not cluttering the public schema.
    What is the best way to deal with this issue.

    Thanks
    Susan
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    4
    Rep Power
    0

    Update: problem is solved


    Hi,

    Miraculously, today i could see the map preview in geoserver. I did'nt do anything different.
    The new schema (not public) is connected to geoserver.
    maybe the problem was to do with server connections in my department...or whatever....??
    i have no clue why i could not see the map preview all day yesterday.
    My problem is solved !!

    Thanks
    Susan
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    11
    You might have simply had a connectivity problem, but without knowing the details I'm not able to say, of course.

    But using PostGIS functionality from an adjacent schema in the same database shouldn't be a problem at all -- PostGIS is an extension, after all, and extensions in public are (unless you change permissions) usable by other roles and schemas. I don't think you'll have any further problems, but post back if you do. I'm curious.

    I can sympathize with not wanting to clutter public -- the main project I'm working on now involves over 1200 db objects (and still growing slightly). It would be a nightmare to prevent namespace clashes if they weren't divvied up among logical schema layouts!

IMN logo majestic logo threadwatch logo seochat tools logo