Subject: [Thuban-list] proposal changes on postgisdb.py

From: [email-masked] (Luiko Czub)
Date: Sun, 25 Jan 2004 23:22:40 +0100

Moin folks,

I had several problems, viewing my data stored by 0.8 postgis DB in thuban
1.0.0.  You can find in the attachment the results of my hack, to solv these
problems.

I had following problems:

1.) postgisdb.py expect, that the postgis tables has a field 'gid'. I don't know
why, so I replaced the SELECT-Statements with the default rwo-id Field OID.
Why are you expecting the field 'gid' ?
All changes for this are marked with the comment 'SUBSTITUTE GID WITH OID '

2.) My geom datas are stored in postgis DB for a SRID different from
  '-1'. psgl raises the traceback 'Operation on two GEOMETRIES with different
SRIDs',
  when PostGISShapeStore.ShapesInRegion() searches for geometries. I created a
  new Method PostGISShapeStore.Srid() and .ShapesInRegion() call this know
  instead of '-1'. 
  Changes for this are marked with the comment 'SRID != -1'
  Attention: I'm not shure, that the projection of geomeries, imported from
  postgis, will be correct.

3.) I'm working with LINESTRING Geometries in postgis and there was no mapping
  for this geometry-type.
  Changes for this are marked with the comment 'ADD geomtype LINESTRING'
   
4.) My postgis tables sometimes has more than one geometry-field, but Thuban
  could not handle this. I made several changes on PostGISTable,
  SessionLoader.start_dbshapesource() and  SessionSaver.write_data_containers() 
  with the result, that a postgis table connections now needs the explicit
  geomcolumn name for the init(). 
  Changes for this are marked with the comment 'Init with geom_column_name'

Example for a changed session-file:
    <dbconnection dbtype="postgis" dbname="dfktest" port="" host="localhost"
user="mapserv" id="D1098406668"/>
    <dbshapesource geomcolumn="flaeche_geom" tablename="dfk_gebaeude"
id="D1098407052" dbconn="D1098406668"/>
    <dbshapesource geomcolumn="linie_geom" tablename="dfk_gebaeude"
id="D1098405644" dbconn="D1098406668"/>
    <map title="unbenannte Karte">
        <layer title="dfk_gebaeude.flaeche_geom" stroke_width="1"
shapestore="D1098407052" visible="true" stroke="#000000" fill="None"/>
        <layer title="dfk_gebaeude.linie_geom" stroke_width="1"
shapestore="D1098405644" visible="true" stroke="#000000" fill="None"/>
    </map>

What are your experiences with the postgis-connection? 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. 

If you want to test my changes, replace the files 
- load.py, save.py and postgisdb.py
in the directory
- .../lib/thuban/Thuban/Model

PS: sorry for my bad english, hope, you hav eunderstand the main problems

-- 

Gruß Luiko Czub

______________________________________________________
Luiko Czub
Weberstraße 1            Tel.:  ++49-761/8098-240
D-79249 Merzhausen       Email: [email-masked]
______________________________________________________

---

From: Frank Koormann <[email-masked]>
Date: Tue, 27 Jan 2004 09:27:31 +0100

Moin,

> 1.0.0.  You can find in the attachment the results of my hack, to solv these

Luiko, thank you very much for your proposal. I have created a ticket
for your proposal in the bugtracker:

        http://intevation.de/rt/webrt?serial_num=2299

We will have a deeper look at your proposal and comment on it later.

Best regards,

        Frank

