[postgis-users] Unique Constraint on Spatial Point violated

[Sairam Krishnamurthy]

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 at 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 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<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 at gmail.com<http://mc/compose?to=kmsram420@gmail.com>
> >* wrote:
>
>
> From: Sairam Krishnamurthy <kmsram420 at gmail.com<http://mc/compose?to=kmsram420@gmail.com>
> >
> Subject: [postgis-users] Unique Constraint on Spatial Point violated
> To: postgis-users at postgis.refractions.net<http://mc/compose?to=postgis-users@postgis.refractions.net>
> Cc: "Aditya Kulkarni" <kulkarni.aditya00 at 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 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/e45458b0/attachment.html>