[Thuban-list] proposal changes on postgisdb.py

[Bernhard Herzog]

Bernhard Herzog <bh at intevation.de> writes:

> LCzub at t-online.de (Luiko Czub) writes:
>
>> My big wish is the possibility to connect to a view (possible in JUMP
>> 1.1.1, but JUMP 1.1.1 also doesn't handle multiple geometry fields at
>> one table).  But I think, this would need a redesign of postgisdb.py.
>
> Not necessarily.  I haven't tested it, but it might be enough for a
> start to provide a way in the GUI to type in a table name so that you
> can try to open tables that are not in the list thuban shows you.

Thinking about this a bit more, it wouldn't be as easy as this.  The
problem is that it would be difficult to figure out which srid and
geometry type the table or view uses.  The way Thuban currently does
this (by searching the geometry_columns table) wouldn't work for views.
In fact Thuban more or less expects that all shapes in a shapestore have
the same type (e.g. all polygons or all points but not both).  In a view
that is not necessarily the case.  I don't know whether that actually
occurs in practice but it's possible:


CREATE VIEW foo (geom) AS 
       SELECT CASE gid WHEN 1 THEN Envelope(the_geom) ELSE the_geom END
              FROM roads;

Where roads is a table with the roads from the iceland sample data set [1]
Now, if you do a 

   SELECT * from foo limit 2;

you get two rows, one with a MULTILINESTRING and one with a POLYGON.

Playing around with that a bit, I think we might be able to use e.g.

   SELECT DISTINCT GeometryType(geom) FROM foo;

and 

   SELECT DISTINCT SRID(geom) FROM foo;


to determine the geometry type and srid to use.  If either of the
queries yields more than one row, Thuban would have to refuse to use it
for now (it could be made to work if the geometry types are sufficiently
similar e.g.  LINESTRING and MULTILINESTRING).  I'm not sure what to do
if no rows are returned.

In case someone is interested in playing around with it: I tried the
above in the database created by thuban's test suite.  Inside the test/
subdirectory, after you've run the test suite successfully (which means
among other things that you need to have a suitable postgresql with
postgis installed on the machine), you can start the database server
again for test purposes like this:

   pg_ctl -D temp/postgis/ start

And then connect to it with psql with username postgres or observer.
The database with the iceland data is PostGISStaticTests.  If you've got
a postgres server using the default port number running on the same
machine you may want to choose a different port, of course.

   Bernhard

-- 
Intevation GmbH                                 http://intevation.de/
Skencil                                http://sketch.sourceforge.net/
Thuban                                  http://thuban.intevation.org/