[GRASS-dev] Re: [GRASS-CVS] markus: grass6/lib/vector/Vlib field.c, 1.38, 1.39

Huidae_Cho · June 14, 2006, 4:41am

On Wed, Jun 14, 2006 at 12:52:10AM +0200, grass@intevation.de wrote:

Author: markus

Update of /grassrepository/grass6/lib/vector/Vlib
In directory doto:/tmp/cvs-serv19408

Modified Files:
field.c
Log Message:
GID search added; auto-search of FID fixed (still non-functional due to apparent OGR_L_GetFIDColumn() failure; auto-search disabled

Markus,

The FID auto-search works well with PostgreSQL:

GRASS 6.1.cvs (Bryan):~/tmp > v.external dsn="PG:host=localhost dbname=mydb user=me" layer=tmp output=tmp
D0/0: Using FID column <ogc_fid> in OGR DB
Building topology ...
WARNING: Random read is not supported by OGR for this layer, cannot build
support.

It looks like not all the OGR formats support FID column
(http://ogr.maptools.org/classOGRLayer.html#a26). For example, shape files
have no FID columns.

FYI, find attached the patch for gdal-1.3.2.

Huidae Cho

(attachments)

gdal-1.3.2.patch (384 Bytes)

Markus_Neteler1 · June 14, 2006, 6:51am

Huidae Cho wrote on 06/14/2006 06:41 AM:

On Wed, Jun 14, 2006 at 12:52:10AM +0200, grass@intevation.de wrote:

Author: markus

Update of /grassrepository/grass6/lib/vector/Vlib
In directory doto:/tmp/cvs-serv19408

Modified Files:
field.c
Log Message:
GID search added; auto-search of FID fixed (still non-functional due to apparent OGR_L_GetFIDColumn() failure; auto-search disabled

Markus,

The FID auto-search works well with PostgreSQL:

Cool! If so, then we can change line 386 in lib/vect/Vlib/field.c

GRASS 6.1.cvs (Bryan):~/tmp > v.external dsn="PG:host=localhost dbname=mydb user=me" layer=tmp output=tmp
D0/0: Using FID column <ogc_fid> in OGR DB

I have now fixed the debug output.

Building topology ...
WARNING: Random read is not supported by OGR for this layer, cannot build
support.

It looks like not all the OGR formats support FID column
(http://ogr.maptools.org/classOGRLayer.html#a26). For example, shape files
have no FID columns.

but does the new autodetection work for SHAPE?

I get open-on-level-2 error with v.info. Does v.info work on your PG map?

FYI, find attached the patch for gdal-1.3.2.

Huidae Cho

------------------------------------------------------------------------

--- ogr/ogrsf_frmts/pg/ogrpgtablelayer.cpp.orig Tue Jun 13 22:46:05 2006
+++ ogr/ogrsf_frmts/pg/ogrpgtablelayer.cpp Tue Jun 13 22:49:05 2006
@@ -830,7 +830,7 @@

        if( pszBytea != NULL )
        {
- osCommand += osCommand + "'" + pszBytea + "'";
+ osCommand = osCommand + "'" + pszBytea + "'";
            CPLFree( pszBytea );
        }
        else

Ah, was this needed to get it working? Please submit to the GDAL bug
tracker (Frank is traveling,
so it won't get lost).

I am curious if the FID autodetector also works for Oracle now.

Markus

Stephan_Holl1 · June 14, 2006, 10:52am

Hello Markus, Huidae

On Wed, 14 Jun 2006 08:51:32 +0200 Markus Neteler <neteler@itc.it>
wrote:

Huidae Cho wrote on 06/14/2006 06:41 AM:

>On Wed, Jun 14, 2006 at 12:52:10AM +0200, grass@intevation.de wrote:
>
>
>>Author: markus
>>
>>Update of /grassrepository/grass6/lib/vector/Vlib
>>In directory doto:/tmp/cvs-serv19408
>>
>>Modified Files:
>> field.c
>>Log Message:
>>GID search added; auto-search of FID fixed (still non-functional
>>due to apparent OGR_L_GetFIDColumn() failure; auto-search disabled
>>
>>
>>
>
>Markus,
>
>The FID auto-search works well with PostgreSQL:
>
>

Cool! If so, then we can change line 386 in lib/vect/Vlib/field.c

>GRASS 6.1.cvs (Bryan):~/tmp > v.external dsn="PG:host=localhost
>dbname=mydb user=me" layer=tmp output=tmp D0/0: Using FID column
><ogc_fid> in OGR DB
>
>
I have now fixed the debug output.

>Building topology ...
>WARNING: Random read is not supported by OGR for this layer, cannot
>build
> support.
>
>It looks like not all the OGR formats support FID column
>(http://ogr.maptools.org/classOGRLayer.html#a26). For example,
>shape files have no FID columns.
>
>
but does the new autodetection work for SHAPE?

I get open-on-level-2 error with v.info. Does v.info work on your PG
map?

>FYI, find attached the patch for gdal-1.3.2.
>
>Huidae Cho
>
>
>------------------------------------------------------------------------
>
>--- ogr/ogrsf_frmts/pg/ogrpgtablelayer.cpp.orig Tue Jun 13
>22:46:05 2006 +++ ogr/ogrsf_frmts/pg/ogrpgtablelayer.cpp Tue
>Jun 13 22:49:05 2006 @@ -830,7 +830,7 @@
>
> if( pszBytea != NULL )
> {
>- osCommand += osCommand + "'" + pszBytea + "'";
>+ osCommand = osCommand + "'" + pszBytea + "'";
> CPLFree( pszBytea );
> }
> else
>
>

Ah, was this needed to get it working? Please submit to the GDAL bug
tracker (Frank is traveling,
so it won't get lost).

I am curious if the FID autodetector also works for Oracle now.

I have used Markus latest code and successfully v.externaled a large
dataset (~300.000 polys), but strangly ogrinfo does not report any
FID-column back, but imported, so that it can be viewed in GRASS.
Also querying using d.what.vect works after spatial-index has built
(took several minutes though).

The dataset takes ages to display, but once there you can zoom into it.

But apparently it needs to read the whole map for redrawing, it takes
as long as the whole dataset. AFAIK it should only draw the features
available in the current region.

Best

Stephan

--
GDF Hannover - Solutions for spatial data analysis and remote sensing
Hannover Office - Mengendamm 16d - D-30177 Hannover
Internet: www.gdf-hannover.de - Email: holl@gdf-hannover.de
Phone : ++49-(0)511.39088507 - Fax: ++49-(0)511.39088508

Markus_Neteler1 · June 14, 2006, 8:59pm

On Tue, Jun 13, 2006 at 11:41:05PM -0500, Huidae Cho wrote:

On Wed, Jun 14, 2006 at 12:52:10AM +0200, grass@intevation.de wrote:
> Author: markus
>
> Update of /grassrepository/grass6/lib/vector/Vlib
> In directory doto:/tmp/cvs-serv19408
>
> Modified Files:
> field.c
> Log Message:
> GID search added; auto-search of FID fixed (still non-functional due to apparent OGR_L_GetFIDColumn() failure; auto-search disabled
>

Markus,

The FID auto-search works well with PostgreSQL:

GRASS 6.1.cvs (Bryan):~/tmp > v.external dsn="PG:host=localhost dbname=mydb user=me" layer=tmp output=tmp
D0/0: Using FID column <ogc_fid> in OGR DB
Building topology ...
WARNING: Random read is not supported by OGR for this layer, cannot build
support.

It looks like not all the OGR formats support FID column
(http://ogr.maptools.org/classOGRLayer.html#a26). For example, shape files
have no FID columns.

Here is the related wish report:

Subject: [Bug 1019] Method to determine and return FID-colum in OGRLayer
http://bugzilla.remotesensing.org/show_bug.cgi?id=1019

There seems to be support for
- PostgreSQL
- MySQL
- Oracle

FYI, find attached the patch for gdal-1.3.2.

Could you tell me more about this patch? Needed to make
PG support functioning?

thanks

Markus

Huidae_Cho · June 14, 2006, 9:15pm

On Wed, Jun 14, 2006 at 10:59:35PM +0200, Markus Neteler wrote:

On Tue, Jun 13, 2006 at 11:41:05PM -0500, Huidae Cho wrote:
> On Wed, Jun 14, 2006 at 12:52:10AM +0200, grass@intevation.de wrote:
> > Author: markus
> >
> > Update of /grassrepository/grass6/lib/vector/Vlib
> > In directory doto:/tmp/cvs-serv19408
> >
> > Modified Files:
> > field.c
> > Log Message:
> > GID search added; auto-search of FID fixed (still non-functional due to apparent OGR_L_GetFIDColumn() failure; auto-search disabled
> >
>
> Markus,
>
> The FID auto-search works well with PostgreSQL:
>
> GRASS 6.1.cvs (Bryan):~/tmp > v.external dsn="PG:host=localhost dbname=mydb user=me" layer=tmp output=tmp
> D0/0: Using FID column <ogc_fid> in OGR DB
> Building topology ...
> WARNING: Random read is not supported by OGR for this layer, cannot build
> support.
>
> It looks like not all the OGR formats support FID column
> (http://ogr.maptools.org/classOGRLayer.html#a26). For example, shape files
> have no FID columns.

Here is the related wish report:

Subject: [Bug 1019] Method to determine and return FID-colum in OGRLayer
http://bugzilla.remotesensing.org/show_bug.cgi?id=1019

There seems to be support for
- PostgreSQL
- MySQL
- Oracle

> FYI, find attached the patch for gdal-1.3.2.

Could you tell me more about this patch? Needed to make
PG support functioning?

thanks

Markus

Markus,

I checked if v.info worked with a v.externaled PostgreSQL vector and,
unfortunately, it didn't:

GRASS 6.1.cvs (Bryan):~/tmp > v.info tmp
D1/3: Vect_open_old(): name = tmp mapset= PERMANENT update = 0
D1/3: Vect_set_thresh(): thresh = 0.000000
D3/3: dig_init_plus()
D1/3: dig_spidx_init()
D3/3: dig_cidx_init()
D1/3: open format file: 'PERMANENT/vector/tmp/frmt'
D3/3: dig_read_frmt_ascii()
D1/3: Vector format: 1 (non-native)
D1/3: Vect_set_thresh(): thresh = 0.000000
D1/3: Vect__read_head(): vector = tmp@PERMANENT
D1/3: Vect_set_thresh(): thresh = 0.000000
D1/3: Level request = 2
D1/3: Vect_open_topo(): name = tmp mapset= PERMANENT
D1/3: Topo file for vector 'tmp@PERMANENT' not available.
D2/3: G__home home = /home/hcho
ERROR: Cannot open old vector tmp@PERMANENT on level 2
GRASS 6.1.cvs (Bryan):~/tmp >

Without the attached patch, the resulting SQL query from v.out.ogr will look
like (invalid SQL query):

INSERT INTO "table" (WKB_GEOMETRY ) VALUES (INSERT INTO "table" (WKB_GEOMETRY )
VALUES ('\\001\\002....')

because osCommand already has "INSERT INTO 'table' (WKB_GEOMETRY ) VALUES (" in
it and it's appended to itself again by "osCommand += osCommand ...", which I
fixed. Note "INSERT INTO" command after the first VALUES. This bug has been
reported to the gdal bugzilla.

Huidae Cho

Markus_Neteler1 · June 14, 2006, 9:40pm

On Wed, Jun 14, 2006 at 04:15:34PM -0500, Huidae Cho wrote:

On Wed, Jun 14, 2006 at 10:59:35PM +0200, Markus Neteler wrote:
> On Tue, Jun 13, 2006 at 11:41:05PM -0500, Huidae Cho wrote:
> > On Wed, Jun 14, 2006 at 12:52:10AM +0200, grass@intevation.de wrote:
> > > Author: markus
> > >
> > > Update of /grassrepository/grass6/lib/vector/Vlib
> > > In directory doto:/tmp/cvs-serv19408
> > >
> > > Modified Files:
> > > field.c
> > > Log Message:
> > > GID search added; auto-search of FID fixed (still non-functional due to apparent OGR_L_GetFIDColumn() failure; auto-search disabled
> > >
> >
> > Markus,
> >
> > The FID auto-search works well with PostgreSQL:
> >
> > GRASS 6.1.cvs (Bryan):~/tmp > v.external dsn="PG:host=localhost dbname=mydb user=me" layer=tmp output=tmp
> > D0/0: Using FID column <ogc_fid> in OGR DB
> > Building topology ...
> > WARNING: Random read is not supported by OGR for this layer, cannot build
> > support.
> >
> > It looks like not all the OGR formats support FID column
> > (http://ogr.maptools.org/classOGRLayer.html#a26). For example, shape files
> > have no FID columns.
>
> Here is the related wish report:
>
> Subject: [Bug 1019] Method to determine and return FID-colum in OGRLayer
> http://bugzilla.remotesensing.org/show_bug.cgi?id=1019
>
> There seems to be support for
> - PostgreSQL
> - MySQL
> - Oracle
>
> > FYI, find attached the patch for gdal-1.3.2.
>
> Could you tell me more about this patch? Needed to make
> PG support functioning?
>
> thanks
>
> Markus

Markus,

I checked if v.info worked with a v.externaled PostgreSQL vector and,
unfortunately, it didn't:

GRASS 6.1.cvs (Bryan):~/tmp > v.info tmp
D1/3: Vect_open_old(): name = tmp mapset= PERMANENT update = 0
D1/3: Vect_set_thresh(): thresh = 0.000000
D3/3: dig_init_plus()
D1/3: dig_spidx_init()
D3/3: dig_cidx_init()
D1/3: open format file: 'PERMANENT/vector/tmp/frmt'
D3/3: dig_read_frmt_ascii()
D1/3: Vector format: 1 (non-native)
D1/3: Vect_set_thresh(): thresh = 0.000000
D1/3: Vect__read_head(): vector = tmp@PERMANENT
D1/3: Vect_set_thresh(): thresh = 0.000000
D1/3: Level request = 2
D1/3: Vect_open_topo(): name = tmp mapset= PERMANENT
D1/3: Topo file for vector 'tmp@PERMANENT' not available.
D2/3: G__home home = /home/hcho
ERROR: Cannot open old vector tmp@PERMANENT on level 2
GRASS 6.1.cvs (Bryan):~/tmp >

I had exactly the same problem. That's why I disabled the
auto-FID search (so, apparently unrelated, but the version
check should be set to 1330 if Frank applies the patch
for the next release).

Without the attached patch, the resulting SQL query from v.out.ogr will look
like (invalid SQL query):

INSERT INTO "table" (WKB_GEOMETRY ) VALUES (INSERT INTO "table" (WKB_GEOMETRY )
VALUES ('\\001\\002....')

because osCommand already has "INSERT INTO 'table' (WKB_GEOMETRY ) VALUES (" in
it and it's appended to itself again by "osCommand += osCommand ...", which I
fixed. Note "INSERT INTO" command after the first VALUES. This bug has been
reported to the gdal bugzilla.

http://bugzilla.remotesensing.org/show_bug.cgi?id=1203

Thanks for tracking that down, I have added myself in cc to watch the
bug.

Markus

Markus_Neteler1 · June 17, 2006, 1:22pm

On Wed, Jun 14, 2006 at 04:15:34PM -0500, Huidae Cho wrote:

On Wed, Jun 14, 2006 at 10:59:35PM +0200, Markus Neteler wrote:
> On Tue, Jun 13, 2006 at 11:41:05PM -0500, Huidae Cho wrote:

...

> > FYI, find attached the patch for gdal-1.3.2.
>
> Could you tell me more about this patch? Needed to make
> PG support functioning?
>
> thanks
>
> Markus

Markus,

I checked if v.info worked with a v.externaled PostgreSQL vector and,
unfortunately, it didn't:

GRASS 6.1.cvs (Bryan):~/tmp > v.info tmp
D1/3: Vect_open_old(): name = tmp mapset= PERMANENT update = 0
D1/3: Vect_set_thresh(): thresh = 0.000000
D3/3: dig_init_plus()
D1/3: dig_spidx_init()
D3/3: dig_cidx_init()
D1/3: open format file: 'PERMANENT/vector/tmp/frmt'
D3/3: dig_read_frmt_ascii()
D1/3: Vector format: 1 (non-native)
D1/3: Vect_set_thresh(): thresh = 0.000000
D1/3: Vect__read_head(): vector = tmp@PERMANENT
D1/3: Vect_set_thresh(): thresh = 0.000000
D1/3: Level request = 2
D1/3: Vect_open_topo(): name = tmp mapset= PERMANENT
D1/3: Topo file for vector 'tmp@PERMANENT' not available.
D2/3: G__home home = /home/hcho
ERROR: Cannot open old vector tmp@PERMANENT on level 2
GRASS 6.1.cvs (Bryan):~/tmp >

Without the attached patch, the resulting SQL query from v.out.ogr will look
like (invalid SQL query):

INSERT INTO "table" (WKB_GEOMETRY ) VALUES (INSERT INTO "table" (WKB_GEOMETRY )
VALUES ('\\001\\002....')

because osCommand already has "INSERT INTO 'table' (WKB_GEOMETRY ) VALUES (" in
it and it's appended to itself again by "osCommand += osCommand ...", which I
fixed. Note "INSERT INTO" command after the first VALUES. This bug has been
reported to the gdal bugzilla.

The fix has been applied to GDAL-CVS.

Markus