Opened 6 years ago

Closed 5 years ago

#3248 closed defect (fixed)

v.external: mixed up IDs from PostGIS tables

Reported by: sbl Owned by: grass-dev@…
Priority: normal Milestone: 7.2.3
Component: Vector Version: 7.0.5
Keywords: v.external Cc:
CPU: Unspecified Platform: Unspecified

Description (last modified by martinl)

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

Attachments (1)

points.sqlite (92.0 KB ) - added by sbl 6 years ago.
example points

Download all attachments as: .zip

Change History (10)

by sbl, 6 years ago

Attachment: points.sqlite added

example points

comment:1 by martinl, 6 years ago

Description: modified (diff)

comment:2 by martinl, 6 years ago

In 70330:

v.external: mixed up IDs from PostGIS tables (see #3248)

in reply to:  description comment:3 by martinl, 6 years ago

Replying to 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?

comment:4 by sbl, 6 years ago

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.

comment:5 by martinl, 6 years ago

Milestone: 7.2.17.2.2

comment:6 by neteler, 5 years ago

Milestone: 7.2.27.2.3

Ticket retargeted after milestone closed

comment:7 by sbl, 5 years ago

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

comment:8 by sbl, 5 years ago

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

comment:9 by martinl, 5 years ago

Resolution: fixed
Status: newclosed

In 72102:

v.external: mixed up IDs from PostGIS tables, fix #3248

Note: See TracTickets for help on using tickets.