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]> 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]> 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
