[GRASS-user] sql and postgis

[Moritz Lennert]

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 at 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