<font color="#006600"><font size="2"><font face="courier new,monospace">Thanks Brent. Going to try this. I will update the progress. </font></font></font><div><font color="#006600"><font size="2"><font face="courier new,monospace"><br>
</font></font></font></div><div><font color="#006600"><font size="2"><font face="courier new,monospace"><br clear="all"></font></font></font>Thanks,<br>Sairam Krishnamurthy<br>+1 612 859 8161<br>
<br><br><div class="gmail_quote">On Fri, Apr 22, 2011 at 4:08 PM, <span dir="ltr"><<a href="mailto:pcreso@pcreso.com">pcreso@pcreso.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<table cellspacing="0" cellpadding="0" border="0"><tbody><tr><td valign="top" style="font:inherit">It is likely to be the fastest waty to initially populate the table as a bulk insert. There is nothing stopping you adding rows later however you like. I have populated tables with 250,000,000+ rows like this.<br>
<br>The advantage is that once the data is loaded without constraints, you can run queries on the data to identify rows with values that will breach the constraints, then address the now identified problems so the constraints can be imposed.<br>
<br>The first step is to get the data loaded. You can do this with your trigger & no constraint if you prefer. Then diagnose/fix/add constraint.<br><br>I'd get the data inserted first, then add the composite unique index on lat/long. Then create the point. Note that your trigger may try to generate the point before the insert is validated, so could show the point duplication error prior to identifying the
duplicate lat/lon data.<br><br>Cheers,<br><br> Brent Wood<div class="im"><br><br>--- On <b>Sat, 4/23/11, Sairam Krishnamurthy <i><<a href="mailto:kmsram420@gmail.com" target="_blank">kmsram420@gmail.com</a>></i></b> wrote:<br>
</div><blockquote style="border-left:2px solid rgb(16, 16, 255);margin-left:5px;padding-left:5px"><div class="im"><br>From: Sairam Krishnamurthy <<a href="mailto:kmsram420@gmail.com" target="_blank">kmsram420@gmail.com</a>><br>
</div>Subject: Re: [postgis-users] Unique Constraint on Spatial Point violated<br>To: <a href="mailto:pcreso@pcreso.com" target="_blank">pcreso@pcreso.com</a><br>Cc: "PostGIS Users Discussion" <<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>>, <a href="mailto:kulkarni.aditya00@gmail.com" target="_blank">kulkarni.aditya00@gmail.com</a><br>
Date: Saturday, April 23, 2011, 7:44 AM<div><div></div><div class="h5"><br><br><div><font color="#006600"><font size="2"><font face="courier new,monospace">Well ... That wont in my case because I will be adding rows to the table later. Also it requires additional time to update the table. The table will have millions of rows. </font></font></font><div>
<font color="#006600"><font size="2"><font face="courier new,monospace"><br></font></font></font></div><div><font color="#006600"><font size="2"><font face="courier new,monospace">But is it really different from the why I am doing it right now? Will it help in the unique constraint in any way ?</font></font></font></div>
<div><font color="#006600"><font size="2"><font face="courier new,monospace"><br clear="all"></font></font></font>Thanks,<br>Sairam Krishnamurthy<br><a href="tel:%2B1%20612%20859%208161" value="+16128598161" target="_blank">+1 612 859 8161</a><br>
<br><br><div>On Fri, Apr 22, 2011 at 2:35 PM, <span dir="ltr"><<a rel="nofollow" href="http://mc/compose?to=pcreso@pcreso.com" target="_blank">pcreso@pcreso.com</a>></span> wrote:<br><blockquote style="margin:0pt 0pt 0pt 0.8ex;border-left:1px solid rgb(204, 204, 204);padding-left:1ex">
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr><td style="font:inherit" valign="top">I'd try a different approach to loading your data into the table.<br><br>Try loading your lat/lon values using copy. This will be fastest (specify the field delimiter char): eg: cat <file> | psql -d <db> -c "copy <table> from STDIN with delimiter '?';"<br>
<br>Then add your geometry column to the table.<br>select addgeometrycolumn(...);<br><br>Then update the geometry column using makepoint<br>update table set geom = setsrid(makepoint(lon,lat));<br><br>Then try to create your unique indexes & work through any duplicates in the db records.<br>
<br>It is also a good idea to have unique indices as required on natural keys, but have an integer primary key on the table. This is easy to do:<br><br>alter table add column gid serial (or bigserial);<br><br>then make gid the primary key.<br>
<br>--- On <b>Sat, 4/23/11, Sairam Krishnamurthy
<i><<a rel="nofollow" href="http://mc/compose?to=kmsram420@gmail.com" target="_blank">kmsram420@gmail.com</a>></i></b> wrote:<br><blockquote style="border-left:2px solid rgb(16, 16, 255);margin-left:5px;padding-left:5px">
<br>From: Sairam Krishnamurthy <<a rel="nofollow" href="http://mc/compose?to=kmsram420@gmail.com" target="_blank">kmsram420@gmail.com</a>><br>
Subject: [postgis-users] Unique Constraint on Spatial Point violated<br>To: <a rel="nofollow" href="http://mc/compose?to=postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
Cc: "Aditya Kulkarni" <<a rel="nofollow" href="http://mc/compose?to=kulkarni.aditya00@gmail.com" target="_blank">kulkarni.aditya00@gmail.com</a>><br>
Date: Saturday, April 23, 2011, 7:10 AM<br><br><div><div><div></div><div>All,<br><br>I am loading a table from a file. The file is really big and has<br>millions of rows. Table structure is described below:<br>
<br>lat : double precision (primary key)<br>lon: double precision (primary key)<br>spatialPoint: geometry (unique)<br><br>The file has lines of lat and lon: (lat,lon)<br><br>Since I am loading from a file I cannot load the spatialPoint<br>
directly. I have a trigger to call the following function BEFORE<br>INSERT OR UPDATE:<br><br>BEGIN<br> NEW."spatialPoint"
:= ST_SetSRID(ST_MakePoint(NEW.lon, NEW.lat), 4326);<br> RETURN new;<br>END<br><br>When I load the table I get unique key constraint on the filed<br>spatialPoint. This happens when I try to load the point<br>"-3.751046|-51.359041". But when I grep for the point in the file only<br>
one row exists.<br><br>I am not sure if this is a precision problem in calculting the spatial<br>points from the lat.lon value.<br><br>Can someone help me with this?<br><br>Thanks,<br>Sairam Krishnamurthy<br><a rel="nofollow">+1 612 859 8161</a><br>
</div></div>_______________________________________________<br>postgis-users mailing list<br><a rel="nofollow" href="http://mc/compose?to=postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<a rel="nofollow" href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></div></blockquote></td></tr></tbody></table></blockquote>
</div>
<br></div>
</div></div></div></blockquote></td></tr></tbody></table></blockquote></div><br></div>