[postgis-devel] pgsql2shp dump optimization and change

[Jeremy Palmer]

Hi,

I was just try to dump a shape from postgis that had 30 million rows was
wondering why the initialisation was taking so long. I think I found the
problem: On the geometry type validation pgsql2shp a distinct clause is
being used to aggregate geometry types in the table. In PostgreSQL a group
by aggregation would be must faster. i.e. change

	if ( schema )
	{
		sprintf(query, "SELECT DISTINCT geometrytype(\"%s\") "
			"FROM \"%s\".\"%s\" WHERE NOT geometrytype(\"%s\") "
			"IS NULL", geo_col_name, schema, table,
geo_col_name);
	}
	else
	{
		sprintf(query, "SELECT DISTINCT geometrytype(\"%s\") "
			"FROM \"%s\" WHERE NOT geometrytype(\"%s\") IS
NULL",
			geo_col_name, table, geo_col_name);
	}

To

	if ( schema )
	{
		sprintf(query, "SELECT geometrytype(\"%s\") "
			"FROM \"%s\".\"%s\" WHERE NOT geometrytype(\"%s\") "
			"IS NULL GROUP BY SELECT geometrytype(\"%s\")", 
                  geo_col_name, schema, table, geo_col_name, geo_col_name );
	}
	else
	{
		sprintf(query, "SELECT geometrytype(\"%s\") "
			"FROM \"%s\" WHERE NOT geometrytype(\"%s\") IS NULL
",
			"GROUP BY geometrytype(\"%s\")", 
                  geo_col_name, table, geo_col_name, geo_col_name);
	}

This is a side topic but related: Could it be possible to implement
pgsql2shp to dump a shape file for each geometry class in a geometry table.
Either that or also allow pgsql2shp to take a view or query as source input.

Thanks.