[postgis-devel] Loader cleanup

christian.graefe at web.de christian.graefe at web.de
Wed Apr 6 04:09:27 PDT 2005


Thanks for strives!
I didn't thought it up to the end.

Bert regards

PostGIS Development Discussion <postgis-devel at postgis.refractions.net> schrieb am 06.04.05 13:04:53:
> 
> On Wed, Apr 06, 2005 at 12:57:14PM +0200, christian.graefe at web.de wrote:
> > Hi strk
> > 
> > Thanks for your valueable work. It helps so much!
> > 
> > My idea, better say feature request is: It is theoretical
> > possible to add a parameter, may be "-i", which
> > automatically builds an GIST index on the "the_geom"
> > column?
> 
> Yes, it is theoretical possible, but we are trying to get 1.0.0 final
> version out so this is not the moment for features requests.
> The -p feature broke the wall due to contribution of the proposer.
> 
> Note also that having an index while loading more data would slow
> things down, so one would do:
> 	PREPARE
> 	APPEND
> 	...
> 	INDEX
> The index should probably ONLY be defined when creating the
> table (not in append mode), to avoid redefinitions attempts.
> 
> --strk;
> 
> > 
> > Best regards
> > Christian
> > 
> > 
> > PostGIS Development Discussion <postgis-devel at postgis.refractions.net> schrieb am 06.04.05 12:44:28:
> > > 
> > > I've finished the cleanup step.
> > > I didn't change anything in the algorithm, just:
> > > 	- made main() much more concise
> > > 	- fixed a bug in -w handling (for hwgeom)
> > > 
> > > Now I'd go on with:
> > > 
> > > 	- GID omission.
> > > 
> > > 	  Current loader explicitly set the gid when NOT
> > > 	  in append mode. I'd make it *always* act like that
> > > 	  and rely on the 'serial' type. Do you see any
> > > 	  problem with that ?
> > > 
> > > 	- TRANSACTION
> > > 
> > > 	  Current loader breaks insert lines in multiple
> > > 	  transaction blocks. It has been noted that this
> > > 	  should speed things up, but I'm afraid it would
> > > 	  add half-shapes (skipping the bogus transaction
> > > 	  block and continue with the rest). Would it be
> > > 	  ok for you wrapping the whole code in a single
> > > 	  transaction relying on dump mode (-D) for speed
> > > 	  reasons ?
> > > 
> > > 	- NULL shapes
> > > 
> > > 	  Current loader checks shapefile and exits with
> > > 	  an error if all shapes are null (or contain no
> > > 	  vertexes). This requires two shapefile scans
> > > 	  (check scan, load scan). Since the load scan
> > > 	  copes cleanly with null shapes what about dropping
> > > 	  the first scan allowing for a all-nulls shapefile
> > > 	  to produce an all-nulls geometry features table ?
> > > 	  (attributes would still be loaded!)
> > > 
> > > 	- PREPARE mode
> > > 
> > > 	  I think we all know about this ;)
> > > 
> > > 	- FIELD TYPE handling
> > > 
> > > 	  This is Marks' thread. Didn't check it out yet.
> > > 
> > > CVS contains work done so far.
> > > 
> > > --strk;
> > > 
> > > On Wed, Apr 06, 2005 at 12:25:56PM +0200, strk at refractions.net wrote:
> > > > Markus, I'm doing the big cleanup myself.
> > > > Please stop producing patches or I'll feel too guilty :P
> > > > I'll put the -p switch in.
> > > > 
> > > > --strk;
> > > > 
> > > > On Tue, Apr 05, 2005 at 10:44:22PM +0200, Markus Schaber wrote:
> > > > > Hi, @all,
> > > > > 
> > > > > Here's another version of the patch. Apart from the documentation
> > > > > updates, it tries to be as less invasive as possible. I even did not
> > > > > re-indent the if()-encapsulated main loop.
> > > > > 
> > > > > Markus
> > > > 
> > > > > Index: doc/postgis.xml
> > > > > ===================================================================
> > > > > RCS file: /home/cvs/postgis/postgis/doc/postgis.xml,v
> > > > > retrieving revision 1.135
> > > > > diff -u -r1.135 postgis.xml
> > > > > --- doc/postgis.xml	5 Apr 2005 08:00:07 -0000	1.135
> > > > > +++ doc/postgis.xml	5 Apr 2005 20:39:38 -0000
> > > > > @@ -1283,13 +1283,23 @@
> > > > >            </varlistentry>
> > > > >  
> > > > >            <varlistentry>
> > > > > +            <term>-p</term>
> > > > > +
> > > > > +            <listitem>
> > > > > +              <para>Only produces the table creation SQL code, without adding 
> > > > > +              any actual data. This can be used if you need to completely
> > > > > +              separate the table creation and data loading steps.</para>
> > > > > +            </listitem>
> > > > > +          </varlistentry>
> > > > > +
> > > > > +          <varlistentry>
> > > > >              <term>-D</term>
> > > > >  
> > > > >              <listitem>
> > > > > -              <para>Creates a new table and populates it from the Shape file.
> > > > > -              This uses the PostgreSQL "dump" format for the output data and
> > > > > -              is much faster to load than the default "insert" SQL format. Use
> > > > > -              this for very large data sets.</para>
> > > > > +              <para>Use the PostgreSQL "dump" format for the output data. This 
> > > > > +              can be combined with -a, -c and -d. It is much faster to load
> > > > > +              than the default "insert" SQL format. Use this for very large data
> > > > > +              sets.</para>
> > > > >              </listitem>
> > > > >            </varlistentry>
> > > > >  
> > > > > @@ -1334,6 +1344,8 @@
> > > > >  
> > > > >          </variablelist>
> > > > >  
> > > > > +        <para>Note that -a, -c, -d and -p are mutually exclusive.</para>
> > > > > +
> > > > >          <para>An example session using the loader to create an input file and
> > > > >          uploading it might look like this:</para>
> > > > >  
> > > > > Index: doc/man/shp2pgsql.1
> > > > > ===================================================================
> > > > > RCS file: /home/cvs/postgis/postgis/doc/man/shp2pgsql.1,v
> > > > > retrieving revision 1.3
> > > > > diff -u -r1.3 shp2pgsql.1
> > > > > --- doc/man/shp2pgsql.1	5 Apr 2005 13:43:50 -0000	1.3
> > > > > +++ doc/man/shp2pgsql.1	5 Apr 2005 20:39:38 -0000
> > > > > @@ -26,8 +26,14 @@
> > > > >  Creates a new table and populates it from the Shape file. This is the default mode.
> > > > >  
> > > > >  .TP 
> > > > > +\fB\-p\fR
> > > > > +Only produces the table creation SQL code, without adding any actual data. This can
> > > > > +be used if you need to completely separate the table creation and data loading steps.
> > > > > +
> > > > > +.TP 
> > > > >  \fB\-D\fR
> > > > > -Use the PostgreSQL "dump" format for the output data. This can be combined with -d, -a and -c and is much faster to load than the default "insert" SQL format. Use this for very large data sets.
> > > > > +Use the PostgreSQL "dump" format for the output data. This can be combined with -a, -c and -d.
> > > > > +It is much faster to load than the default "insert" SQL format. Use this for very large data sets.
> > > > >  
> > > > >  .TP 
> > > > >  \fB\-s\fR <\fISRID\fR>
> > > > > @@ -47,6 +53,9 @@
> > > > >  Note that this will introduce coordinate drifts and will drop
> > > > >  M values from shapefiles.
> > > > >  
> > > > > +.LP
> > > > > +Note that -a, -c, -d and -p are mutually exclusive.
> > > > > +
> > > > >  .SH "EXAMPLES"
> > > > >  .LP 
> > > > >  An example session using the loader to create an input file and uploading it might look like this:
> > > > > Index: loader/README.shp2pgsql
> > > > > ===================================================================
> > > > > RCS file: /home/cvs/postgis/postgis/loader/README.shp2pgsql,v
> > > > > retrieving revision 1.3
> > > > > diff -u -r1.3 README.shp2pgsql
> > > > > --- loader/README.shp2pgsql	4 May 2002 22:44:04 -0000	1.3
> > > > > +++ loader/README.shp2pgsql	5 Apr 2005 20:39:38 -0000
> > > > > @@ -38,19 +38,23 @@
> > > > >  
> > > > >  The options are as follows:
> > > > >  
> > > > > -(-a || -c || -d) these options are mutually exclusive.
> > > > > +(-a || -c || -d || -p) these options are mutually exclusive.
> > > > >  
> > > > >    -a    Append mode. Do not delete the target table or try to create
> > > > >          a new table, simple insert the data into the existing table.
> > > > >          A table will have to exist for this to work, it is usually
> > > > > -        used after a create mode as been run once.(mutually exclusive
> > > > > -	with -c and -d)
> > > > > +        used after a create mode as been run once or after -p. (mutually
> > > > > +        exclusive with -c, -d and -p)
> > > > >    -c    Create mode. This is the default mode is no other is specified.
> > > > >  	Create a new table and upload the data into that table.
> > > > > -	(mutually exclusive with -a and -d)
> > > > > +	(mutually eclusive with -a, -d and -p)
> > > > >    -d    Delete mode. Delete the database table named <tablename>, then
> > > > >  	create a new one with that name before uploading the data into
> > > > > -	the new empty database table.(mutually exclusive with -a and -c)
> > > > > +	the new empty database table. (mutually exclusive with -a, -c 
> > > > > +        and -p)
> > > > > +  -p    Prepare mode. Read the table schema from the shape file and 
> > > > > +        create the new table, but do not insert any data. (mutually
> > > > > +        exclusive with -a, -c and -d)
> > > > >  
> > > > >    -D Dump. When inserting the data into the table use 'dump' format.
> > > > >  	Dump format is used by PostgreSQL for large data dumps and 
> > > > > Index: loader/shp2pgsql.c
> > > > > ===================================================================
> > > > > RCS file: /home/cvs/postgis/postgis/loader/shp2pgsql.c,v
> > > > > retrieving revision 1.84
> > > > > diff -u -r1.84 shp2pgsql.c
> > > > > --- loader/shp2pgsql.c	4 Apr 2005 20:51:26 -0000	1.84
> > > > > +++ loader/shp2pgsql.c	5 Apr 2005 20:39:40 -0000
> > > > > @@ -532,9 +532,9 @@
> > > > >  	printf("BEGIN;\n");
> > > > >  
> > > > >  	//if opt is 'a' do nothing, go straight to making inserts
> > > > > -	if(opt == 'c' || opt == 'd') create_table();
> > > > > +	if(opt == 'c' || opt == 'd' || opt == 'p') create_table();
> > > > >  
> > > > > -	if (dump_format){
> > > > > +	if (dump_format && opt != 'p'){
> > > > >  		if ( schema )
> > > > >  		{
> > > > >  			printf("COPY \"%s\".\"%s\" %s FROM stdin;\n",
> > > > > @@ -555,8 +555,9 @@
> > > > >   *   MAIN SHAPE OBJECTS SCAN
> > > > >   * 
> > > > >   **************************************************************/
> > > > > -	for (j=0;j<num_entities; j++)
> > > > > -	{
> > > > > +	if (opt != 'p') { /*only if we do not have prepare mode*/
> > > > > +	    for (j=0;j<num_entities; j++)
> > > > > +	    {
> > > > >  		//wrap a transaction block around each 250 inserts...
> > > > >  		if ( ! dump_format )
> > > > >  		{
> > > > > @@ -646,12 +647,12 @@
> > > > >  		
> > > > >  		SHPDestroyObject(obj);	
> > > > >  
> > > > > -	} // END of MAIN SHAPE OBJECT LOOP
> > > > > +	    } // END of MAIN SHAPE OBJECT LOOP
> > > > >  
> > > > >  
> > > > > -	if ((dump_format) ) {
> > > > > +	    if ((dump_format) ) {
> > > > >  		printf("\\.\n");
> > > > > -
> > > > > +	    }
> > > > >  	} 
> > > > >  
> > > > >  	free(col_names);
> > > > > @@ -660,7 +661,7 @@
> > > > >  		if ( schema )
> > > > >  		{
> > > > >  			printf("\nALTER TABLE ONLY \"%s\".\"%s\" ADD CONSTRAINT \"%s_pkey\" PRIMARY KEY (gid);\n",schema,table,table);
> > > > > -			if(j > 1)
> > > > > +			if(j > 1 && opt != 'p')
> > > > >  			{
> > > > >  				printf("SELECT setval ('\"%s\".\"%s_gid_seq\"', %i, true);\n", schema, table, j-1);
> > > > >  			}
> > > > > @@ -668,7 +669,7 @@
> > > > >  		else
> > > > >  		{
> > > > >  			printf("\nALTER TABLE ONLY \"%s\" ADD CONSTRAINT \"%s_pkey\" PRIMARY KEY (gid);\n",table,table);
> > > > > -			if(j > 1){
> > > > > +			if(j > 1 && opt != 'p'){
> > > > >  				printf("SELECT setval ('\"%s_gid_seq\"', %i, true);\n", table, j-1);
> > > > >  			}
> > > > >  		}
> > > > > @@ -783,13 +784,14 @@
> > > > >  	fprintf(stderr, "OPTIONS:\n");
> > > > >  	fprintf(stderr, "  -s <srid>  Set the SRID field. If not specified it defaults to -1.\n");
> > > > >  	fprintf(stderr, "\n");
> > > > > -	fprintf(stderr, "  (-d|a|c) These are mutually exclusive options:\n");
> > > > > +	fprintf(stderr, "  (-d|a|c|p) These are mutually exclusive options:\n");
> > > > >  	fprintf(stderr, "      -d  Drops the table , then recreates it and populates\n");
> > > > >  	fprintf(stderr, "          it with current shape file data.\n");
> > > > >  	fprintf(stderr, "      -a  Appends shape file into current table, must be\n");
> > > > >  	fprintf(stderr, "          exactly the same table schema.\n");
> > > > >  	fprintf(stderr, "      -c  Creates a new table and populates it, this is the\n");
> > > > >  	fprintf(stderr, "          default if you do not specify any options.\n");
> > > > > +	fprintf(stderr, "      -p  Prepare mode, only creates the table\n");
> > > > >  	fprintf(stderr, "\n");
> > > > >  	fprintf(stderr, "  -g <geometry_column> Specify the name of the geometry column\n");
> > > > >  	fprintf(stderr, "     (mostly useful in append mode).\n");
> > > > > @@ -1372,7 +1374,7 @@
> > > > >  	int curindex=0;
> > > > >  	char  *ptr;
> > > > >  
> > > > > -	while ((c = getopt(ARGC, ARGV, "kcdaDs:g:iW:w")) != EOF){
> > > > > +	while ((c = getopt(ARGC, ARGV, "kcdapDs:g:iW:w")) != EOF){
> > > > >                 switch (c) {
> > > > >                 case 'c':
> > > > >                      if (opt == ' ')
> > > > > @@ -1392,6 +1394,12 @@
> > > > >                      else
> > > > >                           return 0;
> > > > >                      break;
> > > > > +	       case 'p':
> > > > > +                    if (opt == ' ')
> > > > > +                         opt ='p';
> > > > > +                    else
> > > > > +                         return 0;
> > > > > +                    break;
> > > > >  	       case 'D':
> > > > >  		    dump_format =1;
> > > > >                      break;
> > > > 
> > > > > _______________________________________________
> > > > > postgis-devel mailing list
> > > > > postgis-devel at postgis.refractions.net
> > > > > http://postgis.refractions.net/mailman/listinfo/postgis-devel
> > > > 
> > > > _______________________________________________
> > > > postgis-devel mailing list
> > > > postgis-devel at postgis.refractions.net
> > > > http://postgis.refractions.net/mailman/listinfo/postgis-devel
> > > _______________________________________________
> > > postgis-devel mailing list
> > > postgis-devel at postgis.refractions.net
> > > http://postgis.refractions.net/mailman/listinfo/postgis-devel
> > 
> 
> 
> 
> > _______________________________________________
> > postgis-devel mailing list
> > postgis-devel at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-devel
> 
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel

-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/x-pkcs7-signature
Size: 1452 bytes
Desc: S/MIME Cryptographic Signature
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20050406/2bda1602/attachment.bin>


More information about the postgis-devel mailing list