You are here:GeoTux»Geo-Blogs»Geographic databases»SQL console for the pgAdmin plugin: PostGIS viewer

Statistics

Invitados: 58
Usuarios registrados: 3094
Usuarios en línea:
-
Registrados hoy:
-

Register

RSS

Blogs and News:
Recibe las actualizaciones en Geo-Noticias y Geo-Blogs

Get them by e-mail
Recibir Geo-Noticias y Geo-Blogs por e-mail

¿What is this about?

Latest Geo-Tweets

Saturday, 04 February 2012 10:22

SQL console for the pgAdmin plugin: PostGIS viewer

Written by  German Carrillo
Rate this item
(5 votes)

I have added an SQL console to the pgAdmin3 plugin "PostGIS viewer". It allows you to run SQL queries on PostGIS data in order to filter them or execute spatial functions on them.

For the most up-to-date code go to the PostGIS-Layer-Viewer repository at GitHub. You're invited to contribute there, enhancing/optimizing the plugin's code, solving an issue, or reporting bugs you might have found. You could even just say "thanks, I find this useful" with a simple GitHub 'Star'!

--------------------------

The already included SQL query builder seems to be a bit complex when all you need to accomplish is to run a single query. Because of that reason I added the QGIS plugin Fast SQL Layer, which lets you write your query in a simple console.

 

What is Fast SQL Layer?

Fast SQL Layer is a QGIS plugin written by Pablo T. Carreira to run SQL queries for PostGIS and SpatiaLite data. It also comes with its own SQL syntax highlighter. I have edited it a bit for integrating it to the PostGIS viewer.

 

Prerequisites

Python, PyQt4, PyQGIS, libqt4-sql-psql, psycopg2 and pygments.

How to install prerequisites in Windows: See this post, you will also need to install psycopg2 and pygments from OSGeo4W.

How to install prerequisites in Ubuntu/Linux: For QGIS, see downloads official page. For the other programs/libs use apt-get or Synaptic.

 

Installation

Once you have the prerequisites installed, all you need to do is to extract one ZIP file (download the zip file here) into the appropriate path (on Windows: C:/Archivos de programa/PostgreSQL/9.1/bin/ whereas on Ubuntu/Linux: /usr/bin/) as well as edit the pgAdmin plugins.ini file.

 

On Ubuntu/Linux you will need the sudo command to extract and set the 755 permissions to the extracted files, this way:

  sudo unzip postgis_viewer.zip -d /usr/bin/ 
sudo chmod 755 /usr/bin/postgis_viewer/ -R

 

Additionally, copy the next lines to /usr/share/pgadmin3/plugins.ini (if the file does not exist, you have to create it):

Title=View PostGIS layer
Command=$$PGBINDIR/postgis_viewer/postgis_viewer.py -h $$HOSTNAME -p $$PORT -U $$USERNAME -W $$PASSWORD -d $$DATABASE -s $$SCHEMA -t $$OBJECTNAME
Description=View PostGIS layer
KeyFile=$$PGBINDIR/postgis_viewer/postgis_viewer.py
Platform=unix
ServerType=postgresql
Database=Yes
SetPassword=Yes


On Windows edit C:/Archivos de programa/PostgreSQL/9.1/pgAdmin III/plugins.ini adding the following lines:

Title=View PostGIS layer
Command="$$PGBINDIR\postgis_viewer\postgis_viewer.bat" -h "$$HOSTNAME" -p "$$PORT" -U "$$USERNAME" -W "$$PASSWORD" -d "$$DATABASE" -s "$$SCHEMA" -t "$$OBJECTNAME"
Description=View PostGIS layer
KeyFile=$$PGBINDIR\postgis_viewer\postgis_viewer.bat
Platform=windows
ServerType=postgresql
Database=Yes
SetPassword=Yes

 

The postgis_viewer.bat file sets the OSGeo4W environment variables (Qt, Python, QGIS) for allowing the execution of postgis_viewer.py The .bat file assumes OSGeo4W is installed in C:/ and PostgreSQL can be found in C:/Archivos de programa/PostgreSQL/9.1/ You should check that file if you get some troubles while running the plugin.

 

How can I use it?

You can use the PostGIS viewer from a command line (like in this post) as well as from pgAdmin. If the installation process went well, to see one of your spatial tables you need to select it in the pgAdmin's object browser, click in Plugins and then click in View PostGIS layer, this way:

 

Now you can write your SQL queries in the Query tab. Don't forget to provide an id field name and a geometry field name to get a well defined (valid) layer. If you get a new layer from your query but cannot see any geometry on the map, you probably need to adjust the id field name.

 

 

 

Have fun!

 

===============================

Update (2014.12.12), Version 1.6:

  • v.1.6 supports raster layers stored in PostGIS (QGIS >= v.2.6)
  • New tab About

Raster layer stored in PostGIS

 

Last modified on Tuesday, 12 April 2016 16:05

comments  

 
0 # Updatestuxman 2012-03-20 19:40
(English below)

Este mensaje es solo para informarles de un par de actualizaciones en el plugin:
* Botón "Zoom Full Extent".
* Soporte del tipo 'geography' de PostGIS.

El enlace de descarga es el indicado en el post.

---------
This is just to inform you about a couple of updates in the plug-in:
* "Zoom Full Extent" button.
* Support for 'geography' type.
The URL for downloading the new version is the indicated in the post.

Saludos,

Tuxman
Reply | Reply with quote | Quote
 
 
0 # Actualizaciónjhcanof 2012-07-24 19:39
Hola Germán,
Tengo Debian testing, con PostgreSQL 9 y Postgis 1.5.x, pgadmin3 1.14.2.

En el archivo plugins.ini Faltaría la linea "KeyFile=$$PGBINDIR/postgis_viewer/postgis_viewer.py"

quedando así:

Title=View PostGIS layer
Command=$$PGBINDIR/postgis_viewer/postgis_viewer.py -h $$HOSTNAME -p $$PORT -U $$USERNAME -W $$PASSWORD -d $$DATABASE -s $$SCHE$
Description=View PostGIS layer
KeyFile=$$PGBINDIR/postgis_viewer/postgis_viewer.py
Platform=unix
ServerType=postgresql
Database=Yes
SetPassword=Yes

De esa forma me funciona a la perfección

Muchas gracias por tu ayuda.
Reply | Reply with quote | Quote
 
 
0 # Re:tuxman 2012-07-24 21:38
Gracias jhcanof,

efectivamente para versiones de pgAdmin > 1.12.x se requiere esa línea extra en el archivo plugins.ini

Saludos,

Tuxman
Reply | Reply with quote | Quote
 
 
0 # shp2psql and psqlpamella 2012-11-09 01:11
Dear tuxman,

I have a problem with executing the above command. Am creating an application using openlayers, geoserver and wfs. I set up the geoserver and published my maps but i cannot edit the wfs. i actually cant visualise this icon which is part of my code and should be on the map.
I realised the problem could be with the import and export of the shapefiles to transform them in an acceptable form for wfs.

I used the pgAdminloader which was successful but i cannot execute the psql command. i get a response of access denied, even when i include the right password. Iam using windows 7.

Please advice on what i could be doing wrongly.

regards
Reply | Reply with quote | Quote
 

Add comment


Security code
Refresh

 

On-line users

Latest Geo-Forums

More Topics »

Latest Comments