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