<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<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>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Good afternoon.</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
I am the lead developer, prototyping migrating TIGER (Spatial/Topology) database from Oracle to POSTGIS.</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
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.</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Here is a quick summary of where we are:</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
One of my first issues is the size of the datatype in <b>relation </b>table - it is currently
<b>int4 </b>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
<b>relation </b>table (and the <b>topology.topogeometry</b> type) as a workaround, but a few of the functions expect an
<b>int4 </b>ID eg. <b><code>CREATE FUNCTION topology.st_getfacegeometry(toponame character varying, aface
</code>int4<code>)</code></b></div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
This would need to be modified to <b>int8 </b>or <b>bigint</b>, 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. </div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
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
<b>topology.layers</b> table).</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Please let me know who to contact.</div>
<div id="Signature" class="elementToProof">
<div id="divtagdefaultwrapper" dir="ltr" style="color:rgb(0,0,0);font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt;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">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">census.gov</a> |
<a href="https://twitter.com/uscensusbureau" title="https://twitter.com/uscensusbureau">
@uscensusbureau</a> | <a href="https://2020census.gov" 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">
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>
<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>
</body>
</html>