Oracle FDW
or… making data in an oracle database appear to be in PostGIS!
Foreign Data Wrapper?
In 2003, a new specification "SQL Management of External Data" (SQL/MED) was added to the SQL standard.
It is a standardized way of handling access to remote objects from SQL databases.
In 2011, PostgreSQL 9.1 was released with read-only support
In 2013 write support was added with PostgreSQL 9.3.
There are now a variety of Foreign Data Wrappers (FDW) available which enable PostgreSQL Server to different remote data stores,
ranging from other SQL databases through to flat file.
Oracle FDW
Install
Download the appropriate zip file
Copy files into the appropriate folders
Demo time!
So how does it work?
In Oracle - Simple table called AH_TEST (imaginatively!)
In PostgreSQL
Create Foreign Table
Gotchas!
No SDO_GEOMETRY :(
but...
does support BLOBS :)
So how do we do Geometry?
In oracle we create a view which converts SDO_GEOMETRY to WKB
Little gotcha! WKB does not include the SRID
Then in PostGIS we create a view on the foreign table which converts it from WKB to Geometry :)
What about editing in PostgreSQL 9.3?
Possible Solution:
Write an instead of trigger on both the views used to make the updates on the underlying tables.
Problem:
You can't update a derived column in a view
and we are using derived columns!
Issue:
Only works for updating non-spatial data. Will not work for inserting, deleting or updating spatial data.
Oracle instead of triggers do not support returning clause :(
Gotcha!
- You can see data held in an Oracle database as if it was in the PostGIS database!
- If you update non-spatial data in PostGIS it automatically updates the oracle database
Summary
You CAN'T edit spatial data yet!
but .....
It is in testing :)
