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 <[email protected]> wrote: From: Sairam Krishnamurthy <[email protected]> Subject: [postgis-users] Unique Constraint on Spatial Point violated To: [email protected] Cc: "Aditya Kulkarni" <[email protected]> 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 [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
