[GRASS-dev] [GRASS GIS] #3248: v.external: mixed up IDs from PostGIS tables

GDAL · January 10, 2017, 9:57am

#3248: v.external: mixed up IDs from PostGIS tables
-------------------------+-------------------------
Reporter: sbl | Owner: grass-dev@…
Type: defect | Status: new
Priority: normal | Milestone: 7.2.1
Component: Vector | Version: 7.0.5
Keywords: v.external | CPU: Unspecified
Platform: Unspecified |
-------------------------+-------------------------
I tried to link a PostGIS table to GRASS GIS 7 for point sampling using
v.external (in GRASS 7.0.6 and GRASS 7.2.1svn).

With that linked layer (which is not in public schema and has column
"point_id" as primary key) I get a lot of:

{{{
WARNING: No record for category XXXX in table
<myschema.points>

}}}

when I use v.what.rast on that layer.

The results suggest that not the proper IDs were used when values were
loaded to attribute table. Because values in the attribute table of the
points do not match with values in the raster at point locations.
Furthermore, some points, which are supposed to have values in the
attribute table, have none...

With the attaced data, the issue can be reproduced like this:
{{{
ogr2ogr -f PostgreSQL "PG:dbname=gisdata schemas=myschema" points.sqlite
points
v.external --overwrite input=PG:dbname=gisdata layer=myschema.points
output=points_pg -o
g.region -p -a vector=points res=250
r.random.surface --overwrite --verbose output=rand
v.what.rast map=points_pg raster=rand column=morphology
}}}

Linking the SQLite data source however seems to work fine. No errors for:
{{{
v.external --overwrite input=points.sqlite layer=myschema.points
output=points_sqlite -o
v.what.rast map=points_sqlite raster=rand column=morphology
}}}

This is my PostGIS / GRASS environment:

{{{
PostgreSQL 9.5.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
4.8.2-19ubuntu1) 4.8.2, 64-bit
POSTGIS="2.2.2 r14797"
GEOS="3.5.1-CAPI-1.9.1 r4171"
SFCGAL="1.3.0"
PROJ="Rel. 4.9.2, 08 September 2015"
GDAL="GDAL 2.0.3, released 2016/07/01"
}}}

For discussion see: https://lists.osgeo.org/pipermail/grass-
dev/2017-January/083807.html

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3248>
GRASS GIS <https://grass.osgeo.org>

GDAL · January 10, 2017, 9:57am

#3248: v.external: mixed up IDs from PostGIS tables
--------------------------+-------------------------
  Reporter: sbl | Owner: grass-dev@…
      Type: defect | Status: new
  Priority: normal | Milestone: 7.2.1
Component: Vector | Version: 7.0.5
Resolution: | Keywords: v.external
       CPU: Unspecified | Platform: Unspecified
--------------------------+-------------------------
Changes (by sbl):

* Attachment "points.sqlite" added.

example points

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3248>
GRASS GIS <https://grass.osgeo.org>

GDAL · January 10, 2017, 10:23am

#3248: v.external: mixed up IDs from PostGIS tables
--------------------------+-------------------------
  Reporter: sbl | Owner: grass-dev@…
      Type: defect | Status: new
  Priority: normal | Milestone: 7.2.1
Component: Vector | Version: 7.0.5
Resolution: | Keywords: v.external
       CPU: Unspecified | Platform: Unspecified
--------------------------+-------------------------
Description changed by martinl:

Old description:

I tried to link a PostGIS table to GRASS GIS 7 for point sampling using
v.external (in GRASS 7.0.6 and GRASS 7.2.1svn).

With that linked layer (which is not in public schema and has column
"point_id" as primary key) I get a lot of:

{{{
WARNING: No record for category XXXX in table
<myschema.points>

}}}

when I use v.what.rast on that layer.

The results suggest that not the proper IDs were used when values were
loaded to attribute table. Because values in the attribute table of the
points do not match with values in the raster at point locations.
Furthermore, some points, which are supposed to have values in the
attribute table, have none...

