<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle19
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle20
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal">Hi,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">We have greenplum database with PostGIS extension.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><b><u>Greenplum version :</u></b> <o:p></o:p></p>
<p class="MsoNormal">PostgreSQL 8.2.15 (Greenplum Database 4.3.9.0 build 1) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Aug 8 2016 05:36:26<o:p></o:p></p>
<p class="MsoNormal"><b><u>PostGIS version<o:p></o:p></u></b></p>
<p class="MsoNormal">POSTGIS="2.0.3 r11128" GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel. 4.8.0, 6 March 2012" LIBXML="2.7.8"<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I’m working on a POC to find the country/state/country of a transaction based on the GPS lon and lat. The transaction table has 15-20 millions records.<o:p></o:p></p>
<p class="MsoNormal">I downloaded the polygon for all countries in a table.<o:p></o:p></p>
<p class="MsoNormal">Added a geometry column to the transaction table and derived the value using ST_SetSRID(ST_MakePoint(idle_viol_pos_long_deg::double precision,idle_viol_pos_lat_deg::double precision),4326).<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Then started updating the transaction table with country code using the below statement.
<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"> update test a<o:p></o:p></p>
<p class="MsoNormal"> set country = su_a3 <o:p></o:p></p>
<p class="MsoNormal"> from (select su_a3 ,geom from ne_10m_admin_0_countries) b
<o:p></o:p></p>
<p class="MsoNormal"> where ST_Intersects(a.geom, b.geom)<o:p></o:p></p>
<p class="MsoNormal"> and country is null<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Updating 1 million records took around 1 hr.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">If I try to update 20 millions , not sure how long it will take.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I tried to reduce the entry in polygon table to 5 as the transaction received is only from 5 countries.. Didn’t see any gain in the performance.<o:p></o:p></p>
<p class="MsoNormal">Created index on geom column of the transaction table and still no gain.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Question. Whether I’m on the right track? If yes how to overcome the performance issue?<o:p></o:p></p>
<p class="MsoNormal">Any guidance would be appreciated.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Thanks<o:p></o:p></p>
<p class="MsoNormal">Suraj <o:p></o:p></p>
</div>
=============== CONFIDENTIALITY NOTICE: This e-mail and any attachments contain information from Lytx, Inc. and/or its affiliates, and are intended solely for the use of the named recipient(s). This e-mail may contain confidential information of Lytx and its
customers. Any dissemination of this e-mail by anyone other than an intended recipient is strictly prohibited. If you are not a named recipient, you are prohibited from any further viewing of the e-mail or any attachments or from making any use of the e-mail
or attachments. If you believe you have received this e-mail in error, notify the sender immediately and permanently delete the e-mail, any attachments, and all copies thereof from any drives or storage media and destroy any printouts of the e-mail or attachments.
</body>
</html>