Convert Shapefiles to SQL

The PostgreSQL /bin folder contains an executable capable to perform the conversion:

shp2pgsql shape_file_name myschema.dbtable_name > output_file.sql

psql -d database_name -f output_file.sql

One command conversion and upload to the PostgreSQL can be done all in one step using UNIX pipes:

shp2pgsql shape_file_name myschema.dbtable_name | psql -d database_name

Create a spatial database with PostGIS

Ensure your PostgreSQL database is correctly installed and running. Do do it just check the connection using PgAdmin III client. If an error arrises, probabily your server isn’t running. nu Start it using the “Start server” entry in the start menu of your operating system or else on Ubuntu this will work (if the server was installed as in this previous post)

sudo /opt/PostgreSQL/8.4/installer/server/startserver.sh

1) Open the Databases tree item and have a look at the available databases. The postgres database is the user database for the default postgres user and is not too interesting to us. The template_postgis database is what we are going to use to create spatial databases. Right-click on the Databases item and select New Database.

Note: If you receive an error indicating that the source database (template_postgis) is being accessed by other users, this is likely because you still have it selected. Right-click on the PostgreSQL 8.4 item and select Disconnect. You can then double-click the same item to reconnect and try again.

2) Fill in the New Database form with the info below and click OK.

Name: your_database_name

Owner: postgres

Encoding: UTF8

Template: template_postgis

.

3) Select the new database and click on the SQL query button (or go to Tools > Query Tool).

4) Enter the following query into the query text field:

SELECT postgis_full_version();

5) Click the play button in the toolbar (F5 will work also) to execute the query. The query will return a string that confirms that PostGIS is properly enabled in the database.

Information source/

Installing PostgeSQL and PostGIS

This is a simple method I found to install postgres and postgis in ubuntu/windows. Browse to this url http://www.postgresql.org/download/ and in “Binary Packages” you may choose the operating system where you will be using the server. Choosing windows or linux you will download an executable, but the problem in linux you will have to chmod it in order to use it as one. This will do the trick using the terminal:

  chmod 700 filename.bin

and that’s it. The binary file can now be run:

  sudo ./filename.bin

This will show a GUI installer interface where you will be able to define the root password. The final process will be the selection of addons like database connectors, web components to work with, wikis and the spatial extension we are looking for – postgis. First select the server already running in your OS platform:

In the next screen be sure to check the PostGIS extension.

The installer will then download and install the selected extensions. Open PgAdmin III and be sure that a database called template_postgis exists.

That was a peace of cake… the next post will be about creating a georeferenced enabled database.