With the attaced data, the issue can be reproduced like this:
{{{
ogr2ogr -f PostgreSQL "PG:dbname=gisdata schemas=myschema" points.sqlite
points
v.external --overwrite input=PG:dbname=gisdata layer=myschema.points
output=points_pg -o
g.region -p -a vector=points res=250
r.random.surface --overwrite --verbose output=rand
v.what.rast map=points_pg raster=rand column=morphology
}}}

Linking the SQLite data source however seems to work fine. No errors for:
{{{
v.external --overwrite input=points.sqlite layer=myschema.points
output=points_sqlite -o
v.what.rast map=points_sqlite raster=rand column=morphology
}}}

This is my PostGIS / GRASS environment:

{{{
PostgreSQL 9.5.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
4.8.2-19ubuntu1) 4.8.2, 64-bit
POSTGIS="2.2.2 r14797"
GEOS="3.5.1-CAPI-1.9.1 r4171"
SFCGAL="1.3.0"
PROJ="Rel. 4.9.2, 08 September 2015"
GDAL="GDAL 2.0.3, released 2016/07/01"
}}}

For discussion see: https://lists.osgeo.org/pipermail/grass-
dev/2017-January/083807.html

New description:

I tried to link a PostGIS table to GRASS GIS 7 for point sampling using
v.external (in GRASS 7.0.6 and GRASS 7.2.1svn).

With that linked layer (which is not in public schema and has column
"point_id" as primary key) I get a lot of:

{{{
WARNING: No record for category XXXX in table
<myschema.points>

}}}

when I use v.what.rast on that layer.

The results suggest that not the proper IDs were used when values were
loaded to attribute table. Because values in the attribute table of the
points do not match with values in the raster at point locations.
Furthermore, some points, which are supposed to have values in the
attribute table, have none...

With the attaced data, the issue can be reproduced like this:
{{{
ogr2ogr -f PostgreSQL "PG:dbname=gisdata schemas=myschema" points.sqlite
points
v.external --overwrite input=PG:dbname=gisdata layer=points
output=points_pg -o
g.region -p -a vector=points_pg res=250
r.random.surface --overwrite --verbose output=rand
v.what.rast map=points_pg raster=rand column=morphology
}}}

Linking the SQLite data source however seems to work fine. No errors for:
{{{
v.external --overwrite input=points.sqlite layer=myschema.points
output=points_sqlite -o
v.what.rast map=points_sqlite raster=rand column=morphology
}}}

This is my PostGIS / GRASS environment:

{{{
PostgreSQL 9.5.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
4.8.2-19ubuntu1) 4.8.2, 64-bit
POSTGIS="2.2.2 r14797"
GEOS="3.5.1-CAPI-1.9.1 r4171"
SFCGAL="1.3.0"
PROJ="Rel. 4.9.2, 08 September 2015"
GDAL="GDAL 2.0.3, released 2016/07/01"
}}}

For discussion see: https://lists.osgeo.org/pipermail/grass-
dev/2017-January/083807.html

--

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3248#comment:1>
GRASS GIS <https://grass.osgeo.org>

GDAL · January 10, 2017, 10:45am

#3248: v.external: mixed up IDs from PostGIS tables
--------------------------+-------------------------
  Reporter: sbl | Owner: grass-dev@…
      Type: defect | Status: new
  Priority: normal | Milestone: 7.2.1
Component: Vector | Version: 7.0.5
Resolution: | Keywords: v.external
       CPU: Unspecified | Platform: Unspecified
--------------------------+-------------------------

Comment (by martinl):

