[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Unique Constraint on Spatial Point violated
From: Sairam Krishnamurthy <kmsram420 () gmail ! com>
Date: 2011-04-22 21:36:22
Message-ID: BANLkTik0ZSSBC5daRc+Hxgw9zS3YXn0ssg () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
Thanks Brent. Going to try this. I will update the progress.
Thanks,
Sairam Krishnamurthy
+1 612 859 8161
On Fri, Apr 22, 2011 at 4:08 PM, <pcreso@pcreso.com> wrote:
> 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.
>
> 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.
>
> 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.
>
> 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.
>
> Cheers,
>
> Brent Wood
>
>
> --- On *Sat, 4/23/11, Sairam Krishnamurthy <kmsram420@gmail.com>* wrote:
>
>
> From: Sairam Krishnamurthy <kmsram420@gmail.com>
> Subject: Re: [postgis-users] Unique Constraint on Spatial Point violated
> To: pcreso@pcreso.com
> Cc: "PostGIS Users Discussion" <postgis-users@postgis.refractions.net>,
> kulkarni.aditya00@gmail.com
> Date: Saturday, April 23, 2011, 7:44 AM
>
>
> 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.
>
> But is it really different from the why I am doing it right now? Will it
> help in the unique constraint in any way ?
>
> Thanks,
> Sairam Krishnamurthy
> +1 612 859 8161
>
>
> On Fri, Apr 22, 2011 at 2:35 PM, \
> <pcreso@pcreso.com<http://mc/compose?to=pcreso@pcreso.com>
> > wrote:
>
> I'd try a different approach to loading your data into the table.
>
> 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 '?';"
>
> Then add your geometry column to the table.
> select addgeometrycolumn(...);
>
> Then update the geometry column using makepoint
> update table set geom = setsrid(makepoint(lon,lat));
>
> Then try to create your unique indexes & work through any duplicates in the
> db records.
>
> 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:
>
> alter table add column gid serial (or bigserial);
>
> then make gid the primary key.
>
> --- On *Sat, 4/23/11, Sairam Krishnamurthy \
> <kmsram420@gmail.com<http://mc/compose?to=kmsram420@gmail.com>
> > * wrote:
>
>
> From: Sairam Krishnamurthy \
> <kmsram420@gmail.com<http://mc/compose?to=kmsram420@gmail.com>
> >
> Subject: [postgis-users] Unique Constraint on Spatial Point violated
> To: postgis-users@postgis.refractions.net<http://mc/compose?to=postgis-users@postgis.refractions.net>
>
> Cc: "Aditya Kulkarni" \
> <kulkarni.aditya00@gmail.com<http://mc/compose?to=kulkarni.aditya00@gmail.com>
> >
> Date: Saturday, April 23, 2011, 7:10 AM
>
> All,
>
> I am loading a table from a file. The file is really big and has
> millions of rows. Table structure is described below:
>
> lat : double precision (primary key)
> lon: double precision (primary key)
> spatialPoint: geometry (unique)
>
> The file has lines of lat and lon: (lat,lon)
>
> Since I am loading from a file I cannot load the spatialPoint
> directly. I have a trigger to call the following function BEFORE
> INSERT OR UPDATE:
>
> BEGIN
> NEW."spatialPoint" := ST_SetSRID(ST_MakePoint(NEW.lon, NEW.lat), 4326);
> RETURN new;
> END
>
> When I load the table I get unique key constraint on the filed
> spatialPoint. This happens when I try to load the point
> "-3.751046|-51.359041". But when I grep for the point in the file only
> one row exists.
>
> I am not sure if this is a precision problem in calculting the spatial
> points from the lat.lon value.
>
> Can someone help me with this?
>
> Thanks,
> Sairam Krishnamurthy
> +1 612 859 8161
> _______________________________________________
> postgis-users mailing list
> postgis-users@postgis.refractions.net<http://mc/compose?to=postgis-users@postgis.refractions.net>
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
[Attachment #5 (text/html)]
<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>
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic