Migrating TIGER from Oracle to POSTGIS

[Sandro Santilli]

Hi Ayorinde,

On Tue, Mar 11, 2025 at 04:41:08PM +0000, Ayorinde O Adesugba (CENSUS/DITD FED) wrote:

> I am the lead developer, prototyping migrating TIGER (Spatial/Topology) database from Oracle to POSTGIS.

Good choice !

> I have run into some issues and wanted to find out how to communicate
> these issues and possibly contribute to the product so we can complete
> a successful migration. TIGER is probably the largest topology schema
> in the world, and has it performance and complexity requirements.

This mailing list is perfect for communication.
Issues tracking is done on https://trac.osgeo.org/postgis - you will
need an account, feel free to mail me privately to get the required
"mantra".

> Here is a quick summary of where we are:
> 
> One of my first issues is the size of the datatype in relation table - it is currently int4 but TIGER has OIDs (unique IDs) that are 15-17 digits long. These contain business logic and switching them or creating alternate/new ones is not an option. I have been able to alter the relation table (and the topology.topogeometry type)  as a workaround, but a few of the functions expect an int4 ID eg. CREATE FUNCTION topology.st_getfacegeometry(toponame character varying, aface int4)

This is already ticketed here:
https://trac.osgeo.org/postgis/ticket/3110

It was a requirement for Regione Toscana too (big dataset as well).
The enhancement entered the "librttopo" fork of PostGIS Topology but
was never backported. A contribution (in code or funds) would be
welcome.

> I am hoping we can work closely with the core team to make some changes to the base product. It would be great to have these variants as options in the base product. We would probably also need a few functions related to the management of the topology eg. being able to create a layer by passing a layerID instead of an auto generated one (we currently inserted directly into the topology.layers table).
> 
> Please let me know who to contact.

I'm available for hire.

--strk;