In [changeset:"70330" 70330]:
{{{
#!CommitTicketReference repository="" revision="70330"
v.external: mixed up IDs from PostGIS tables (see #3248)
}}}

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3248#comment:2>
GRASS GIS <https://grass.osgeo.org>

GDAL · January 10, 2017, 10:57am

#3248: v.external: mixed up IDs from PostGIS tables
--------------------------+-------------------------
  Reporter: sbl | Owner: grass-dev@…
      Type: defect | Status: new
  Priority: normal | Milestone: 7.2.1
Component: Vector | Version: 7.0.5
Resolution: | Keywords: v.external
       CPU: Unspecified | Platform: Unspecified
--------------------------+-------------------------

Comment (by martinl):

Replying to [ticket:3248 sbl]:
> Linking the SQLite data source however seems to work fine. No errors
for:
> {{{
> v.external --overwrite input=points.sqlite layer=myschema.points
output=points_sqlite -o
> v.what.rast map=points_sqlite raster=rand column=morphology
> }}}

SQLite links are processed by OGR interface, PostGIS by native GRASS-
PostGIS interface. You can try to enforce OGR interface also for PostGIS
links by `GRASS_VECTOR_OGR` environmental variable. Then `v.rast.stats`
should work.

{{{
GRASS_VECTOR_OGR=1 v.what.rast map=points_pg raster=rand column=morphology
}}}

> For discussion see: https://lists.osgeo.org/pipermail/grass-
dev/2017-January/083807.html

Should be fixed in r70330 (trunk). Could you confirm?

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3248#comment:3>
GRASS GIS <https://grass.osgeo.org>

GDAL · January 10, 2017, 12:15pm

#3248: v.external: mixed up IDs from PostGIS tables
--------------------------+-------------------------
  Reporter: sbl | Owner: grass-dev@…
      Type: defect | Status: new
  Priority: normal | Milestone: 7.2.1
Component: Vector | Version: 7.0.5
Resolution: | Keywords: v.external
       CPU: Unspecified | Platform: Unspecified
--------------------------+-------------------------

Comment (by sbl):

Thanks Martin for the quick fix. I can confirm that the issue is solved in
r70330.
Leaving the ticket open until it is backported...

BTW:
{{{
GRASS_VECTOR_OGR=1 v.what.rast map=points_pg raster=rand column=morphology
}}}
did not work, but
{{{
GRASS_VECTOR_OGR=1 v.external --overwrite input=PG:dbname=gisdata
layer=points output=points_pg -o
v.what.rast map=points_pg raster=rand column=morphology
}}}
did.

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3248#comment:4>
GRASS GIS <https://grass.osgeo.org>

GDAL · November 17, 2017, 7:16am

#3248: v.external: mixed up IDs from PostGIS tables
--------------------------+-------------------------
  Reporter: sbl | Owner: grass-dev@…
      Type: defect | Status: new
  Priority: normal | Milestone: 7.2.3
Component: Vector | Version: 7.0.5
Resolution: | Keywords: v.external
       CPU: Unspecified | Platform: Unspecified
--------------------------+-------------------------

Comment (by sbl):

Just a reminder: This has not been backported yet.
See:
https://trac.osgeo.org/grass/browser/grass/branches/releasebranch_7_2/lib/vector/Vlib/read_pg.c#L389

Once backported the ticket can be closed. It is a small change (r70330)
that was successfully tested...

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3248#comment:7>
GRASS GIS <https://grass.osgeo.org>

GDAL · January 19, 2018, 7:41pm

#3248: v.external: mixed up IDs from PostGIS tables
--------------------------+-------------------------
  Reporter: sbl | Owner: grass-dev@…
      Type: defect | Status: new
  Priority: normal | Milestone: 7.2.3
Component: Vector | Version: 7.0.5
Resolution: | Keywords: v.external
       CPU: Unspecified | Platform: Unspecified
--------------------------+-------------------------

Comment (by sbl):

Should we close this, since it will be shipped with 7.4?

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3248#comment:8>
GRASS GIS <https://grass.osgeo.org>

GDAL · January 20, 2018, 10:53am

#3248: v.external: mixed up IDs from PostGIS tables
--------------------------+-------------------------
  Reporter: sbl | Owner: grass-dev@…
      Type: defect | Status: closed
  Priority: normal | Milestone: 7.2.3
Component: Vector | Version: 7.0.5
Resolution: fixed | Keywords: v.external
       CPU: Unspecified | Platform: Unspecified
--------------------------+-------------------------
Changes (by martinl):

* status: new => closed
* resolution: => fixed

Comment:

In [changeset:"72102" 72102]:
{{{
#!CommitTicketReference repository="" revision="72102"
v.external: mixed up IDs from PostGIS tables, fix #3248
}}}

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3248#comment:9>
GRASS GIS <https://grass.osgeo.org>