[GRASS-user] sql and postgis

GRASS GRASS user
,
1

Hi all,

I am using GRASS 6.3 RC1 compiled from source. I have a big table (2 million
points) in Postgresql/Postgis that I can map with v.external. The problem is
when I try to select a subset of the data. I am using gis.m and when I run
the command:

d.vect map=monitoreo@PERMANENT color=0:0:0 lcolor=0:0:0 fcolor=170:170:170
display=shape type=point icon=basic/circle size=5 layer=1 lsize=8 xref=left
yref=center llayer=1 {where=vel<4}

I get the following error:

ERROR 1: ERROR: error de sintaxis en o cerca de «FROM»
LINE 1: SELECT FROM monitoreo WHERE vel<4
                        ^

DBMI-OGR driver error:
Cannot select:
SELECT FROM monitoreo WHERE vel<4

Any clues?

--
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.”

2

Gustavo Martinez wrote:

I am using GRASS 6.3 RC1 compiled from source. I have a big table (2 million
points) in Postgresql/Postgis that I can map with v.external. The problem is
when I try to select a subset of the data. I am using gis.m and when I run
the command:

d.vect map=monitoreo@PERMANENT color=0:0:0 lcolor=0:0:0 fcolor=170:170:170
display=shape type=point icon=basic/circle size=5 layer=1 lsize=8 xref=left
yref=center llayer=1 {where=vel<4}

I get the following error:

ERROR 1: ERROR: error de sintaxis en o cerca de «FROM»
LINE 1: SELECT FROM monitoreo WHERE vel<4
                        ^

DBMI-OGR driver error:
Cannot select:
SELECT FROM monitoreo WHERE vel<4

Any clues?

LINE 1: SELECT FROM monitoreo WHERE vel<4

               ^^^^

Somehow it is losing the '*'. The SQL query should look like:

  SELECT * FROM monitoreo WHERE vel<4

why? I don't know..

Hamish

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

3

On 25/10/07 17:20, Gustavo Martinez wrote:

Hi all,

I am using GRASS 6.3 RC1 compiled from source. I have a big table (2 million points) in Postgresql/Postgis that I can map with v.external. The problem is when I try to select a subset of the data. I am using gis.m and when I run the command:

d.vect map=monitoreo@PERMANENT color=0:0:0 lcolor=0:0:0 fcolor=170:170:170 display=shape type=point icon=basic/circle size=5 layer=1 lsize=8 xref=left yref=center llayer=1 {where=vel<4}

I get the following error:

ERROR 1: ERROR: error de sintaxis en o cerca de «FROM»
LINE 1: SELECT FROM monitoreo WHERE vel<4
                        ^

DBMI-OGR driver error:
Cannot select: SELECT FROM monitoreo WHERE vel<4

Any clues?

What does v.db.connect -p monitoreo show ?
Does database access work for v.external maps just as it does for imported maps ?

Moritz

4

El Monday 29 October 2007 10:33:40 escribió:

On 25/10/07 17:20, Gustavo Martinez wrote:
> Hi all,
>
> I am using GRASS 6.3 RC1 compiled from source. I have a big table (2
> million points) in Postgresql/Postgis that I can map with v.external. The
> problem is when I try to select a subset of the data. I am using gis.m
> and when I run the command:
>
>
> d.vect map=monitoreo@PERMANENT color=0:0:0 lcolor=0:0:0
> fcolor=170:170:170 display=shape type=point icon=basic/circle size=5
> layer=1 lsize=8 xref=left yref=center llayer=1 {where=vel<4}
>
> I get the following error:
>
> ERROR 1: ERROR: error de sintaxis en o cerca de «FROM»
> LINE 1: SELECT FROM monitoreo WHERE vel<4
> ^
>
> DBMI-OGR driver error:
> Cannot select:
> SELECT FROM monitoreo WHERE vel<4
>
> Any clues?

What does v.db.connect -p monitoreo show ?
Does database access work for v.external maps just as it does for
imported maps ?

Moritz

Moritz,

the result of v.db.connect monitoreo -p is:

Vector map <monitoreo> is connected by:
layer <1> table <monitoreo> in database <PG:host=localhost dbname=pesca-new
user=postgres> through driver <ogr> with key <>

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.”

5

On 29/10/07 15:17, Gustavo Martinez wrote:

El Monday 29 October 2007 10:33:40 escribió:

On 25/10/07 17:20, Gustavo Martinez wrote:

Hi all,

I am using GRASS 6.3 RC1 compiled from source. I have a big table (2
million points) in Postgresql/Postgis that I can map with v.external. The
problem is when I try to select a subset of the data. I am using gis.m
and when I run the command:

d.vect map=monitoreo@PERMANENT color=0:0:0 lcolor=0:0:0
fcolor=170:170:170 display=shape type=point icon=basic/circle size=5
layer=1 lsize=8 xref=left yref=center llayer=1 {where=vel<4}

I get the following error:

ERROR 1: ERROR: error de sintaxis en o cerca de «FROM»
LINE 1: SELECT FROM monitoreo WHERE vel<4
                        ^

DBMI-OGR driver error:
Cannot select:
SELECT FROM monitoreo WHERE vel<4

Any clues?

What does v.db.connect -p monitoreo show ?
Does database access work for v.external maps just as it does for
imported maps ?

Moritz

