[postgis-users] Possibility to join PostGIS with non spatialdbstored attribute data

[Steven De Vriendt]

Regina,

Thanks for this answer, think you've lost me around the middle of
paragraph two but the first lines of your reply are what I'm after.
I'll explain.

A lot of data we use is stored in a non-spatial Oracle database.
We've introduced GIS in our organisation to 'visualise' the Oracle
database (we know what happens, but  WHERE do this happen ?)
Using a join in ArcGIS we're now able to view the attribute data
stored in Oracle using ArcGIS, based on a unique ID (that's a dossier
number).

I'm trying to do the same in a MapServer-envirnoment. Basically, I
want to visualise PostGIS-polygon data and Oracle attribute data,
based on a join of ID's that are stored in both databases.

The purpose is not focused on the PostGIS spatial functions, my main
interest is to be able to visualise both PostGIS (spatial) and Oracle
(attribute) data in MapServer.

Thanks for helping me out !

Regards
Steven

On 5/8/07, Obe, Regina <robe.dnd at cityofboston.gov> wrote:
>
>
>
> Steve,
>
> 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.
>
> 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.
>
> 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.
>
> 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.
>
> Hope this wasn't needlessly complicated.  It may help us a bit to know what
> kind of problem you are trying to solve here.
>
> Hope that helps,
> Regina
>
>  ________________________________
>  From: postgis-users-bounces at postgis.refractions.net on
> behalf of Steven De Vriendt
> Sent: Tue 5/8/2007 12:21 PM
> To: rindahl at lrcwe.com; PostGIS Users Discussion
> Subject: Re: [postgis-users] Possibility to join PostGIS with non
> spatialdbstored attribute data
>
>
>
>
> Bruce,
>
> Thanks for your quick reply. Now if I make a link in MsAccess to my
> Oracle-tables...
> could I make use of the same type of ODBC connection you mention ?
>
> Steven
>
> On 5/8/07, Bruce Rindahl <rindahl at lrcwe.com> wrote:
> > For MS access link the PostGIS tables using the ODBC connection included
> in
> > the Windows version.  Then they look just like a regular table you can
> join
> > or query.
> > Bruce Rindahl
> >
> > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net
> > [mailto:postgis-users-bounces at postgis.refractions.net] On
> Behalf Of Steven
> > De Vriendt
> > Sent: Tuesday, May 08, 2007 10:09 AM
> > To: postgis-users at postgis.refractions.net
> > Subject: [postgis-users] Possibility to join PostGIS with non spatial
> > dbstored attribute data
> >
> > Hi,
> >
> > This must have been asked before but I can't seem to find a good answer to
> > it.
> > I'd like to join PostGIS data with attribute data stored in different
> > databases, MS Access, Oracle,...based on a similar unique key stored
> > in both databases.
> >
> > I don't know how you guys do this using postgis but sure a lot of you
> > must use this type of connection.
> > Can someone explain me how to do this ?
> >
> > Regards,
> > Steven
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> >
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> >
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> ________________________________
>
>
>  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.
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>