[postgis-users] transform to postgis

[Paul Ramsey]

One other thing, due to the "power of the pipe" you don't need to use 
DBI/DBD to do data loading (I never do).

-- command

perl myscript.pl myfile.txt | psql mydatabase

-- myscript.pl

print qq{
	CREATE TABLE mytable (
		station INTEGER,
		the_geom GEOMETRY
	);
	BEGIN;
};

while(<>) {
	chomp;
	($id,$east,$north) = split /\s+/;
	print qq{
		INSERT INTO mytable VALUES (
		 $id,
		 GeometryFromText('POINT($east $north)',<srid>)
		);
	};
}

print qq{
	CREATE INDEX mytable_oid ON mytable (oid)
	CREATE INDEX mytable_spatial
		ON mytable
		USING GIST ( the_geom );
	END;
};


Paul Ramsey wrote:

> BTW: it is always better practice to create your indexes *after* your 
> bulk load, otherwise you end up doing an index update for every insert 
> (expensive!)
> 
> Ethan Alpert wrote:
> 
>>
>> Ugh...don't know why that got formatted so bad.  This should look
>> better:
>>
>> Something like:
>>
>> use DBI;
>>
>> $dbh =
>> DBI->connect("DBI:Pg:dbname=<dbname>;host=<host>","<user>","<pwd>")
>>                         or die "Could not connect to db\n";
>> $dbh->do("create table mytable (station int, the_geom geometry)");
>> $dbh->do("CREATE INDEX mytable_oid ON mytable (oid)");
>> $dbh->do("CREATE INDEX mytable_spatial ON mytable USING GIST ( the_geom
>> GIST_GEOMETRY_OPS)";
>>
>> while(<>) {
>>     chomp;
>>     ($id,$east,$north) = split /\s+/;
>>     $dbh->do("insert into mytable (station,the_geom) value
>> ($id,GeometryFromText('POINT($east $north)',<srid>))");
>> }
>>
>> %> cat your.dat | ./thisperl.pl