[postgis-users] dblink experiences? multiple geom copies in multiple databases

[John Smith]

thanks paul.

can someone throw in some pointers/life-experiences- dos and donts,
schema tips (group by project or geo feature?) etc for the kick-ass
*spatial* database mentioned above.
http://www.postgresql.org/docs/8.2/static/ddl-schemas.html
http://www.postgresql.org/docs/8.2/static/sql-createschema.html
jzs

On 8/13/07, Paul Ramsey <pramsey at refractions.net> wrote:
> It just makes creating new databases with spatial enabled possible
> from the SQL command line:
>
> CREATE DATABASE newdb WITH TEMPLATE=template_postgis;
>
> P
>
> On 13-Aug-07, at 11:59 AM, John Smith wrote:
>
> > interesting.
> >
> > i also see a postgis template database. does that only help to
> > spatially enable new databases (likely) or also help spatial queries
> > from other databases (not likely, but if so, how is this
> > inter-database querying happening)?
> > jzs
> >
> > On 8/13/07, Paul Ramsey <pramsey at refractions.net> wrote:
> >> Correct, that's how most do it. dblink is an option, but I have no
> >> experience to share on that. Just from a straight design PoV though,
> >> the complete opacity of a dblink table in terms of size, selectivity,
> >> indexes, etc, means that you'll have to be pretty careful about what
> >> use cases you try and push through that pipe -- it's not a
> >> transparent gateway, it's more of a straw.
> >>
> >> P
> >>
> >> On 13-Aug-07, at 7:02 AM, John Smith wrote:
> >>
> >>> given this:
> >>> - cluster
> >>> -- database
> >>> --- schema (except public schema can be accessed from outside)
> >>> ---- table, function etc
> >>> it means (and i didn't want to accept this conclusion) most postgis
> >>> shops have 1 kick-ass database for all their geom and related non-
> >>> geom
> >>> tables separated by named schemas. users, permissions etc are all
> >>> restricted by these named schemas and *all* geom tables are
> >>> contained
> >>> in this monster database (to avoid duplication).
> >>>
> >>> well that'll certainly ease administration :).
> >>>
> >>> On 8/11/07, Paul Ramsey <pramsey at refractions.net> wrote:
> >>>> In what respect do you find that postgis doesn't support multiple
> >>>> schemas?  We generally install postgis in 'public.' and create lots
> >>> http://postgis.refractions.net/pipermail/postgis-users/2006-January/
> >>> 010602.html
> >>> jzs
> >>>
> >>> On 8/11/07, Paul Ramsey <pramsey at refractions.net> wrote:
> >>>>
> >>>> On 10-Aug-07, at 2:05 PM, John Smith wrote:
> >>>>
> >>>>> postgresql doesn't support queries across multiple databases and
> >>>>> postgis doesn't support multiple schemas, so how'd you guys do
> >>>>> it?\\
> >>>>
> >>>> In what respect do you find that postgis doesn't support multiple
> >>>> schemas?  We generally install postgis in 'public.' and create lots
> >>>> of other schemas ('foo.', 'water.', 'etc.') for our data tables.
> >>>>
> >>>> P