[Gdal-dev] 2004 Tiger with new PostGIS

[David Blasby]

I'm trying to load the new Tiger data into Postgis, and I'm having troubles.

I solved the "constraints" on tables problem - just run the CREATE
FUNCTION statement below - it replaces the addgeometrycolumn function
so that it doesnt add constaints.  Hacky, but it works.

The second problem is with OGR - the altNames table isn't correctly
forming arrays:

LOG:  statement: INSERT INTO "altname" ("module", "tlid", "rtsq",
"feat") VALUES ('TGR11001', 76224848, 1, '{696,702')

the '{696,702' should have a "}" at the end - '{696,702}'.  I dont
want to just ignore errors since I'll lose everything inside the
transaction.

Is it possible to get ogr to spit out sql instead of actually hitting
the database?  That way I can write a perl script to fix the problems.

Anyone else trying to load the 2004 data?

CREATE OR REPLACE FUNCTION
AddGeometryColumn(varchar,varchar,varchar,varchar,integer,varchar,integer)
	RETURNS text
	AS 
'
DECLARE
	catalog_name alias for $1;
	schema_name alias for $2;
	table_name alias for $3;
	column_name alias for $4;
	new_srid alias for $5;
	new_type alias for $6;
	new_dim alias for $7;

	rec RECORD;
	schema_ok bool;
	real_schema name;

	fixgeomres text;

BEGIN

	IF ( not ( (new_type =''GEOMETRY'') or
		   (new_type =''GEOMETRYCOLLECTION'') or
		   (new_type =''POINT'') or 
		   (new_type =''MULTIPOINT'') or
		   (new_type =''POLYGON'') or
		   (new_type =''MULTIPOLYGON'') or
		   (new_type =''LINESTRING'') or
		   (new_type =''MULTILINESTRING'') or
		   (new_type =''GEOMETRYCOLLECTIONM'') or
		   (new_type =''POINTM'') or 
		   (new_type =''MULTIPOINTM'') or
		   (new_type =''POLYGONM'') or
		   (new_type =''MULTIPOLYGONM'') or
		   (new_type =''LINESTRINGM'') or
		   (new_type =''MULTILINESTRINGM'')) )
	THEN
		RAISE EXCEPTION ''Invalid type name - valid ones are: 
			GEOMETRY, GEOMETRYCOLLECTION, POINT, 
			MULTIPOINT, POLYGON, MULTIPOLYGON, 
			LINESTRING, MULTILINESTRING,
			GEOMETRYCOLLECTIONM, POINTM, 
			MULTIPOINTM, POLYGONM, MULTIPOLYGONM, 
			LINESTRINGM, or MULTILINESTRINGM '';
		return ''fail'';
	END IF;

	IF ( (new_dim >4) or (new_dim <0) ) THEN
		RAISE EXCEPTION ''invalid dimension'';
		return ''fail'';
	END IF;

	IF ( (new_type LIKE ''%M'') and (new_dim!=3) ) THEN

		RAISE EXCEPTION ''TypeM needs 3 dimensions'';
		return ''fail'';
	END IF;


	IF ( schema_name != '''' ) THEN
		schema_ok = ''f'';
		FOR rec IN SELECT nspname FROM pg_namespace WHERE text(nspname) =
schema_name LOOP
			schema_ok := ''t'';
		END LOOP;

		if ( schema_ok <> ''t'' ) THEN
			RAISE NOTICE ''Invalid schema name - using current_schema()'';
			SELECT current_schema() into real_schema;
		ELSE
			real_schema = schema_name;
		END IF;

	ELSE
		SELECT current_schema() into real_schema;
	END IF;



	-- Add geometry column

	EXECUTE ''ALTER TABLE '' ||

		quote_ident(real_schema) || ''.'' || quote_ident(table_name)



		|| '' ADD COLUMN '' || quote_ident(column_name) || 
		'' geometry '';


	-- Delete stale record in geometry_column (if any)

	EXECUTE ''DELETE FROM geometry_columns WHERE
		f_table_catalog = '' || quote_literal('''') || 
		'' AND f_table_schema = '' ||

		quote_literal(real_schema) || 



		'' AND f_table_name = '' || quote_literal(table_name) ||
		'' AND f_geometry_column = '' || quote_literal(column_name);


	-- Add record in geometry_column 

	EXECUTE ''INSERT INTO geometry_columns VALUES ('' ||
		quote_literal('''') || '','' ||

		quote_literal(real_schema) || '','' ||



		quote_literal(table_name) || '','' ||
		quote_literal(column_name) || '','' ||
		new_dim || '','' || new_srid || '','' ||
		quote_literal(new_type) || '')'';

	-- Add table checks

	SELECT fix_geometry_columns() INTO fixgeomres;

	return 

		real_schema || ''.'' || 

		table_name || ''.'' || column_name ||
		'' SRID:'' || new_srid ||
		'' TYPE:'' || new_type || 
		'' DIMS:'' || new_dim || ''\n '' ||
		''geometry_column '' || fixgeomres;
END;
'
LANGUAGE 'plpgsql' VOLATILE STRICT; -- WITH (isstrict);