<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<p style="font-family:Calibri;font-size:10pt;color:#000000;margin:5pt;font-style:normal;font-weight:normal;text-decoration:none;" align="Center">
Census - General<br>
</p>
<br>
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Hi Sandro.</div>
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
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.</div>
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; color: rgb(0, 0, 0);">
<span style="font-size: 12pt;">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
</span><span style="font-size: 11pt;"><b>topology.topogeometry</b> </span><span style="font-size: 12pt;">type, and some functions (creating a variant with int8 parameters but was restricted on AWS which is the target location for the final migration). </span></div>
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
It would be great to contribute those changes to the base code, so it becomes part of the extension.</div>
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div id="Signature">
<div id="divtagdefaultwrapper" dir="ltr" style="font-family:Calibri, Arial, Helvetica, sans-serif;font-size:12pt;color:rgb(0, 0, 0);background-color:rgb(255, 255, 255)">
<hr style="margin-right: 0px; margin-left: 0px;">
<div style="margin-right: 0px; margin-left: 0px; font-family: "Default Sans Serif", Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">
<i>Thanks, </i></div>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 13px;"><i><br>
</i></span></p>
<div style="margin: 0px; font-family: Arial, sans-serif; font-size: 10pt;"><b>Ayorinde Adesugba,
</b>Special Assistant to ADC,</div>
<div style="margin: 0px; font-family: Arial, sans-serif; font-size: 10pt;">Spatial Data Systems, DITD</div>
<div style="margin: 0px; font-family: Arial, sans-serif; font-size: 10pt;">U.S. Census Bureau </div>
<div style="margin: 0px; font-family: Arial, sans-serif; font-size: 10pt;">O: 301-763-2057 | M: 202-381-8034 | F: 301-763-4710 </div>
<div style="margin: 0px; font-family: Arial, sans-serif; font-size: 10pt;"><a href="mailto:ayorinde.o.adesugba@census.gov" id="OWAa2a7db47-c395-082c-5cc8-80350ea4cd3b" class="OWAAutoLink">ayorinde.o.adesugba@census.gov</a> </div>
<div style="margin: 0px; font-family: Arial, sans-serif; font-size: 10pt;"><a href="http://www.census.gov/" target="_blank" id="OWA971dfb0e-250b-420a-39a7-305fda56d47b" class="OWAAutoLink">census.gov</a> |
<a href="https://twitter.com/uscensusbureau" id="OWA3ef6b435-fa55-9b1f-fb2e-2924990ae123" class="OWAAutoLink" title="https://twitter.com/uscensusbureau">
@uscensusbureau</a> | <a href="https://2020census.gov" id="OWAb0f8f292-3a7c-e729-08c1-6cd7efeeedbb" class="OWAAutoLink" title="https://2020census.gov">
2020Census.gov</a></div>
<div style="margin: 0px; font-family: Arial, sans-serif; font-size: 10pt;">Connect with us on
<a href="http://www.census.gov/about/contact-us/social_media.html" target="_blank" id="OWAad0b81c6-d170-93cc-6a81-db07f427c0dd" class="OWAAutoLink">
Social Media</a></div>
<p style="margin: 0px;"><span style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 13px;"> </span></p>
</div>
</div>
<div id="appendonsend"></div>
<br>
<p style="font-family:Calibri;font-size:10pt;color:#000000;margin:5pt;font-style:normal;font-weight:normal;text-decoration:none;" align="Center">
Census - General<br>
</p>
<hr style="display:inline-block;width:98%" tabindex="-1">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" style="font-size:11pt" color="#000000"><b>From:</b> Sandro Santilli <strk@kbt.io><br>
<b>Sent:</b> Monday, March 17, 2025 04:51<br>
<b>To:</b> Ayorinde O Adesugba (CENSUS/DITD FED) <Ayorinde.O.Adesugba@census.gov><br>
<b>Cc:</b> postgis-devel@lists.osgeo.org <postgis-devel@lists.osgeo.org><br>
<b>Subject:</b> Re: Migrating TIGER from Oracle to POSTGIS</font>
<div> </div>
</div>
<div class="BodyFragment"><font size="2"><span style="font-size:11pt;">
<div class="PlainText">[You don't often get email from strk@kbt.io. Learn why this is important at
<a href="https://aka.ms/LearnAboutSenderIdentification">https://aka.ms/LearnAboutSenderIdentification</a> ]<br>
<br>
Hi Ayorinde,<br>
<br>
On Tue, Mar 11, 2025 at 04:41:08PM +0000, Ayorinde O Adesugba (CENSUS/DITD FED) wrote:<br>
<br>
> I am the lead developer, prototyping migrating TIGER (Spatial/Topology) database from Oracle to POSTGIS.<br>
<br>
Good choice !<br>
<br>
> I have run into some issues and wanted to find out how to communicate<br>
> these issues and possibly contribute to the product so we can complete<br>
> a successful migration. TIGER is probably the largest topology schema<br>
> in the world, and has it performance and complexity requirements.<br>
<br>
This mailing list is perfect for communication.<br>
Issues tracking is done on <a href="https://trac.osgeo.org/postgis">https://trac.osgeo.org/postgis</a> - you will<br>
need an account, feel free to mail me privately to get the required<br>
"mantra".<br>
<br>
> Here is a quick summary of where we are:<br>
><br>
> 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)<br>
<br>
This is already ticketed here:<br>
<a href="https://trac.osgeo.org/postgis/ticket/3110">https://trac.osgeo.org/postgis/ticket/3110</a><br>
<br>
It was a requirement for Regione Toscana too (big dataset as well).<br>
The enhancement entered the "librttopo" fork of PostGIS Topology but<br>
was never backported. A contribution (in code or funds) would be<br>
welcome.<br>
<br>
> 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).<br>
><br>
> Please let me know who to contact.<br>
<br>
I'm available for hire.<br>
<br>
--strk;<br>
</div>
</span></font></div>
</body>
</html>