-- 
Frank Koormann                             <[email-masked]>
 Professional Service around Free Software       (http://intevation.net/)
 FreeGIS Project                                 (http://freegis.org/)

---

From: Bernhard Herzog <[email-masked]>
Date: Wed, 11 Feb 2004 14:58:50 +0100

[email-masked] (Luiko Czub) writes:

> 1.) postgisdb.py expect, that the postgis tables has a field 'gid'. I
> don't know why,

The code was developed originally in an attempt to be as close to the
OGC Simple Features for SQL specification as possible and the gid came
from there IIRC.  I checked that a bit better now, and while the
specification mentions a gid in some places AFAICT it doesn't actually
specify that it must be used in the fashion that Thuban currently
expects.

> so I replaced the SELECT-Statements with the default rwo-id Field OID.

I'm not sure simply using OID instead of gid is the right way to fix it.
It's PostgreSQL specific and even PostgreSQL doesn't guarantee that it
is there since it's possible to create tables without an OID column.

The best way to handle it would be to let the user select the id column
to use, at least when there are a several to choose from (not sure
whether there's a good way for Thuban to figure out which columns are
ints with a unique and not NULL constraint).  This would require a
change to the file format, so would have to wait for 1.1.

For 1.0, using the OID instead of the gid is a good solution, I think.
It's definitely better than relying on gid and tables that don't have
oids are probably quite uncommon.

>   Changes for this are marked with the comment 'SRID != -1'

This is now in CVS.  The main difference to you patch is that there is
no Srid method.

>   postgis, will be correct.

Yes, that might be a problem.  Ideally Thuban should determine the
projection used by the data in the database if the srid is != -1 so that
it can reproject properly if necessary.  This shouldn't be too hard in
the case of Postgis because like Thuban itself it uses the proj library
for projections and a suitable paramter list is the SPATIAL_REF_SYS
table.  It's not all that easy in the current Thuban architecture
because the projection is associated with the layer and not the data as
it probably should be.

>   could not handle this.

Some parts of this are now in CVS.  The PostGISShapeStore class can
handle table with multiple geometry columns.  It's somewhat different
from you patch in that the name of the geometry column is an extra
paramter to PostGISShapeStore and only required if the table has more
than one geometry column.  This is for backwards compatibility.

Also for compatibility reasons the other changes are not yet in CVS
because they mean changes to the file format which we want to avoid for
the stable 1.0 branch.  Once we start on a developement branch we'll
have a look at that again.

There's one incompatibility in current CVS in comparison to 1.0.  Thuban
could handle tables with multiple geometry columns in some cases,
e.g. both columns have the same geometry type and srid -1.  Thuban would
more or less arbitrarily choose which of the columns to use, though, so
it wasn't all that useful.  Trying to access such a layer will produce
an error message now.  This means that loading .thuban files with such
layers will fail now, but since it wasn't really supported anyway, this
is not much a problem, I think.

> 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.  This
wouldn't require file format changes, so it could easily be added in the
stable branch.

   Bernhard

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

---

From: Bernhard Herzog <[email-masked]>
Date: Thu, 12 Feb 2004 11:44:22 +0100

Bernhard Herzog <[email-masked]> writes:

> 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

---

From: [email-masked] (Luiko Czub)
Date: Thu, 12 Feb 2004 22:32:32 +0100

Moin Bernhard,

> > can try to open tables that are not in the list thuban shows you.

Cause I am a friend of tables and views with more than one geometryfield, 
please enlarge this idea with a non-mandadory field for the geometry-field name.
And you also need an entry for the unique-id field, i think. This might be a
mandatory
entry, isn't?

> this (by searching the geometry_columns table) wouldn't work for views.

That is, what I mean with 'redisgn', if you whiches to handle tables and views
in the some way. 

> occurs in practice but it's possible:

In my opinion, the main interest to connect from a viewer like Thuban to views
is, using datas from non-spatial tables for the classification of
geometry-tables.
So you would use the view, to add additional data-attributes to the
geometry-selection and 
not to mix several geometry-types. Here is a example for a view, I am working
with: 
CREATE VIEW edbs_linien_view AS 
	SELECT	f.folie, f.linienart, f.objnr_links, f.objnr_rechts, 
		f.objteilnr_links, f.objteilnr_rechts, f.gid , 
		l.nummerierungsbezirk, l.art_der_geometrie, l.linie_geom
	FROM 	edbs_linienfunktion AS f 
	JOIN	edbs_linie AS l 
	ON 	f.owner_id = l.gid;

Kind of association is: edbs_linie 1:n edbs_linienfunktion
Geometryfield is : edbs_linie.linie_geom (LINESTRING)
unique-id field is : f.gid

You could create the situation with several geometrytypes in one column also
with a 'normal' table, if the geometry column is of the type 'GEOMETRY'. But
this is in my opinion a
bad datamodel design , if someone did not know, with what kind of datas he is
working. And do you realy
want to consider all possible bad datamodel designs? Don't make the mistake to
build the
EierLegendeWollMilchSau.

I think, you would make a big step forward for the classification of datas, if
there is a posssibility for connecting views in the way, you describe above.
 
> ....

Maybe, there must be some rules for connecting views (like the rule 'unique-id
field gid' in thuban 1.0.0):
- all entries for a geometry-column must be of the same geometrytype and srid
- there must be one row of the view, where the geometry-column has an entry

-- 

Gruß Luiko Czub

---

From: Bernhard Herzog <[email-masked]>
Date: Fri, 13 Feb 2004 17:17:50 +0100

[email-masked] (Luiko Czub) writes:

> Cause I am a friend of tables and views with more than one
> geometryfield, please enlarge this idea with a non-mandadory field for
> the geometry-field name.

Sure.  That's part of a proper fix for the multiple geometry columns per
table/view problem, though, and thus pretty independent of how views can
be supported in Thuban.

> And you also need an entry for the unique-id
> field, i think. This might be a mandatory entry, isn't?

Yes.  This is yet another independent issue, though.

Do you know whether there's a way to determine which of the columns
might be suitable, BTW?  Figuring out which of the columns contain ints is
easy and would suffice for a start, but it would be nice to know which
of those columns also have unique and not null constraints.

> not to mix several geometry-types.
[snip example]

Thanks for the example.  At this point we should obviously concentrate
on support for the common use cases.  For that we need feedback like
yours about how postgis is used in practice and how well Thuban supports
it.

> But
> want to consider all possible bad datamodel designs?

"Consider" perhaps, as long as we don't need to implement them :)

However, it's usually also good to keep track of why an implementation
is limited in which ways.  Implementing features that will not be used
is pointless, but thinking about what would be possible, what of that
might be useful and what is necessary can often make a design better
even if you only implement the minimum of what you need at first.  At
the very least it leads to a better understanding of the problem domain.

> Don't make the mistake to build the EierLegendeWollMilchSau.

That's unlikely to happen as we don't have the resources for that :)

> - there must be one row of the view, where the geometry-column has an entry

That reminds me that Thuban doesn't yet support rows where the geometry
column is NULL.

Another requirement is that there must be a column that can be used as
an ID.

   Bernhard