Migrating TIGER from Oracle to POSTGIS

[Ayorinde O Adesugba (CENSUS/DITD FED)]

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 the topology.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<mailto:ayorinde.o.adesugba at census.gov>
census.gov<http://www.census.gov/> | @uscensusbureau<https://twitter.com/uscensusbureau>  | 2020Census.gov<https://2020census.gov>
Connect with us on Social Media<http://www.census.gov/about/contact-us/social_media.html>




Census - General
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20250311/40cbb46a/attachment.htm>