Migrating TIGER from Oracle to POSTGIS

[Paul Ramsey]

Sandro Santilli <strk at kbt.io <mailto:strk at kbt.io>> is the primary developer of the topology extension.
If you require a US contracting point, Crunchy Data works extensively with the US Federal Government. You can contact me directly at <paul.ramsey at crunchydata.com <mailto:paul.ramsey at crunchydata.com>>. 

Some couple years ago, we discussed with members of the TIGER team that simply taking topology in its default state and YOLO’ing a transition was not going to work, and that a fair amount of investment would be required. When one remembers that Oracle topology was essentially specifically engineered for TIGER use case, hoping PostGIS would just come off the shelf and handle TIGER was not a great bet.

The problem of topology editing remains, I would think? The equivalent to Oracle’s TopMap does not exist.

ATB,
Paul

> On Mar 11, 2025, at 9:41 AM, Ayorinde O Adesugba (CENSUS/DITD FED) <Ayorinde.O.Adesugba at census.gov> wrote:
> 
> Census - General
> 
> Good afternoon.
> 
> I am the lead developer, prototyping migrating TIGER (Spatial/Topology) database from Oracle to POSTGIS.
> 
> 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.
> 
> 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 would need to be modified to int8 or bigint, and we have been able to do some of that on-prem. However, we are preparing to move to the cloud, and RDS is our current testbed. We are unable to modify variants of functions that are c-based, so that is currently a showstopper. 
> 
> 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 thetopology.layers table).
> 
> Please let me know who to contact.
> Thanks, 
> Ayorinde Adesugba, Special Assistant to ADC,
> Spatial Data Systems, DITD
> U.S. Census Bureau   
> O: 301-763-2057 | M: 202-381-8034 | F: 301-763-4710   
> ayorinde.o.adesugba at census.gov    census.gov | @uscensusbureau  | 2020Census.gov
> Connect with us on Social Media
>  
> Census - General