[postgis-users] Unique Constraint on Spatial Point violated

[pcreso at pcreso.com]

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 at gmail.com> wrote:

From: Sairam Krishnamurthy <kmsram420 at gmail.com>
Subject: Re: [postgis-users] Unique Constraint on Spatial Point violated
To: pcreso at pcreso.com
Cc: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>, kulkarni.aditya00 at 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 at 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 at gmail.com> wrote:

From: Sairam Krishnamurthy <kmsram420 at gmail.com>


Subject: [postgis-users] Unique Constraint on Spatial Point violated
To: postgis-users at postgis.refractions.net
Cc: "Aditya Kulkarni" <kulkarni.aditya00 at 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 at postgis.refractions.net


http://postgis.refractions.net/mailman/listinfo/postgis-users




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110422/2aa83583/attachment.html>