Moritz,

the result of v.db.connect monitoreo -p is:

Vector map <monitoreo> is connected by:
layer <1> table <monitoreo> in database <PG:host=localhost dbname=pesca-new user=postgres> through driver <ogr> with key <>

There's the problem: 'with key <>'. GRASS does not know how to link the geographical data (the map) to the attribute data. This is where the SELECT FROM comes from, i.e., nothing to select.

As I said, I'm not sure that a map linked into GRASS with v.external can be used for database queries. Also don't know what the ogr driver supports.

You could try to relink the map directly to the postgresql table with something like

v.db.connect -o map=monitoreo driver=pg database="host=localhost dbname=pesca-new" table=monitoreo key=XXX

where XXX is the column that contains the identifiers of the geographical objects in your map.

Moritz

6

El Monday 29 October 2007 12:53:20 escribió:

>
> Moritz,
>
> the result of v.db.connect monitoreo -p is:
>
> Vector map <monitoreo> is connected by:
> layer <1> table <monitoreo> in database <PG:host=localhost
> dbname=pesca-new user=postgres> through driver <ogr> with key <>

There's the problem: 'with key <>'. GRASS does not know how to link the
geographical data (the map) to the attribute data. This is where the
SELECT FROM comes from, i.e., nothing to select.

As I said, I'm not sure that a map linked into GRASS with v.external can
be used for database queries. Also don't know what the ogr driver supports.

You could try to relink the map directly to the postgresql table with
something like

v.db.connect -o map=monitoreo driver=pg database="host=localhost
dbname=pesca-new" table=monitoreo key=XXX

where XXX is the column that contains the identifiers of the
geographical objects in your map.

Moritz

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

The table <monitoreo> is now part of vector map <monitoreo> and may be deleted
or overwritten by GRASS modules

However, if I tgry again
v.db.connect monitoreo -p the key field is still empty.

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.”

7

On 30/10/07 18:28, Gustavo Martinez wrote:

El Monday 29 October 2007 12:53:20 escribió:

Moritz,

the result of v.db.connect monitoreo -p is:

Vector map <monitoreo> is connected by:
layer <1> table <monitoreo> in database <PG:host=localhost
dbname=pesca-new user=postgres> through driver <ogr> with key <>

There's the problem: 'with key <>'. GRASS does not know how to link the
geographical data (the map) to the attribute data. This is where the
SELECT FROM comes from, i.e., nothing to select.

As I said, I'm not sure that a map linked into GRASS with v.external can
be used for database queries. Also don't know what the ogr driver supports.

You could try to relink the map directly to the postgresql table with
something like

v.db.connect -o map=monitoreo driver=pg database="host=localhost
dbname=pesca-new" table=monitoreo key=XXX

where XXX is the column that contains the identifiers of the
geographical objects in your map.

Moritz

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

The table <monitoreo> is now part of vector map <monitoreo> and may be deleted or overwritten by GRASS modules

However, if I tgry again v.db.connect monitoreo -p the key field is still empty.

What is the output of v.db.connect -p ?

I'm not sure I can help you any further, but my guess would be that there is a difference in the handling of maps linked via v.external compared to maps which are imported into GRASS...

Moritz

8

On 30/10/07 19:14, Gustavo Martinez wrote:

El Tuesday 30 October 2007 15:00:30 escribió:

On 30/10/07 18:28, Gustavo Martinez wrote:

El Monday 29 October 2007 12:53:20 escribió:

Moritz,

the result of v.db.connect monitoreo -p is:

Vector map <monitoreo> is connected by:
layer <1> table <monitoreo> in database <PG:host=localhost
dbname=pesca-new user=postgres> through driver <ogr> with key <>

There's the problem: 'with key <>'. GRASS does not know how to link the
geographical data (the map) to the attribute data. This is where the
SELECT FROM comes from, i.e., nothing to select.

As I said, I'm not sure that a map linked into GRASS with v.external can
be used for database queries. Also don't know what the ogr driver
supports.

You could try to relink the map directly to the postgresql table with
something like

v.db.connect -o map=monitoreo driver=pg database="host=localhost
dbname=pesca-new" table=monitoreo key=XXX

where XXX is the column that contains the identifiers of the
geographical objects in your map.

Moritz

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

The table <monitoreo> is now part of vector map <monitoreo> and may be
deleted or overwritten by GRASS modules

However, if I tgry again
v.db.connect monitoreo -p the key field is still empty.

What is the output of v.db.connect -p ?

I'm not sure I can help you any further, but my guess would be that
there is a difference in the handling of maps linked via v.external
compared to maps which are imported into GRASS...

Moritz

the result of v.db.connect monitoreo -p is:

Vector map <monitoreo> is connected by:
layer <1> table <monitoreo> in database <PG:host=localhost
dbname=pesca-new user=postgres> through driver <ogr> with key <>

So v.db.connect -o actually did not work (driver is still ogr).

I would recommend that you import the map into GRASS with v.in.ogr, do what you have to there and then export it again to PostGIS with v.out.ogr.

Moritz

9

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
--
View this message in context: http://www.nabble.com/sql-and-postgis-tf4691735.html#a13495693
Sent from the Grass - Users mailing list archive at Nabble.com.

10

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@PERMANENT layer=1

the result is

Unable to open vector map <monitoreo@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.”