[GRASS-user] Max length of sql_query in v.extract
Luigi Ponti
lponti at infinito.it
Thu Jan 28 07:34:17 EST 2010
On 28/01/2010 00:45, Markus Neteler wrote:
> On Wed, Jan 27, 2010 at 8:35 PM, Luigi Ponti <lponti at infinito.it> wrote:
>
>> Hello,
>>
>> Is there a length limit to a "where" sql statement fed to v.extract?
>>
>> I am getting an
>> ...
>> Error in db_open_select_cursor()
>> ...
>> in the middle of a very long query.
>>
>
> Could you please try to analyse with
> g.gisenv set=DEBUG=3
> to identify when it gets lost? And perhaps provide an example
> based on the Spearfish or North Carolina data sets?
>
Version: WinGRASS-6.4.SVN-r40650-1 (installed in C:\GRASS-64-SVN i.e. no
spaces in path; OS Windows 7)
Dataset: North Caroline dataset
Mapset: user1
vector: census_wake2000 (this actually resides in PERMANENT)
Working on the "Cmd >" input box of the wxGUI. I have chosen a case
analogous to that where I encountered the error, i.e. a DBF Character
field containing 105 different numeric codes (e.g. 050100) one per record:
(note that you can find full debug output in a text file available at
the following link:
https://docs.google.com/leaf?id=0B0EpsfMZ-IZYOTJiYWVmNjUtNGYzMC00NDM5LWI1NDYtZTMyNzg3M2NjOThk&sort=name&layout=list&num=50
I thought 20,000+ lines was too much for the mailing list but please
advice if had better taken a different course of action.)
### START GRASS CMD HISTORY ###
g.gisenv
set=DEBUG=3
(Thu Jan 28 12:12:34 2010) Command finished (0
sec)
(Thu Jan 28 12:12:43
2010)
v.extract input=census_wake2000 output=census_extract_all_trt2000
where=(TRT2000='050100')or(TRT2000='050300')or(TRT2000='050400')or(TRT2000='050500')or(TRT2000='050600')or(TRT2000='050700')or(TRT2000='050800')or(TRT2000='050900')or(TRT2000='051000')or(TRT2000='051100')or(TRT2000='051200')or(TRT2000='051400')or(TRT2000='051501')or(TRT2000='051502')or(TRT2000='051600')or(TRT2000='051700')or(TRT2000='051800')or(TRT2000='051900')or(TRT2000='052001')or(TRT2000='052002')or(TRT2000='052101')or(TRT2000='052102')or(TRT2000='052201')or(TRT2000='052202')or(TRT2000='052301')or(TRT2000='052302')or(TRT2000='052401')or(TRT2000='052402')or(TRT2000='052404')or(TRT2000='052405')or(TRT2000='052501')or(TRT2000='052503')or(TRT2000='052504')or(TRT2000='052601')or(TRT2000='052602')or(TRT2000='052603')or(TRT2000='052701')or(TRT2000='052703')or(TRT2000='052704')or(TRT2000='052705')or(TRT2000='052801')or(TRT2000='052802')or(TRT2000='052803')or(TRT2000='052804')or(TRT2000='052805')or(TRT2000='052900')or(TRT2000='053001')or(TRT2000='053002')or(TRT2000='053101')or(TRT2000='053103')or(TRT2000='053104')or(TRT2000='053200')or(TRT2000='053402')or(TRT2000='053403')or(TRT2000='053404')or(TRT2000='053405')or(TRT2000='053406')or(TRT2000='053407')or(TRT2000='053501')or(TRT2000='053505')or(TRT2000='053506')or(TRT2000='053507')or(TRT2000='053508')or(TRT2000='053509')or(TRT2000='053510')or(TRT2000='053512')or(TRT2000='053513')or(TRT2000='053514')or(TRT2000='053515')or(TRT2000='053600')or(TRT2000='053703')or(TRT2000='053706')or(TRT2000='053707')or(TRT2000='053709')or(TRT2000='053710')or(TRT2000='053711')or(TRT2000='053712')or(TRT2000='053713')or(TRT2000='053714')or(TRT2000='053715')or(TRT2000='053716')or(TRT2000='053801')or(TRT2000='053802')or(TRT2000='053900')or(TRT2000='054001')or(TRT2000='054003')or(TRT2000='054004')or(TRT2000='054006')or(TRT2000='054007')or(TRT2000='054008')or(TRT2000='054009')or(TRT2000='054010')or(TRT2000='054102')or(TRT2000='054104')or(TRT2000='054105')or(TRT2000='054106')or(TRT2000='054107')or(TRT2000='054108')or(TRT2000='054109')or(TRT2000='054201')or(TRT2000='054202')or(TRT2000='054301')or(TRT2000='054302')or(TRT2000='054401')or(TRT2000='054402')
D3/3: Mapset = PERMANENT
D1/3: Vect_open_old(): name = census_wake2000 mapset=
PERMANENT update = 0
D1/3: Vect_set_thresh(): thresh = 0.000000
[...]
D3/3: db_select_int()
D3/3: SQL: SELECT cat FROM census_wake2000 WHERE (TRT2000=
'050100')or(TRT2000='050300')or(TRT2000='050400')or(TRT2000=
'050500')or(TRT2000='050600')or(TRT2000='050700')or(TRT2000=
'050800')or(TRT2000='050900')or(TRT2000='051000')or(TRT2000=
'051100')or(TRT2000='051200')or(TRT2000='051400')or(TRT2000=
'051501')or(TRT2000='051502')or(TRT2000='051600')or(TRT2000=
'051700')or(TRT2000='051800')or(TRT2000='051900')or(TRT2000=
'052001')or(TRT2000='052002')or(TRT2000='052101')or(TRT2000=
'052102')or(TRT2000='052201')or(TRT2000='052202')or(TRT2000=
'052301')or(TRT2000='052302')or(TRT2000='052401')or(TRT2000=
'052402')or(TRT2000='052404')or(TRT2000='052405')or(TRT2000=
'052501')or(TRT2000='052503')or(TRT2000='052504')or(TRT2000=
'052601')or(TRT2000='052602')or(TRT2000='052603')or(TRT2000=
'052701')or(TRT2000='052703')or(TRT2000='052704')or(TRT2000=
'052705')or(TRT2000='052801')or(TRT2000='052802')or(TRT2000=
'052803')or(TRT2000='052804')or(TRT2000='052805')or(TRT2000=
'052900')or(TRT2000='053001')or(TRT2000='053002')or(TRT2000=
'053101')or(TRT
DBMI-DBF driver error:
SQL parser error: syntax error, unexpected $end processing
''
in statement:
SELECT cat FROM census_wake2000 WHERE (TRT2000='050100')or(T
RT2000='050300')or(TRT2000='050400')or(TRT2000='050500')or(T
RT2000='050600')or(TRT2000='050700')or(TRT2000='050800')or(T
RT2000='050900')or(TRT2000='051000')or(TRT2000='051100')or(T
RT2000='051200')or(TRT2000='051400')or(TRT2000='051501')or(T
RT2000='051502')or(TRT2000='051600')or(TRT2000='051700')or(T
RT2000='051800')or(TRT2000='051900')or(TRT2000='052001')or(T
RT2000='052002')or(TRT2000='052101')or(TRT2000='052102')or(T
RT2000='052201')or(TRT2000='052202')or(TRT2000='052301')or(T
RT2000='052302')or(TRT2000='052401')or(TRT2000='052402')or(T
RT2000='052404')or(TRT2000='052405')or(TRT2000='052501')or(T
RT2000='052503')or(TRT2000='052504')or(TRT2000='052601')or(T
RT2000='052602')or(TRT2000='052603')or(TRT2000='052701')or(T
RT2000='052703')or(TRT2000='052704')or(TRT2000='052705')or(T
RT2000='052801')or(TRT2000='052802')or(TRT2000='052803')or(T
RT2000='052804')or(TRT2000='052805')or(TRT2000='052900')or(T
RT2000='053001')or(TRT2000='053002')or(TRT2000='053101')or(T
RT
Error in db_open_select_cursor()
ERROR: Unable select records from table <census_wake2000>
(Thu Jan 28 12:12:45 2010) Command finished (1 sec)
### END GRASS CMD HISTORY ###
But if I do the same with only the first 10 TRT2000 codes, everything
seem to work fine:
### START GRASS CMD HISTORY ###
v.extract input=census_wake2000 output=census_extract_all_trt2000
where=(TRT2000='050100')or(TRT2000='050300')or(TRT2000='050400')or(TRT2000='050500')or(TRT2000='050600')or(TRT2000='050700')or(TRT2000='050800')or(TRT2000='050900')or(TRT2000='051000')or(TRT2000='051100')
D3/3: Mapset = PERMANENT
D1/3: Vect_open_old(): name = census_wake2000 mapset=
PERMANENT update = 0
[...]
mapset= user1
D1/3: get coor info: C:\cygwin\home\andy/nc_spm_08/user1/vec
tor/census_extract_all_trt2000/coor
D2/3: ftell = 6941
D1/3: Info->size = 6941, Info->mtime = 1264679138
D1/3: dig__write_head()
D1/3: write coor size (6941) to head
D2/3: coor body offset 14
D1/3: Vect_get_thresh(): thresh = 0.000000
D1/3: Vect_write_dblinks(): map =
census_extract_all_trt2000, mapset = user1
D1/3: dbln file: C:\cygwin\home\andy/nc_spm_08/user1/vector/
census_extract_all_trt2000/dbln
D1/3: 1 census_extract_all_trt2000 cat
$GISDBASE/$LOCATION_NAME/$MAPSET/dbf/ dbf
D1/3: Dblinks written
(Thu Jan 28 12:45:51 2010) Command finished (13
sec)
### END GRASS CMD HISTORY ###
And the resulting vector displays ok -- the v.info output is the following:
v.info
census_extract_all_trt2000
+----------------------------------------------------------------------------+
| Layer:
census_extract_all_trt2000 |
| Mapset:
user1 |
| Location:
nc_spm_08 |
| Database:
C:\cygwin\home\andy |
| Titolo: Wake County census tracts 2000 (polygon
map) |
| Scala della mappa:
1:1 |
| Formato della mappa:
native |
| Name of creator:
helena |
| Organization: NC
OneMap |
| Dati di origine: Tue Nov 7 19:54:38
2006 |
|----------------------------------------------------------------------------|
| Type of Map: vector (level:
2) |
|
|
| Numero di punti: 0 Numero di aree:
10 |
| Numero di linee: 0 Numero di isolee:
1 |
| Numero di confini: 41 Numero di facce:
0 |
| Numero di centroidi:
10 |
|
|
| Map is 3D:
No |
| Number of dblinks:
1 |
|
|
| Proiezione: Lambert Conformal
Conic |
| N: 228616.93548384 S:
222650.82550162 |
| E: 645055.16891034 W:
637287.55397511 |
|
|
| Digitization threshold:
0 |
|
Comments: |
|
|
+----------------------------------------------------------------------------+
(Thu Jan 28 13:12:03 2010) Command finished (0
sec)
Kind regards,
Luigi
> best
> Markus
>
>
More information about the grass-user
mailing list