postgresql:pgsqlnotes [beOpen

Postgresql online tutorial

Online guide by Cuneo Linux Group (in italiano)

Manage DB in postgresql

su -
su - postgres
createcsrdb <database>
dropdb <database>
pg_dump -x <database> > outfile.txt
psql -e <database> < outfile.txt

Prepare the DB in postgis

su -
su - postgres
/usr/local/pgsql/bin/createlang plpgsql strade  (createlang non è nel path quindi andare a prenderselo)
psql -f /home/install/mapserver/postgis-1.1.3/lwpostgis.sql -d strade
psql -f /home/install/mapserver/postgis-1.1.3/spatial_ref_sys.sql -d strade

Create index

It’s definitely better create an index on geometry field as show in benchmark page, here how do it

CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] GIST_GEOMETRY_OPS );
es CREATE INDEX gist_areeurbane ON areeurbane USING GIST ( the_geom GIST_GEOMETRY_OPS );

If you use pmapper or, anyway, need to perform search on a field it’s better to add another index on the search field. Something like that:

CREATE INDEX [indexname]
ON [tablename]
USING btree
([searchfield]);

Where [searchfield] is unique field to be used as key for search.

When finished

VACUUM ANALYZE [table_name] [column_name];

Data loader from CSV

psql -d <database> -U <user> -W -c "copy residenti from '/home/luca/tmp/Lombardia.csv' using delimiters ';'";

Data dumper

pg_dump -i -h <host> -p <port> -U postgres -F p -O -o -v -f "outputfile.sql" -t <table> <database>

Postgis Loader e Dumper

shp2pgsql -W "ISO-8859-15" shaperoads myschema.roadstable > roads.sql 
psql -d roadsdb -U postgres -f roads.sql
iconv -f "ISO-8859-15" -t "UTF-8" roads.sql (if you need it!)

oppure in una riga

shp2pgsql -W "ISO-8859-15" shaperoads myschema.roadstable | psql -d roadsdb

`-d`		Drops the database table before creating a new table with the data in the Shape file.
`-a`		Appends data from the Shape file into the database table. Note that to use this option to load multiple files, the files must have the same attributes and same data types.
`-c`		Creates a new table and populates it from the Shape file. This is the default mode.
`-p`		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.
`-D`		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.
`-s` <SRID>		Creates and populates the geometry tables with the specified SRID.
`-k`		Keep idendifiers case (column, schema and attributes). Note that attributes in Shapefile are all UPPERCASE.
`-i`		Coerce all integers to standard 32-bit integers, do not create 64-bit bigints, even if the DBF header signature appears to warrant it.
`-I`		Create a GiST index on the geometry column.
`-w`		Output WKT format, for use with older (0.x) versions of PostGIS. Note that this will introduce coordinate drifts and will drop M values from shapefiles.
`-W <encoding>`		Specify encoding of the input data (dbf file). When used, all attributes of the dbf are converted from the specified encoding to UTF8. The resulting SQL output will contain a SET CLIENT_ENCODING to UTF8 command, so that the backend will be able to reconvert from UTF8 to whatever encoding the database is configured to use internally.

Note that -a, -c, -d and -p are mutually exclusive.