<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>+1 612 859 8161<br>
<br><br><div class="gmail_quote">On Fri, Apr 22, 2011 at 2:35 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">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 href="mailto: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 href="mailto:kmsram420@gmail.com" target="_blank">kmsram420@gmail.com</a>><br>
Subject: [postgis-users] Unique Constraint on Spatial Point violated<br>To: <a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>Cc: "Aditya Kulkarni" <<a href="mailto: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 class="h5">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 href="tel:%2B1%20612%20859%208161" value="+16128598161" target="_blank">+1 612 859 8161</a><br>
</div></div>_______________________________________________<br>postgis-users mailing list<br><a href="http://mc/compose?to=postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<a 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>