dblink experiences? multiple geom copies in multiple databases

[John Smith-54]

postgresql doesn't support queries across multiple databases and
postgis doesn't support multiple schemas, so how'd you guys do it?
i've multiple projects needing same geom. do i create multiple geom
copies in each database for joins? guess not, but if so how'd i best
update that geom? any experience with dblink?

as i understand
http://www.nabble.com/Multiple-Schemas-with-PostGIS-tf3014581.html#a8394314,
you install postgis in one schema, (probably) put all geoms in that
schema and simply reference it from other databases? can someone show
a simple example. ascii art won't hurt ;)

"if the data is related enough to be joined, it must be related enough
to be in the same database" doesn't cut it.
jzs
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Paul Ramsey]

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


_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[John Smith-54]

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@...> 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@...> 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

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Paul Ramsey]

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@...> 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@...> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users@...
> http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[John Smith-54]

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@...> 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@...> 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@...> 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

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Paul Ramsey]

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@...> 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@...> 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@...> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users@...
> http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[John Smith-54]

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@...> 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@...> 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@...> 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@...> 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

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users