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.
Download the appropriate zip file
Copy files into the appropriate folders
So how does it work?
In Oracle - Simple table called AH_TEST (imaginatively!)
Create Foreign Table
No SDO_GEOMETRY :(
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?
Write an instead of trigger on both the views used to make the updates on the underlying tables.
You can't update a derived column in a view
and we are using derived columns!
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 :(
- 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
You CAN'T edit spatial data yet!
It is in testing :)
By Aileen Heal