<HTML dir=ltr><HEAD><TITLE>Re: [postgis-users] Possibility to join PostGIS with non spatialdbstored attribute data</TITLE>
<META http-equiv=Content-Type content="text/html; charset=unicode">
<META content="MSHTML 6.00.2900.3059" name=GENERATOR></HEAD>
<BODY>
<DIV id=idOWAReplyText89817 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>Steve,</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>Yes you can do the same with Oracle tables as well. The one caveat to using ODBC linked tables is that you really can't take advantage of PostGIS functions or any PostGreSQL specific functions or Oracle functions as a matter of fact since Access puts a Jet emulation layer around the linked tables.</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>This means that you can join your PostGIS tables with your access tables or other linked tables, by attribute fields, but then you can't then do expand, buffer etc. operations on that joined result as you would be able to if they were all PostgreSQL tables.</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>One approach I have used to get around this obstacle is to use an Access pass-through query except with an access pass-through (within the query definitions, you are in PostGreSQL/PostGIS land so to speak so within the pass through query definition - you can only use PostGIS tables), although you can then join the resulting pass through query result with other access native tables and linked tables - say from Oracle. You can with VB code dynamically create and change the definitions of pass through queries so say your query is based on some user input, then this is an ideal way to present data that say uses the output of a postgresql function call.</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>If you need to go one step further, then you may need to have a temp table in PostGIS where you can add and delete select records from your Access database - to join natively in your PostGIS environment and feed back via a pass-thru query.</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>Hope this wasn't needlessly complicated. It may help us a bit to know what kind of problem you are trying to solve here.</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>Hope that helps,</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2>Regina</FONT></DIV></DIV>
<DIV dir=ltr><BR>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> postgis-users-bounces@postgis.refractions.net on behalf of Steven De Vriendt<BR><B>Sent:</B> Tue 5/8/2007 12:21 PM<BR><B>To:</B> rindahl@lrcwe.com; PostGIS Users Discussion<BR><B>Subject:</B> Re: [postgis-users] Possibility to join PostGIS with non spatialdbstored attribute data<BR></FONT><BR></DIV>
<DIV>
<P><FONT size=2>Bruce,<BR><BR>Thanks for your quick reply. Now if I make a link in MsAccess to my<BR>Oracle-tables...<BR>could I make use of the same type of ODBC connection you mention ?<BR><BR>Steven<BR><BR>On 5/8/07, Bruce Rindahl <rindahl@lrcwe.com> wrote:<BR>> For MS access link the PostGIS tables using the ODBC connection included in<BR>> the Windows version. Then they look just like a regular table you can join<BR>> or query.<BR>> Bruce Rindahl<BR>><BR>> -----Original Message-----<BR>> From: postgis-users-bounces@postgis.refractions.net<BR>> [<A href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>] On Behalf Of Steven<BR>> De Vriendt<BR>> Sent: Tuesday, May 08, 2007 10:09 AM<BR>> To: postgis-users@postgis.refractions.net<BR>> Subject: [postgis-users] Possibility to join PostGIS with non spatial<BR>> dbstored attribute data<BR>><BR>> Hi,<BR>><BR>> This must have been asked before but I can't seem to find a good answer to<BR>> it.<BR>> I'd like to join PostGIS data with attribute data stored in different<BR>> databases, MS Access, Oracle,...based on a similar unique key stored<BR>> in both databases.<BR>><BR>> I don't know how you guys do this using postgis but sure a lot of you<BR>> must use this type of connection.<BR>> Can someone explain me how to do this ?<BR>><BR>> Regards,<BR>> Steven<BR>> _______________________________________________<BR>> postgis-users mailing list<BR>> postgis-users@postgis.refractions.net<BR>> <A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>><BR>><BR>><BR>> _______________________________________________<BR>> postgis-users mailing list<BR>> postgis-users@postgis.refractions.net<BR>> <A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>><BR>_______________________________________________<BR>postgis-users mailing list<BR>postgis-users@postgis.refractions.net<BR><A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></FONT></P></DIV></BODY></HTML>
<HTML><BODY><P><hr size=1></P>
<P><STRONG>
The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
</STRONG></P></BODY></HTML>