[GRASS-user] v.external pg alternative geometry column

[Moritz Lennert]

On 29/07/13 18:13, António M. Rodrigues wrote:
> Hi Moritz,
>
> Thanks for your reply.
>
> In http://www.gdal.org/ogr/drv_pg_advanced.html, it says:
>
> "Starting with GDAL 1.6.0, the PostgreSQL driver supports accessing
> tables with multiple PostGIS geometry columns. For such a table, there
> will be as many layers reported as the number of geometry columns listed
> for that table in the geometry_columns table."
>
> Now, I have one postgis table with two geometry fields, the_geom and
> the_geo3763. I opened both in qgis to make sure the geometries were
> correctly transformed and everything was in place.
>
> I set a connection with pg from grass7 using db.connect, db.login,
> db.connect -p, db.tables -p (details below).
>
> db.tables returns only one base.geoi1 table (should be 2, since it has 2
> geometry columns, no?).

No, the db.* modules are completely independent of the geometry issues 
in Postgis and do not use gdal/ogr. They are database management modules 
and as such it is correct that db.tables returns one table, as there is 
only one table.

If you want to see which layers are available in a PostGIS database, use 
v.in.ogr -l :

v.in.ogr -l dsn="PG:host=localhost user=marxco password=******** 
dbname=geocontacto"

>
> Then I imported both geometries with v.external. For the second geometry
> I specified the field the_geometry3763.

Note that v.external does not import files, it links external files to 
allow you to work directly with them as if they were imported. However 
you do not get full topology handling. If you really want to import 
files, use v.in.ogr.

>
> Both tables are imported into grass, showing the same geometry (the
> orginal, with crs=20790).
>
> Is this a bug or I'm doing something wrong?
>
[...]
> ----------------------------
> (Mon Jul 29 16:39:23 2013) Command finished (0 sec)
> (Mon Jul 29 16:40:06 2013)
> db.connect driver=pg database=host=localhost,dbname=geocontacto,port=5432
> (Mon Jul 29 16:40:06 2013) Command finished (0 sec)
> ----------------------------
> (Mon Jul 29 16:41:02 2013)
> db.login user=marxco password=********
> The password was stored in file (.grass7/dblogin)

You do not have to change the db.connect and db.login settings for 
importing / linking external files.


> (from postgis)
> select st_srid(the_geom3763) from base.geoi;
> Returns 3763

I suppose that this should read 'from base.geoindex_jan13_v3' ?
Are the two columns in the geometry_columns table/view ?

> -------------------------------------------------------------
> v.external dsn=PG:host=localhost user=marxco password=********
> dbname=geocontacto layer=base.geoindex_jan13_v3.the_geom3763
> output=geoindex2

I suppose that this is result of an attempt via the GUI wizard ?
AFAIK, this should actually not work at all. As the OGR documentation 
states:

"For example, if a table 'foo' has 2 geometry columns 'bar' and 'baz', 2 
layers will be reported : 'foo(bar)' and 'foo(baz)'."

So, your layer should be base.geoindex_jan13_v3(the_geom3763).

However, did you use the ogr connection, or did you use the PostGIS(PG) 
type of source in the v.external GUI wizard ?

In any case, I don't think that the GUI wizard has foreseen this case, 
so please file a bug against the GUI. In the meantime, you can use 
v.external directly, not via the wizard (just type v.external in the 
command console).

I am not sure, however, if v.external can handle this case correctly, as 
it uses the layer name directly in select queries and select * from 
layer(geomcolumn) does not work... Try it, and if it doesn't work, 
please file another bug against v.external.

Moritz