From Oracle/SDE to PostgreSQL/Postgis in real life

Session Type: 
Tech Session
Presenter(s): 
Lars Aksel Opsahl, Skog og landskap

The case is that we have tables with GB's of data. This tables are updated regularly by different users and applications. Mapserver is used for WMS and is connected to Oracle.

We can not change all the applications to use postgresSQL and we can not except all user's to switch from Oracle to postgresSQL overnight.

One place to start would be to use mapserver against Postgis. To do this we only need to make a copy of the data from Oracle to PostgreSQL. The Oracle database will master and PostgreSQL the slave.

In some cases it's easy to copy data from Oracle to PostgreSQL. For instance if we have small tables and non frequent changes.

But it this case we have big tables (More 10 GB and millions of rows pr table) and some of them changes many times a week.

In our case we need a simple program that can find all the differences and transfer this changes to PostgresSQL while both databases are in use.

We have made such program that handles this case. We want describe the algorithm behind this program and the code.

Key words here are :

  • Split in small chunks by using grids.
  • Keep track of neighbors
  • Remove all equals rows from work mem.
  • Push update, delete and read in single SQL transaction using Hibernate Spatial.

 

Speaker Bio: 

Have a master in computer scenece the University of Oslo. The last 4 years I have been working with agriculture maps here with Skog og landskap.

Schedule info

Time slot: 
Wed, 09/14/2011 - 11:00am - 11:30am
Room: 
Windows