I'm sure this has been asked many times but haven't found a definitive or
consensus answer...

Is there a postgis client that supports spatial, ad hoc queries and returns
those results to a map?  This would include simple (and more complex) SELECT
statements but also other queries with spatial results, such as finding
nearest neighbors, intersect, union, etc...

The best idea I found was to create a view from the ad hoc query and then
use a desktop GIS to display that view.  You'd need to update the view (and
refresh the GIS screen) to run a new query.   I know QGIS (and other FOSS
GIS packages) allows you to create a definition query (a where clause to
subset the layer) and does support database views.  I haven't seen a place
where QGIS supports ad hoc queries.  Searching the web, I did find
references to some work done using OpenMap libraries back in 2004, and the
mezoGIS package from around 2005/2006.

Does anyone know of a GUI tool to use, hopefully one that works for Postgres
9 and PostGIS 2?  Thanks.

- John

PS - Thanks for the recommendations on the PostGIS in Action book.  I just
purchased it and looking forward to learning what I can.

**************************************************
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL:  http://www.dgs.udel.edu
**************************************************

See RT Sql Layer [1][2] from faunalia plugins repository [3]

[1]  http://www.faunalia.it/qgis/rt_sql_layer.zip
[2]  http://www.faunalia.it/qgis/rt_sql_layer.experimental.zip
[3]  http://www.faunalia.it/qgis/plugins.xml

--strk;

  ()   Free GIS & Flash consultant/developer
  /\    http://strk.keybit.net/services.html

John,
doing Ad hoc queries with it.
http://www.postgresonline.com/journal/index.php?/archives/72[..]
stGIS-Spatial-Ad-Hoc-Queries.html

It works fine with PostgreSQL 9.0, but if you are using 9.0 -- you need to
download the latest JDBC drivers or set your bytea_output to escape - both
are documented in the FAQ
http://www.postgis.org/documentation/manual-svn/PostGIS_FAQ.[..]

Leo http://www.postgis.us


  _____

If you are looking for a Web-based solution, MapServer should do this as wll.

David.

postgis-users mailing list
postgis-users*******/mailman/listinfo/postgis-users

Thanks for the responses.  What you describe in your write up with OpenJump
is exactly what I'm trying to do.  Also, in addition to the QGIS plugins
strk mentions, I just found two more possibilities: PgQuery for QGIS and
Postgis SQL Editor.  Looks like there are some good options available.
Thanks again.

- John

Hi,

Thanks for the good documentation,
Would like to mention an undocumented feature coming with the last
OpenJUMP release.

You can copy the "fence" geometry or the "view extent" in your query  :

button View (Vue in the screenshot) will copy "${view:-1}" where ever
you want in the query (replaced by view extent at execution time)
button Fence (Cadre in the screenshot) will copy "${fence:-1}" where
ever you want in the query (replaced by the fence geometry at execution
time)

It may help to download small parts of large datasets.

Michaël

There are three different prototypes called "PostGIS Terminal" around for a
web based client with a "fixed" PostGIS geodatabase below.

The original PostGIS-Terminal probably is http://openlayers-buch.de/beispiele/chapter-09/postgis-termi[..]  (its
german) then there is  http://www.postgisonline.org/   .

I've enhanced theses ideas:  http://www.gis.hsr.ch/wiki/PostGIS_Terminal  .
It's still in an early stage but I can send the code (html, JavaScript, PHP)
to anyone who is interested to become a beta tester :->
Yours, S.

Thank you Stefan.  Yes, I would be interested in testing your application.
Looks interesting.

- John

**************************************************
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL:  http://www.dgs.udel.edu
**************************************************

Hi Stefan,

I would also be interesting in testing your program. Looks quite promising.

Regards,
LJ

John,

I developed a plugin for PgAdmin III to visualize PostGIS data.
Go to the following link  http://ageoguy.blogspot.com/2010/06/plugin-pgadmin-iii-postg[..]
postgisviewer a plugin for PgAdmin III
on my blog where you can download it and you will find a tutorial (in
french) to install and use it.

You can also consult this link  http://www.postgresonline.com/journal/archives/180-pgAdmin11[..]
Postgresonline journal pgAdmin III plugins postgis

Regards,
Jérôme Rolland

Thanks for the reference.  I love the idea of a map viewer plugin for
pgAdmin.   I wasn't aware of this plugin.  Unfortunately, I could not get it
to work through pgAdmin III 1.12 (error: "pgviewer encountered a problem and
needs to close")   Off-hand, does it require MapWindow6 or .NET 4.0 to be
installed?

- John

**************************************************
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL:  http://www.dgs.udel.edu
**************************************************

John,

MapWindow6 or .NET 4.0 don't need to be installed,

1) just download  http://dl.free.fr/icO8LcHoa  PostGISViewer  and decompress it like follow :
http://old.nabble.com/file/p30902546/directory_postgisviewer[..]  

2) define the binaries directory in PgAdmin III Preferences :
http://old.nabble.com/file/p30902546/preferences_pgadminIII.jpg  

you can see that the PostGisViewer directory is in C:\Program
Files\PostgreSQL\9.0\bin

3) put the lines that follow in the plugins.ini file

;
; PgViewer (Windows)
;
[Separator]
Title=PostGisViewer
Command="$$PGBINDIR\PostGisViewer\PGViewer.exe"  "host=$$HOSTNAME"
"port=$$PORT" "username=$$USERNAME" "password=$$PASSWORD"
"database=$$DATABASE" "schema=$$SCHEMA"  "table=$$TABLE"
Description=PostGIS Viewer
KeyFile=$$PGBINDIR\PostGisViewer\PGViewer.exe
Platform=windows
ServerType=postgresql
Database=Yes
;AppliesTo=database
SetPassword=Yes

4) When you start again PgAdmin you must see PostGisViewer in the Plugins
Menu.

I am using PostgreSQL 9.0 and PgAdmin III 1.12.1 , but i had also tested my
plugin with Postgresql 8.2 /8.4 and PgAdmin III 1.10

regards,
Jérôme Rolland

It's working now.  Great.  Thanks for the help, and the plugin.   I had an
older version of .NET 2.0 on my machine (WinXP x64).  Upgrading to .NET
3.0/3.5 did the trick.

And thanks for the discussion.  With the availability of this plugin
(PostGISViewer), OpenJump, several QGIS plugins, and the PostGIS Terminal
from Stefan, it makes my original question seem silly.

- John