[GRASS-user] sql and postgis

[Gustavo Martinez]

El Tuesday 30 October 2007 18:06:50 Markus Neteler escribió:
> Gustavo Martínez wrote:
> > ...
> > I've tried the following command:
> >
> > v.db.connect -o map=monitoreo driver=pg database=pesca-new
> > table=monitoreo key=idmonitoreo layer=1
> >
> >  and it gave me the following:
> >
> > pg driver: column 'fecha', type 1184  is not supported
> > pg driver: column 'fch_local', type 1184  is not supported
> > pg driver: PostGIS column 'geom', type 'geometry'  will not be converted
> > pg driver: column 'fecha', type 1184  is not supported
> > pg driver: column 'fch_local', type 1184  is not supported
> > pg driver: PostGIS column 'geom', type 'geometry'  will not be converted
>
> I have checked the PG type:
>
> grep 1184 /usr/include/pgsql/server/catalog/pg_type.h
> DATA(insert OID = 1184 ( timestamptz PGNSP PGUID        8 f b t \054 0  0
> timestamptz_in timestamptz_out timestamptz_recv timestamptz_send - d p f 0
> -1 0 _null_ _null_ ));
> #define TIMESTAMPTZOID  1184
>
> cd db/drivers/postgres/
> grep TIMESTAMP *
> create_table.c:            case DB_SQL_TYPE_TIMESTAMP:
> db.c:       type = PG_TYPE_TIMESTAMP;
> describe.c:     case PG_TYPE_TIMESTAMP:
> describe.c:         *sqltype = DB_SQL_TYPE_TIMESTAMP;
> fetch.c:            case PG_TYPE_TIMESTAMP:
> globals.h:  PG_TYPE_TIMESTAMP, /* timestamp */
>
> AFAIK the TIMESTAMPTZ type is yet undefined (with timezone).
> The question is if DB_SQL_TYPE_TIMESTAMP is compliant to it
> so that TIMESTAMPTZ can be mapped in globals.h
> ?
>
> Markus

I've changed the TIMESTAMPTZ type by TIMESTAMP in the PG table. when I do 
v.db.connect now it says

pg driver: PostGIS column 'geom', type 'geometry'  will not be converted
pg driver: PostGIS column 'geom', type 'geometry'  will not be converted
The table <monitoreo> is now part of vector map <monitoreo> and may be deleted 
or overwritten by GRASS modules

If I now try 

v.info -c map=monitoreo at PERMANENT layer=1

the result is

Unable to open vector map <monitoreo at PERMANENT> on topology level 2

So I think the problem with the sql query  is that GRASS don't know about the 
columns of the PG table.
I don't know if that is the way GRASS handles the external layers.

Gustavo

-- 
Lic. Gustavo Martínez
Gestión de Pesquerías
Sub Secretaría de Pesca de la Nación
Paseo Colón 982, Anexo Jardín Pesca
Tel: (54) 11 43492165
Buenos Aires, República Argentina

“Que la tierra se vaya haciendo camino ante tus pasos,
que el viento sople siempre a tus espaldas,
que el sol brille cálido sobre tu cara,
que la lluvia caiga suavemente sobre tus campos y,
hasta tanto volvamos a encontrarnos,
que Dios te lleve en la palma de su mano.”