[postgis-users] schemas and postgis data

[Szymon Guz]

On 22 September 2011 12:17, Robert Buckley <robertdbuckley at yahoo.com> wrote:

> I have just read this explaining about how to structure data and functions
> within postgresql
> http://blog.cleverelephant.ca/2010/09/postgis-back-up-restore.html
>
> ..."The "public" schema is where the PostGIS functions and system tables
> get installed, so if you dump that schema you get all those definitions in
> your dump. If those definitions are mixed in amongst your data, loading them
> into a fresh database gets tricky: are the paths to the libraries the same?
> are there function name clashes? (The utils/postgis_restore.pl script
> attempts manfully to strip out PostGIS components from a dump file to allow
> a clean restore, but it is hard to get 100% performance.)
> If, on the other hand, all your data is neatly separated into its own
> schema, you can neatly backup *just that schema* and avoid having PostGIS
> system information mixed in with your data. That means you can easily
> restore your data into *any version of PostGIS and PostgreSQL that you
> like*. So upgrades are easy easy easy.
>
> *Remember: Store your data in a schema other than "public".*
> "
>
>
> Basically Paul recommends saving geodata in a different schema to the
> postgis functions.
>
> My questions are...
> 1 "if the data is located in a different schema which does not have the
> 800 odd postgis functions, are the functions still available to the data?"
> 2 "are cross schema queries allowed?"
> 3  "does it also make sense to seperate non-spatial tables into their own
> schemas?"
>
>
> thanks for any advice,
>
> Rob
>
>
>
Hi Robert,
1) yes, functions are available as long as your search_path contains schema
public (which is standard setting)
2) yes, just use the tables with schemaname in the queries, so you can have
tables with the same name in all schemas, just call them using schemaA.A and
schemaB.A.
3) it depends, but of course you can separate the tables. There is no
difference between tables containing columns of PostGIS types and tables
without those.

regards
Szymon

-- 
*http://simononsoftware.com/* <http://simononsoftware.com/>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110922/c462e916/attachment.html>