<br><br><div class="gmail_quote">On 22 September 2011 12:17, Robert Buckley <span dir="ltr"><<a href="mailto:robertdbuckley@yahoo.com">robertdbuckley@yahoo.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div><div style="color:#000;background-color:#fff;font-family:arial, helvetica, sans-serif;font-size:10pt"><div style="font-family:arial, helvetica, sans-serif;font-size:10pt">I have just read this explaining about how to structure data and functions within postgresql</div>
<div style="font-family:arial, helvetica, sans-serif;font-size:10pt"><a href="http://blog.cleverelephant.ca/2010/09/postgis-back-up-restore.html" target="_blank">http://blog.cleverelephant.ca/2010/09/postgis-back-up-restore.html</a><br>
</div><div style="font-family:arial, helvetica, sans-serif;font-size:10pt"><br></div><div style="font-family:arial, helvetica, sans-serif;font-size:10pt"><span style="white-space:pre-wrap"> </span>..."<span style="color:rgb(48, 48, 48);font-family:Verdana, Tahoma, Arial, sans-serif;font-size:12px;line-height:17px">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/<a href="http://postgis_restore.pl" target="_blank">postgis_restore.pl</a> 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.)</span></div>
<div style="font-family:arial, helvetica, sans-serif;font-size:10pt"><span style="color:rgb(48, 48, 48);font-family:Verdana, Tahoma, Arial, sans-serif;font-size:12px;line-height:17px"><span style="white-space:pre-wrap"> </span></span><span style="color:rgb(48, 48, 48);font-family:Verdana, Tahoma, Arial, sans-serif;font-size:12px;line-height:17px">If, on the other hand, all your data is neatly separated into its own schema, you can
neatly backup <em style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px">just that schema</em> and avoid having PostGIS system information mixed in with your data. That means you can easily restore your data into <em style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px">any version of PostGIS and PostgreSQL that you like</em>. So upgrades are easy easy easy.</span></div>
<span style="color:rgb(48, 48, 48);font-family:Verdana, Tahoma, Arial, sans-serif;font-size:12px;line-height:17px"><br style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px">
<strong style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px">Remember: Store your data in a schema other than "public".</strong></span><div style="font-family:arial, helvetica, sans-serif;font-size:10pt">
<span style="color:rgb(48, 48, 48);font-family:Verdana, Tahoma, Arial, sans-serif;font-size:12px;line-height:17px">"</span></div><div style="font-family:arial, helvetica, sans-serif;font-size:10pt"><br></div><div style="font-family:arial, helvetica, sans-serif;font-size:10pt">
<br></div><div style="font-family:arial, helvetica, sans-serif;font-size:10pt">Basically Paul recommends saving geodata in a different schema to the postgis functions. </div><div style="font-family:arial, helvetica, sans-serif;font-size:10pt">
<br></div><div style="font-family:arial, helvetica, sans-serif;font-size:10pt">My questions are...</div><div style="font-family:arial, helvetica, sans-serif;font-size:10pt"><span style="white-space:pre-wrap"> </span></div>
<div style="font-family:arial, helvetica, sans-serif;font-size:10pt">1<span style="white-space:pre-wrap"> </span>"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?"</div>
<div style="font-family:arial, helvetica, sans-serif;font-size:10pt">2<span style="white-space:pre-wrap"> </span>"are cross schema queries allowed?"</div><div style="font-family:arial, helvetica, sans-serif;font-size:10pt">
3 <span style="white-space:pre-wrap"> </span>"does it also make sense to seperate non-spatial tables into their own schemas?"</div><div style="font-family:arial, helvetica, sans-serif;font-size:10pt"><br></div><div style="font-family:arial, helvetica, sans-serif;font-size:10pt">
<br></div><div style="font-family:arial, helvetica, sans-serif;font-size:10pt">thanks for any advice,</div><div style="font-family:arial, helvetica, sans-serif;font-size:10pt"><br></div><div style="font-family:arial, helvetica, sans-serif;font-size:10pt">
Rob</div></div></div><br><br></blockquote><div><br></div><div>Hi Robert,</div><div>1) yes, functions are available as long as your search_path contains schema public (which is standard setting)</div><div>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.</div>
<div>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. </div></div><br>regards<div>Szymon<br clear="all"><div><br>
</div>-- <br><a href="http://simononsoftware.com/" target="_blank"><b><font face="arial, helvetica, sans-serif" style="background-color:rgb(255, 255, 255)" color="#003300">http://simononsoftware.com/</font></b></a><br><br>
</div>