[GRASS-stats] readVECT6 problem fo importing null data

[Jarosław Jasiewicz]

Dear Collages!

I found some Issue (seems problematic) with readVECT6 (there are all
commands I used, complete my answer)

I have vector file attached table (pgSQL) with null values both for int4
and char columns (example table):
I import the table via RdbiPgSQL

 >library(RdbiPgSQL)
 >conn=dbConnect(PgSQL(), host="localhost", ........)
 >sql="SELECT * FROM t"
 >quer=dbSendQuery(conn, sql)
 >table=dbGetResult(quer)
 >table
  cat int4 char
1   2     1   <NA>
2   4    NA   <NA>
3   6     5   <NA>
4   7    NA   <NA>
5   1     0      n
6   3    NA      b
7   5     4      p
8   8     6      c

As you see, there are two columns int4 and char with null values, which
are imported to R as shoud be.

But what happend if we import data b y readVECT6:

 > readVECT6("t")
Exporting 8 points/lines...
 100%
8 features written
OGR data source with driver: ESRI Shapefile
Source: "/home/jarekj/grassdata/azp/PERMANENT/.tmp/jarekj-desktop",
layer: "t"
with  8  rows and  3  columns
         coordinates         cat int4 char
1 (3688170, 5673160)   1     0      n
2 (3694430, 5674130)   2     1      
3 (3701530, 5665800)   3     0      b
4 (3690110, 5660580)   4     0      
5 (3706040, 5668900)   5     4      p
6 (3693660, 5665800)   6     5      
7 (3704170, 5674130)   7     0      
8 (3705460, 5660450)   8     6      c

null values for char are preserved but for int, has been changed to 0.

AFAIK, it is due to v.out.ogr use shapefile and dbf which  treat null in
integer as 0 during export.

So, it is a problem??? Or May I forgot do something.
best regards
Jarek

_______________________________________________
grass-stats mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/grass-stats

[Roger Bivand]

On Mon, 26 Nov 2007, Jarek Jasiewicz wrote:

> Dear Collages!
>
> I found some Issue (seems problematic) with readVECT6 (there are all commands
> I used, complete my answer)
>
> I have vector file attached table (pgSQL) with null values both for int4 and
> char columns (example table):
> I import the table via RdbiPgSQL
>
>> library(RdbiPgSQL)
>> conn=dbConnect(PgSQL(), host="localhost", ........)
>> sql="SELECT * FROM t"
>> quer=dbSendQuery(conn, sql)
>> table=dbGetResult(quer)
>> table
> cat int4 char
> 1   2     1   <NA>
> 2   4    NA   <NA>
> 3   6     5   <NA>
> 4   7    NA   <NA>
> 5   1     0      n
> 6   3    NA      b
> 7   5     4      p
> 8   8     6      c
>
> As you see, there are two columns int4 and char with null values, which are
> imported to R as shoud be.

I can reproduce the problem, which seems related to DBFIsAttributeNULL not
being handled adequately on either side (both are using the OGR library).
Could you please try to write the temporary shapefile directly using
v.out.ogr, and then try to read it with readOGR() and with readShape*()
with * as Points, Lines, or Poly depending on what getinfo.shape() says?
I'll try to debug using the DBF code in the foreign package to start with
- numeric fields should have value /* NULL numeric fields have value
"****************" */ from dbfopen.c, but do not seem to.

Roger

>
> But what happend if we import data b y readVECT6:
>
>>  readVECT6("t")
> Exporting 8 points/lines...
> 100%
> 8 features written
> OGR data source with driver: ESRI Shapefile
> Source: "/home/jarekj/grassdata/azp/PERMANENT/.tmp/jarekj-desktop", layer:
> "t"
> with  8  rows and  3  columns
>        coordinates         cat int4 char
> 1 (3688170, 5673160)   1     0      n
> 2 (3694430, 5674130)   2     1      3 (3701530, 5665800)   3     0      b
> 4 (3690110, 5660580)   4     0      5 (3706040, 5668900)   5     4      p
> 6 (3693660, 5665800)   6     5      7 (3704170, 5674130)   7     0      8
> (3705460, 5660450)   8     6      c
>
> null values for char are preserved but for int, has been changed to 0.
>
> AFAIK, it is due to v.out.ogr use shapefile and dbf which  treat null in
> integer as 0 during export.
>
> So, it is a problem??? Or May I forgot do something.
> best regards
> Jarek
>
> _______________________________________________
> grass-stats mailing list
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/grass-stats
>

