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

[pcreso at pcreso.com]

Hi Daniel,

There are two common ways to do this, using ogr2ogr or shp2pgsql.

I suggest you try shp2pgsql as it has a simpler command line. It is a utility bundled with Postgis. If you run it with no arguments it provides help with the syntax.

The default ouput goes to stdout, and comprises a set of SQL commands to create & populate a Postgis table to manage the same content as the shapefile.

You can save the output as a file, then load it: psql -d <db> -f <file> 
or you can pipe the shp2pgsql ouput directly into psql: 
shp2pgsql ..... | psql -d <db>

If Postgres is set up as I suggested in my last post, then QGIS & SPIT should work OK as well. 


Cheers,

  Brent


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

From: Daniel Montenegro <dmontenegro84 at gmail.com>
Subject: Re: [postgis-users] How to configure Postgis/Postgresql to use with QGIS
To: pcreso at pcreso.com, sindile.bidla at gmail.com, "postgis-users" <postgis-users at postgis.refractions.net>
Date: Friday, December 30, 2011, 4:54 AM

Thanks Sindile and Brent,
 
Actually, I've unnistalled Postgres (the latest version - 9.xx) and downloaded an older one (8.1.4 - I guess). Then I used STACK BUILDER to install PostGIS on Postgres.
 
Now let me ask you something else:
 
Supose that I have a polygon shape - like "MyCyty.shp", how can I export it to Postgres via Postgis. I tryed to use the SPIT plugin from QGIS but isn't working right yet... Do I have to create some spatial database in Postgres before trying to export it in QGIS?

 
Thanks a lot!
 
 
Daniel

P.S.: I'm using PGADMIN.

2011/12/29 <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/20111229/039123cd/attachment.html>