[postgis-users] Unique Constraint on Spatial Point violated

[Sairam Krishnamurthy]

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://mc/compose?to=postgis-users@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/e5f5a5fa/attachment.html>