[postgis-users] Unique Constraint on Spatial Point violated

[pcreso at pcreso.com]

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/77d6c4e3/attachment.html>