Migrating TIGER from Oracle to POSTGIS

[Ayorinde O Adesugba (CENSUS/DITD FED)]

Census - General

Hi Sandro.

Thanks for the response. I do not have the authority to hire or recommend a contracting company, but I am definitely open to contributing time and code to make the topology extension better for all users.

If you can send any links/documentation of the process - I can start looking into it. I have made a few alterations to table definitions and the topology.topogeometry type, and some functions (creating a variant with int8 parameters but was restricted on AWS which is the target location for the final migration).

It would be great to contribute those changes to the base code, so it becomes part of the extension.

________________________________
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

________________________________
From: Sandro Santilli <strk at kbt.io>
Sent: Monday, March 17, 2025 04:51
To: Ayorinde O Adesugba (CENSUS/DITD FED) <Ayorinde.O.Adesugba at census.gov>
Cc: postgis-devel at lists.osgeo.org <postgis-devel at lists.osgeo.org>
Subject: Re: Migrating TIGER from Oracle to POSTGIS

[You don't often get email from strk at kbt.io. Learn why this is important at https://aka.ms/LearnAboutSenderIdentification ]

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;
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20250317/cc1fc38f/attachment-0001.htm>