--
Roger Bivand
Economic Geography Section, Department of Economics, Norwegian School of
Economics and Business Administration, Helleveien 30, N-5045 Bergen,
Norway. voice: [phone]; fax [phone]
e-mail: [hidden email]
_______________________________________________
grass-stats mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/grass-stats

[Roger Bivand]

On Mon, 26 Nov 2007, Jarek Jasiewicz wrote:

> Dear Collages!
>
> I found some Issue (seems problematic) with readVECT6 (there are all commands
> I used, complete my answer)
>
> I have vector file attached table (pgSQL) with null values both for int4 and
> char columns (example table):
> I import the table via RdbiPgSQL

Dear Jarek,

Could you please try readOGR() directly from the PostgreSQL/PostGIS
source? This will remove the imposition of the intermediary DBF format,
and if it succeeds provides a feasible solution, and if it doesn't,
suggests that OGR drivers have problems with missing value handling in
some implementations (in GRASS and in rgdal, where v.in.ogr and readOGR()
use the same basic approach, as do v.out.ogr and writeOGR()).

Roger

>
>> library(RdbiPgSQL)
>> conn=dbConnect(PgSQL(), host="localhost", ........)
>> sql="SELECT * FROM t"
>> quer=dbSendQuery(conn, sql)
>> table=dbGetResult(quer)
>> table
> cat int4 char
> 1   2     1   <NA>
> 2   4    NA   <NA>
> 3   6     5   <NA>
> 4   7    NA   <NA>
> 5   1     0      n
> 6   3    NA      b
> 7   5     4      p
> 8   8     6      c
>
> As you see, there are two columns int4 and char with null values, which are
> imported to R as shoud be.
>
> But what happend if we import data b y readVECT6:
>
>>  readVECT6("t")
> Exporting 8 points/lines...
> 100%
> 8 features written
> OGR data source with driver: ESRI Shapefile
> Source: "/home/jarekj/grassdata/azp/PERMANENT/.tmp/jarekj-desktop", layer:
> "t"
> with  8  rows and  3  columns
>        coordinates         cat int4 char
> 1 (3688170, 5673160)   1     0      n
> 2 (3694430, 5674130)   2     1      3 (3701530, 5665800)   3     0      b
> 4 (3690110, 5660580)   4     0      5 (3706040, 5668900)   5     4      p
> 6 (3693660, 5665800)   6     5      7 (3704170, 5674130)   7     0      8
> (3705460, 5660450)   8     6      c
>
> null values for char are preserved but for int, has been changed to 0.
>
> AFAIK, it is due to v.out.ogr use shapefile and dbf which  treat null in
> integer as 0 during export.
>
> So, it is a problem??? Or May I forgot do something.
> best regards
> Jarek
>
> _______________________________________________
> grass-stats mailing list
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/grass-stats
>

--
Roger Bivand
Economic Geography Section, Department of Economics, Norwegian School of
Economics and Business Administration, Helleveien 30, N-5045 Bergen,
Norway. voice: [phone]; fax [phone]
e-mail: [hidden email]
_______________________________________________
grass-stats mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/grass-stats

[Jarosław Jasiewicz]

Roger Bivand pisze:

> On Mon, 26 Nov 2007, Jarek Jasiewicz wrote:
>
>> Dear Collages!
>>
>> I found some Issue (seems problematic) with readVECT6 (there are all
>> commands I used, complete my answer)
>>
>> I have vector file attached table (pgSQL) with null values both for
>> int4 and char columns (example table):
>> I import the table via RdbiPgSQL
>
> Dear Jarek,
>
> Could you please try readOGR() directly from the PostgreSQL/PostGIS
> source? This will remove the imposition of the intermediary DBF
> format, and if it succeeds provides a feasible solution, and if it
> doesn't, suggests that OGR drivers have problems with missing value
> handling in some implementations (in GRASS and in rgdal, where
> v.in.ogr and readOGR() use the same basic approach, as do v.out.ogr
> and writeOGR()).
>
> Roger
>
>

So the problem is in r.out.ogr?. There are some other methods as you
describe. So I solved problem easyly. I rather point out issue, maybe
for some other R and GRASS users.
Best ragards

Jarek
_______________________________________________
grass-stats mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/grass-stats

[Roger Bivand]

On Tue, 27 Nov 2007, Jarosaw Jasiewicz wrote:

