[postgis-users] How to configure Postgis/Postgresql to use with QGIS

[pcreso at pcreso.com]

Hi Daniel,
The docs are a good place to start, but here is the simple version, from the command line...

If you have got Postgres installed OK, you need to create a new database, then install Postgis into it, so I'm assuming you have a working Postgres superuser account you can use.  Then:

# create the new, empty database
createdb test_gis

# add support for the plpgsql language. Not always needed for v9+ of Postgres, but it # won't hurt anything to try it anyway.
createlang test_gis plpgsql

# find where on your system the postgis.sql & spatial_reference_sys.sql files are
# run these in your new database

psql -d test_gis -f postgis.sql
psql -d test_gis -f spatial_ref_sys.sql

# You now have a working Postgis database.
# connect to the database
psql -d test_gis

# create a simple table there. QGIS likes integer primary keys, this gives it one
create table test_tab (id serial primary key, name varchar(12));

# add a lat/long geometry column to the table
select addgeometry('','test_tab','geom',4326,'POINT',2);

# add a couple of points to the table
insert into test_tab values (default,'point 1',ST_setsrid(makepoint(179, -45),4326));
insert into test_tab values (default,'point 2',ST_setsrid(makepoint(179.2, -45.2),4326));

# see that they exist:
select * from test_tab;
select id, name, ST_astext(geom) as geom from test_tab;

# exit postgres
\q


To open the table & see the points plotted in QGIS you have a bit of configuration to do. By default a Postgres database is installed securely, so external applications & remote computers cannot connect to it.

The instructions here open the database up to anyone, so it is pretty insecure. If you want to address this, read the Postgres docs...

Shut down your Postgres server. How you do this depends on your operating system & version.

find the file "pg_hba.conf". It contains the access settings.
edit this (you may need admin rights) & set all users from all IP addresses for all databases to "trust", as described in the file

find the file postgresql.conf
edit this & set "listen_addresses" to "*" as described in the file ( = all)

restart the Postgresql service, it will now have an open access configuration, as you just specified.

You can now fire up QGIS on a local or remote system & connect to the database test_gis on the specified server (if same system as the database, the host is called localhost). QGIS will show you the table test_tab & adding this should show your two points.

HTH,

  Brent Wood

--- On Thu, 12/29/11, Daniel Montenegro <dmontenegro84 at gmail.com> wrote:

From: Daniel Montenegro <dmontenegro84 at gmail.com>
Subject: [postgis-users] How to configure Postgis/Postgresql to use with QGIS
To: postgis-users at postgis.refractions.net
Date: Thursday, December 29, 2011, 2:37 PM

Hi there! I've installed Postgis but I'm not being able to create a "spatial" table with a geometry column in Postgresql. Actually, I think I still didn't get all the steps to configure postgis in postgresql.
 I really need some help! Thanks a lot,  Daniel

-----Inline Attachment Follows-----

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111228/1b32cb1e/attachment.html>