[GRASS-user] PostGIS import and primary keys

[Moritz Lennert]

Le 20 octobre 2021 17:56:04 GMT+02:00, "Juan Pedro Pérez Alcántara" <jp.perez.alcantara at gmail.com> a écrit :
>Hello,
>
>I've been testing PostGIS to GRASS imports / exports and there are some
>things I'm not sure I understand correctly. They are related to keeping
>primary keys coming from PostGIS.
>
>My table of origin at PostGIS has a primary key called gid_building
>(integer). When using:
>
>grass $GRASS_DB/PERMANENT --exec \
>v.in.ogr --verbose --overwrite \
>input="PG:host=$HOST dbname=cell_raw_data user=$USER port=$PORT password=
>$PASS" \
>layer=cat2020.building output=building \
>snap=0.01 \
>where="gid_building between 2633000 and 2633100"
>
>and then exporting with:
>
>grass $GRASS_DB/PERMANENT --exec \
>v.out.ogr --overwrite \
>input=building \
>type=area \
>output="PG:host=$HOST dbname=cell_raw_data user=$USER port=$PORT password=
>$PASS" \
>output_layer=cat2020_topo_clean.building_dump \
>format=PostgreSQL \
>lco=FID=gid_building_dump \
>lco=GEOMETRY_NAME=geom
>
>I found the following schema back in PostGIS:
>
>gid_building_dump (integer, with a nextval sequence)
>cat (integer)
>...
>
>But no trace of the original primary key in the PostGIS origin table
>gid_building.
>My guesses:
>
>v.in.ogr just don't use the primary key of the original table,
>gid_building. In
>fact, if I drop the primary key from this column, it is imported normally.

Have you checked whether the 'cat' column values correspond to the primary key values ? Maybe an existing primary key is automatically imported as the 'cat' column ?

Otherwise, this might actually be an OGR issue. What happens to the column when you do an ogr2ogr to transform to geopackage or shapefile ?

Also you might try feeding the PGSQL_OGR_FID option to v.in.ogr's gdal_config parameter. See [1] for more info about the OGR PostgreSQL driver.

If GRASS GIS really loses data during import, I would consider this a serious bug.

>
>GRASS add its cat field to store category.
>
>v.out.ogr adds its own primary key with the nextval sequence.
>
>I'm happy with GDAL / GRASS adding their own internal primary keys to the
>data,
>in fact, I understand this is the sensible thing to do given the disparity
>in
>data models. My only question is: is there any way in v.in.ogr to keep the
>primary key as a normal field?

Actually, using the 'key' parameter you should be able to declare your PostgreSQKL primary key as the column to use by GRASS GIS as its internal primary key (aka category column).


Moritz

[1] https://gdal.org/drivers/vector/pg.html