> Roger Bivand pisze:
>>  On Mon, 26 Nov 2007, Jarek Jasiewicz wrote:
>>
>> >  Dear Collages!
>> >
>> >  I found some Issue (seems problematic) with readVECT6 (there are all
>> >  commands I used, complete my answer)
>> >
>> >  I have vector file attached table (pgSQL) with null values both for int4
>> >  and char columns (example table):
>> >  I import the table via RdbiPgSQL
>>
>>  Dear Jarek,
>>
>>  Could you please try readOGR() directly from the PostgreSQL/PostGIS
>>  source? This will remove the imposition of the intermediary DBF format,
>>  and if it succeeds provides a feasible solution, and if it doesn't,
>>  suggests that OGR drivers have problems with missing value handling in
>>  some implementations (in GRASS and in rgdal, where v.in.ogr and readOGR()
>>  use the same basic approach, as do v.out.ogr and writeOGR()).
>>
>>  Roger
>>
>>
>
>
> So the problem is in r.out.ogr?

No, we do not know that (v.out.ogr). It could just as easily be readOGR()
using the "ESRI Shapefile" driver. In fact the root of the problem is that
there is no standard for setting missing values in DBF files. The one used
in the OGR library and in its included shapelib is only one of many, and
the header does not encode any information about missing values, so that
DBF files are not self-describing.

> There are some other methods as you
> describe. So I solved problem easyly. I rather point out issue, maybe for
> some other R and GRASS users.

Certainly - I will add a warning to the readOGR() help page. What was your
solution - reading from PostgreSQL directly or using readOGR() on the
PostGIS data source?

Roger

> Best ragards
>
> Jarek
>

--
Roger Bivand
Economic Geography Section, Department of Economics, Norwegian School of
Economics and Business Administration, Helleveien 30, N-5045 Bergen,
Norway. voice: [phone]; fax [phone]
e-mail: [hidden email]
_______________________________________________
grass-stats mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/grass-stats

[Jarosław Jasiewicz]

>
> I can reproduce the problem, which seems related to DBFIsAttributeNULL
> not being handled adequately on either side (both are using the OGR
> library). Could you please try to write the temporary shapefile
> directly using v.out.ogr, and then try to read it with readOGR() and
> with readShape*() with * as Points, Lines, or Poly depending on what
> getinfo.shape() says? I'll try to debug using the DBF code in the
> foreign package to start with - numeric fields should have value /*
> NULL numeric fields have value "****************" */ from dbfopen.c,
> but do not seem to.
>

I tried what you sugested. I can't probably help more, but I found that:

- v.out.ogr produce dbf with 0 instead null;
- readOGR() reads dbf with 0 instead null;
- if grass dbf table null are treated as 0, but displayed as null;

I use RdbiPgSQL to import data with null values and nad readVECT6 to
import vector and use  substitute () to merge them (if needed). it is a
temporary solution

Jarek
_______________________________________________
grass-stats mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/grass-stats

[Roger Bivand]

On Thu, 29 Nov 2007, Jarosaw Jasiewicz wrote:

>
>>
>>  I can reproduce the problem, which seems related to DBFIsAttributeNULL not
>>  being handled adequately on either side (both are using the OGR library).
>>  Could you please try to write the temporary shapefile directly using
>>  v.out.ogr, and then try to read it with readOGR() and with readShape*()
>>  with * as Points, Lines, or Poly depending on what getinfo.shape() says?
>>  I'll try to debug using the DBF code in the foreign package to start with
>>  - numeric fields should have value /* NULL numeric fields have value
>>  "****************" */ from dbfopen.c, but do not seem to.
>>
> I tried what you sugested. I can't probably help more, but I found that:
>
> - v.out.ogr produce dbf with 0 instead null;
> - readOGR() reads dbf with 0 instead null;
> - if grass dbf table null are treated as 0, but displayed as null;
>
> I use RdbiPgSQL to import data with null values and nad readVECT6 to import
> vector and use  substitute () to merge them (if needed). it is a temporary
> solution

Jarek,

Thanks - I am adding comments to the readOGR() and readVECT6() help pages.
OGR does not promise to handle missing values at all, because there are no
standards as such - so this affects GRASS vector handling generally.

Two questions if you have time:

1) can you try to read from PostgreSQL directly using readOGR() - I guess
that your GDAL/OGR includes the PostgreSQL/PostGIS driver?

2) can you see whether the missing values are visible correctly within
GRASS, maybe with db.select (the v.db.univar script assumes that there
are no missing values)? If they are seen, then the problem is in
v.out.ogr not checking.

I can see isNull fields in dbmi.h; they seem to be used in the fetch.c
files for mysql, odbc, ogr, postgres, and sqlite drivers in db/drivers/. I
can only see this retrieved in vector/v.univar/main - but Martin Landa
probably added this recently, and he is active. There are traces in
lib/db/dbmi_base/value.c, lib/db/dbmi_base/xdrvalue.c, and
lib/db/dbmi_client/select.c.

I think that Martin will be interested in this, so maybe it's worth
checking. I do not run PostgreSQL, so I'm afraid I can't check myself. If
you do, maybe post to grass-dev?

Roger


>
> Jarek
>

--
Roger Bivand
Economic Geography Section, Department of Economics, Norwegian School of
Economics and Business Administration, Helleveien 30, N-5045 Bergen,
Norway. voice: [phone]; fax [phone]
e-mail: [hidden email]
_______________________________________________
grass-stats mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/grass-stats

[Markus Neteler-3]

Dear all,

Roger notified me about this issue (I sometimes read here as time
permits). I made some tests using out NC OSGeo data set
(http://www.grassbook.org/data_menu3rd.php):

# copy the map into your MAPSET and check for NULL
g.copy vect=lakes,mylakes
v.db.select mylakes
v.db.select mylakes where="FTYPE IS NULL"

# display the lakes, show undefined FTYPE lakes in red
g.region swwake_10m
d.mon x0
d.erase
d.vect mylakes where="FTYPE NOT NULL" type=area col=blue
d.vect mylakes where="FTYPE IS NULL" type=area col=red
# This works pretty well.

# Export test:
v.out.ogr mylakes dsn=mylakes.shp type=area

# Looking at it in openoffice:
cat,N,11,0,AREA,N,24,15,PERIMETER,N,24,15,FULL_HYDRO,N,24,15,FULL_HYDR2,N,24,15,FTYPE,C,80,FCODE,N,11,0,NAME,C,80
8,5310.04,334.89,9,55660,LAKE/POND,39000,
33,21739.17,709.53,34,54836,LAKE/POND,39000,
63,469.77,105.84,64,0,,0,
84,10564.66,402.43,85,55595,ROCK/ISLAND,44100,
93,7655.72,344.71,94,53621,DAM/WEIR,34300,
94,1357.21,178.47,95,0,,0,
...

No problem at all: some empty FTYPE fields as desired.
The problem must be elsewhere (or I misunderstand the problem,
didn't read all messages).

Best wishes,
Markus

Roger Bivand wrote:

On Thu, 29 Nov 2007, Jarosaw Jasiewicz wrote:

>
>>
>>  I can reproduce the problem, which seems related to DBFIsAttributeNULL not
>>  being handled adequately on either side (both are using the OGR library).
>>  Could you please try to write the temporary shapefile directly using
>>  v.out.ogr, and then try to read it with readOGR() and with readShape*()
>>  with * as Points, Lines, or Poly depending on what getinfo.shape() says?
>>  I'll try to debug using the DBF code in the foreign package to start with
>>  - numeric fields should have value /* NULL numeric fields have value
>>  "****************" */ from dbfopen.c, but do not seem to.
>>
> I tried what you sugested. I can't probably help more, but I found that:
>
> - v.out.ogr produce dbf with 0 instead null;
> - readOGR() reads dbf with 0 instead null;
> - if grass dbf table null are treated as 0, but displayed as null;
>
> I use RdbiPgSQL to import data with null values and nad readVECT6 to import
> vector and use  substitute () to merge them (if needed). it is a temporary
> solution

Jarek,

Thanks - I am adding comments to the readOGR() and readVECT6() help pages.
OGR does not promise to handle missing values at all, because there are no
standards as such - so this affects GRASS vector handling generally.

Two questions if you have time:

1) can you try to read from PostgreSQL directly using readOGR() - I guess
that your GDAL/OGR includes the PostgreSQL/PostGIS driver?

2) can you see whether the missing values are visible correctly within
GRASS, maybe with db.select (the v.db.univar script assumes that there
are no missing values)? If they are seen, then the problem is in
v.out.ogr not checking.

I can see isNull fields in dbmi.h; they seem to be used in the fetch.c
files for mysql, odbc, ogr, postgres, and sqlite drivers in db/drivers/. I
can only see this retrieved in vector/v.univar/main - but Martin Landa
probably added this recently, and he is active. There are traces in
lib/db/dbmi_base/value.c, lib/db/dbmi_base/xdrvalue.c, and
lib/db/dbmi_client/select.c.

I think that Martin will be interested in this, so maybe it's worth
checking. I do not run PostgreSQL, so I'm afraid I can't check myself. If
you do, maybe post to grass-dev?

Roger


>
> Jarek
>

--
Roger Bivand
Economic Geography Section, Department of Economics, Norwegian School of
Economics and Business Administration, Helleveien 30, N-5045 Bergen,
Norway. voice: [phone]; fax [phone]
e-mail: [e-mail]

[Jarosław Jasiewicz]

Dear Markus!

The problem most probable concerns export only, when atributres are
stored in postgreSQL database and it happens when data during v.out.ogr
to shp with dbf table, in spite of NOT NULL in pgsql  is set
cheers
Jarek

Markus Neteler wrote:

> Dear all,
>
> Roger notified me about this issue (I sometimes read here as time
> permits). I made some tests using out NC OSGeo data set
> (http://www.grassbook.org/data_menu3rd.php):
>
> # copy the map into your MAPSET and check for NULL
> g.copy vect=lakes,mylakes
> v.db.select mylakes
> v.db.select mylakes where="FTYPE IS NULL"
>
> # display the lakes, show undefined FTYPE lakes in red
> g.region swwake_10m
> d.mon x0
> d.erase
> d.vect mylakes where="FTYPE NOT NULL" type=area col=blue
> d.vect mylakes where="FTYPE IS NULL" type=area col=red
> # This works pretty well.
>
> # Export test:
> v.out.ogr mylakes dsn=mylakes.shp type=area
>
> # Looking at it in openoffice:
> cat,N,11,0,AREA,N,24,15,PERIMETER,N,24,15,FULL_HYDRO,N,24,15,FULL_HYDR2,N,24,15,FTYPE,C,80,FCODE,N,11,0,NAME,C,80
> 8,5310.04,334.89,9,55660,LAKE/POND,39000,
> 33,21739.17,709.53,34,54836,LAKE/POND,39000,
> 63,469.77,105.84,64,0,,0,
> 84,10564.66,402.43,85,55595,ROCK/ISLAND,44100,
> 93,7655.72,344.71,94,53621,DAM/WEIR,34300,
> 94,1357.21,178.47,95,0,,0,
> ...
>
> No problem at all: some empty FTYPE fields as desired.
> The problem must be elsewhere (or I misunderstand the problem,
> didn't read all messages).
>
> Best wishes,
> Markus
>  

_______________________________________________
grass-stats mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/grass-stats

[Markus Neteler-3]

Dear Jarek,

ok, I made again a test using out NC OSGeo data set
but now PostgreSQL:

db.connect driver=pg database="host=localhost,dbname=postgres"
db.tables -p
information_schema.sql_features
...

# copy the map into your MAPSET and check for NULL
g.copy vect=lakes,mylakes
v.db.select mylakes
v.db.select mylakes where="FTYPE IS NULL"

# display the lakes, show undefined FTYPE lakes in red
g.region swwake_10m
d.mon x0
d.erase
d.vect mylakes where="FTYPE IS NOT NULL" type=area col=blue
d.vect mylakes where="FTYPE IS NULL" type=area col=red
# ... works.

# Export test:
v.out.ogr mylakes dsn=mylakes.shp type=area

# Looking at it in openoffice:
openoffice.org2.3 mylakes.dbf
cat,N,11,0,area,N,24,15,perimeter,N,24,15,full_hydro,N,24,15,full_hydr2,N,24,15,ftype,C,80,fcode,N,11,0,name,C,80
8,5310.04,334.89,9,55660,LAKE/POND,39000,
33,21739.17,709.53,34,54836,LAKE/POND,39000,
63,469.77,105.84,64,0,,0,
84,10564.66,402.43,85,55595,ROCK/ISLAND,44100,
93,7655.72,344.71,94,53621,DAM/WEIR,34300,
94,1357.21,178.47,95,0,,0,
96,26382.58,1286.95,97,53957,ROCK/ISLAND,44100,
...

Also the expected empty fields here. I am using PG 8.2.5 and
GRASS 6.3-CVS.
Can you replicate this example?

Markus

Jarek Jasiewicz wrote:

Dear Markus!

The problem most probable concerns export only, when atributres are
stored in postgreSQL database and it happens when data during v.out.ogr
to shp with dbf table, in spite of NOT NULL in pgsql  is set
cheers
Jarek

[Roger Bivand]

On Wed, 5 Dec 2007, Markus Neteler wrote:

>
> Dear Jarek,
>
> ok, I made again a test using out NC OSGeo data set
> but now PostgreSQL:
>
> db.connect driver=pg database="host=localhost,dbname=postgres"
> db.tables -p
> information_schema.sql_features
> ...
>
> # copy the map into your MAPSET and check for NULL
> g.copy vect=lakes,mylakes
> v.db.select mylakes
> v.db.select mylakes where="FTYPE IS NULL"
>
> # display the lakes, show undefined FTYPE lakes in red
> g.region swwake_10m
> d.mon x0
> d.erase
> d.vect mylakes where="FTYPE IS NOT NULL" type=area col=blue
> d.vect mylakes where="FTYPE IS NULL" type=area col=red
> # ... works.
>
> # Export test:
> v.out.ogr mylakes dsn=mylakes.shp type=area
>
> # Looking at it in openoffice:
> openoffice.org2.3 mylakes.dbf
> cat,N,11,0,area,N,24,15,perimeter,N,24,15,full_hydro,N,24,15,full_hydr2,N,24,15,ftype,C,80,fcode,N,11,0,name,C,80
> 8,5310.04,334.89,9,55660,LAKE/POND,39000,
> 33,21739.17,709.53,34,54836,LAKE/POND,39000,
> 63,469.77,105.84,64,0,,0,
> 84,10564.66,402.43,85,55595,ROCK/ISLAND,44100,
> 93,7655.72,344.71,94,53621,DAM/WEIR,34300,
> 94,1357.21,178.47,95,0,,0,
> 96,26382.58,1286.95,97,53957,ROCK/ISLAND,44100,
> ...

There is also the question of numerical fields, and please remember that
an empty field is not NULL, it is a zero-length text string:

> a <- ""
> is.na(a)
[1] FALSE

so one needs to do:

> is.na(a) <- nchar(a) == 0
> is.na(a)
[1] TRUE

and remember that character fields are cast to factor - so use
as.character(a) too. This means that the encoding for each field needs to
be known and transferred *outside* the *.dbf file. I think that the DBF
format is still the default for vector attribute data, isn't it? Maybe
SQLite does a better job, then we could look at different OGR drivers, or
ESRI Shapefile for the geometry and RSQLite for the data? Jarek had found
that numerical NULL was being set to 0 if I remember.

Roger

>
> Also the expected empty fields here. I am using PG 8.2.5 and
> GRASS 6.3-CVS.
> Can you replicate this example?
>
> Markus
>
>
> Jarek Jasiewicz wrote:
>>
>> Dear Markus!
>>
>> The problem most probable concerns export only, when atributres are
>> stored in postgreSQL database and it happens when data during v.out.ogr
>> to shp with dbf table, in spite of NOT NULL in pgsql  is set
>> cheers
>> Jarek
>>
>
>

--
Roger Bivand
Economic Geography Section, Department of Economics, Norwegian School of
Economics and Business Administration, Helleveien 30, N-5045 Bergen,
Norway. voice: [phone]; fax [phone]
e-mail: [hidden email]
_______________________________________________
grass-stats mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/grass-stats

[Jarosław Jasiewicz]

Markus Neteler pisze:

> Dear Jarek,
>
> ok, I made again a test using out NC OSGeo data set
> but now PostgreSQL:
>
> db.connect driver=pg database="host=localhost,dbname=postgres"
> db.tables -p
> information_schema.sql_features
> ...
>
> # copy the map into your MAPSET and check for NULL
> g.copy vect=lakes,mylakes
> v.db.select mylakes
> v.db.select mylakes where="FTYPE IS NULL"
>
> # display the lakes, show undefined FTYPE lakes in red
> g.region swwake_10m
> d.mon x0
> d.erase
> d.vect mylakes where="FTYPE IS NOT NULL" type=area col=blue
> d.vect mylakes where="FTYPE IS NULL" type=area col=red
> # ... works.
>
> # Export test:
> v.out.ogr mylakes dsn=mylakes.shp type=area
>
> # Looking at it in openoffice:
> openoffice.org2.3 mylakes.dbf
> cat,N,11,0,area,N,24,15,perimeter,N,24,15,full_hydro,N,24,15,full_hydr2,N,24,15,ftype,C,80,fcode,N,11,0,name,C,80
> 8,5310.04,334.89,9,55660,LAKE/POND,39000,
> 33,21739.17,709.53,34,54836,LAKE/POND,39000,
> 63,469.77,105.84,64,0,,0,
> 84,10564.66,402.43,85,55595,ROCK/ISLAND,44100,
> 93,7655.72,344.71,94,53621,DAM/WEIR,34300,
> 94,1357.21,178.47,95,0,,0,
> 96,26382.58,1286.95,97,53957,ROCK/ISLAND,44100,
> ...
>
> Also the expected empty fields here. I am using PG 8.2.5 and
> GRASS 6.3-CVS.
> Can you replicate this example?
>
> Markus
>
>
> Jarek Jasiewicz wrote:
>  
>> Dear Markus!
>>
>> The problem most probable concerns export only, when atributres are
>> stored in postgreSQL database and it happens when data during v.out.ogr
>> to shp with dbf table, in spite of NOT NULL in pgsql  is set
>> cheers
>> Jarek
>>
>>    
>
>  

Marcus!

FCODE type is character, but problem concerns ONLY numeric data.
Character data are exported propertly, probably because it is rather
zerolengh string for dbf

AFAIK, handling numeric NULL in dbf is problematic not only for grass users

best wishes
Jarek
_______________________________________________
grass-stats mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/grass-stats

[Markus Neteler-3]

Jarek Jasiewicz wrote:

Marcus!

FCODE type is character, but problem concerns ONLY numeric data.
Character data are exported propertly, probably because it is rather
zerolengh string for dbf

AFAIK, handling numeric NULL in dbf is problematic not only for grass users

best wishes
Jarek

I have debugged a bit.

GRASS 6.3.cvs (nc_spm_06):~ > db.connect -p
driver:dbf
database:$GISDBASE/$LOCATION_NAME/$MAPSET/dbf/
schema:
group:

GRASS 6.3.cvs (nc_spm_06):~ > v.db.update mylakes col=AREA value=NULL where="AREA > 6000"

GRASS 6.3.cvs (nc_spm_06):~ > v.db.select mylakes | head
cat|AREA|PERIMETER|FULL_HYDRO|FULL_HYDR2|FTYPE|FCODE|NAME
1||2853.9372|2|55626|LAKE/POND|39000|
2||404.28643|3|55650|DAM/WEIR|34300|
3||708.44683|4|55652|LAKE/POND|39000|
4|1132.10986|151.16533|5|53514|LAKE/POND|39000|
5|2849.43244|249.99941|6|53510|DAM/WEIR|34300|
6|5908.42064|413.13377|7|55640|DAM/WEIR|34300|
7||567.89134|8|55644|LAKE/POND|39000|
8|5310.04399|334.89296|9|55660|LAKE/POND|39000|
9||404.72896|10|55624|TUNNEL/CULVERT|47800|
...

# -> areas removed.

v.out.ogr mylakes dsn=mylakes.shp type=area

openoffice2.3 mylakes.dbf
cat,N,11,0,AREA,N,24,15,PERIMETER,N,24,15,FULL_HYDRO,N,24,15,FULL_HYDR2,N,24,15,FTYPE,C,80,FCODE,N,11,0,NAME,C,80
8,5310.04,334.89,9,55660,LAKE/POND,39000,
33,0,709.53,34,54836,LAKE/POND,39000,
63,469.77,105.84,64,0,,0,
84,0,402.43,85,55595,ROCK/ISLAND,44100,
93,0,344.71,94,53621,DAM/WEIR,34300,

Hooray, problem reproduced :)

BUT:
g.gisenv set=DEBUG=3
v.db.select mylakes
...
D2/3:   ncols = 8 nrows = 10
D2/3: load_table_head(): tab = 9, /home/neteler/grassdata/nc_spm_06/neteler/dbf//mylakes.dbf
cat|AREA|PERIMETER|FULL_HYDRO|FULL_HYDR2|FTYPE|FCODE|NAME
1||2853.9372|2|55626|LAKE/POND|39000|
2||404.28643|3|55650|DAM/WEIR|34300|
3||708.44683|4|55652|LAKE/POND|39000|
4|1132.10986|151.16533|5|53514|LAKE/POND|39000|
5|2849.43244|249.99941|6|53510|DAM/WEIR|34300|
6|5908.42064|413.13377|7|55640|DAM/WEIR|34300|
7||567.89134|8|55644|LAKE/POND|39000|
8|5310.04399|334.89296|9|55660|LAKE/POND|39000|
9||404.72896|10|55624|TUNNEL/CULVERT|47800|
10||3010.93109|11|55625|LAKE/POND|39000|
...
# -> so far ok

dbfdump /home/neteler/grassdata/nc_spm_06/neteler/dbf/mylakes.dbf
  cat          AREA       PERIMETER    FULL_HYDRO     FULL_HYDR2  FTYPE            FCODE NAME
    1        (NULL)     2853.937200      2.000000   55626.000000  LAKE/POND      39000 (NULL)
    2        (NULL)      404.286430      3.000000   55650.000000  DAM/WEIR       34300 (NULL)
    3        (NULL)      708.446830      4.000000   55652.000000  LAKE/POND      39000 (NULL)
    4   1132.109860      151.165330      5.000000   53514.000000  LAKE/POND      39000 (NULL)
    5   2849.432440      249.999410      6.000000   53510.000000  DAM/WEIR       34300 (NULL)
    6   5908.420640      413.133770      7.000000   55640.000000  DAM/WEIR       34300 (NULL)
    7        (NULL)      567.891340      8.000000   55644.000000  LAKE/POND      39000 (NULL)
    8   5310.043990      334.892960      9.000000   55660.000000  LAKE/POND      39000 (NULL)
    9        (NULL)      404.728960     10.000000   55624.000000  TUNNEL/CULVERT 47800 (NULL)
   10        (NULL)     3010.931090     11.000000   55625.000000  LAKE/POND      39000 (NULL)
...
# -> so far ok

ogrinfo /home/neteler/grassdata/nc_spm_06/neteler/dbf//mylakes.dbf mylakes
...
cat: Real (11.0)
AREA: Real (20.6)
PERIMETER: Real (20.6)
FULL_HYDRO: Real (20.6)
FULL_HYDR2: Real (20.6)
FTYPE: String (24.0)
FCODE: Real (11.0)
NAME: String (99.0)
OGRFeature(mylakes2):0
  cat (Real) =           1
  AREA (Real) = (null)
  PERIMETER (Real) =          2853.937200
  FULL_HYDRO (Real) =             2.000000
  FULL_HYDR2 (Real) =         55626.000000
  FTYPE (String) = LAKE/POND
  FCODE (Real) =       39000
  NAME (String) = (null)
...
# -> so far ok

v.out.ogr ...
D2/2: load_table_head(): tab = 9, /home/neteler/grassdata/nc_spm_06/neteler/dbf//mylakes.dbf
D2/2: col 0 : val = 10
D2/2:   colctype = 2
D2/2: col 1 : val =               <- using db_get_string (&dbstring): works
D2/2:   colctype = 3
D2/2: DBMI db_get_value_double: val = 0.000000              <- using db_get_value_double(Value): fails
D2/2: DBMI db_get_value_double: val = 0.000000
D2/2: col db_get_value_double: val = 0.000000
D2/2: DBMI db_get_value_double: val = 3010.931090
D2/2: col 2 : val = 3010.93109
D2/2:   colctype = 3

It seems to be a non.feature/bug in db_get_value_double(Value), maybe also
db_get_value_int(Value) or how the values are stored in dbValue:
lib/db/dbmi_base/value.c

This should be taken to the grass-dev list.

Markus

[Markus Neteler-3]

I am close but ...

RCS file: /home/grass/grassrepository/grass6/vector/v.out.ogr/main.c,v
retrieving revision 1.48
diff -u -r1.48 main.c
--- main.c      3 Oct 2007 09:40:32 -0000       1.48
+++ main.c      5 Dec 2007 15:34:50 -0000
@@ -630,8 +630,11 @@
                                OGR_F_SetFieldInteger( Ogr_feature, j, db_get_value_int(Value) );
                                break;
                             case DB_C_TYPE_DOUBLE:
-                               OGR_F_SetFieldDouble( Ogr_feature, j, db_get_value_double(Value) );
-                               break;
+                               if ( strlen(db_get_string (&dbstring)) != 0 )
+                                   OGR_F_SetFieldDouble( Ogr_feature, j, db_get_value_double(Value) );
+                               else
+                                  G_debug(2, "skipping NULL field");
+                            break;
                             case DB_C_TYPE_STRING:
                                OGR_F_SetFieldString( Ogr_feature, j, db_get_value_string(Value) );
                                break;

should skip the field if NULL was found. but still 0 is inserted.

I suspect that OGR_F_UnsetField() is the problem which is used in line 597 etc.
http://www.gdal.org/ogr/ogr__api_8h.html#a70b7675a003141e63f9603c752ee22e

    /* Attributes */
    /* Reset */
    if ( doatt ) {
        for (j = 0; j < ncol; j++) OGR_F_UnsetField(Ogr_feature, j);
    } else {
        OGR_F_UnsetField(Ogr_feature, 0);
    }

No idea how to solve this but cannot be so hard.

Markus