postGIS vector input example/setup

[tastatham]

Firstly, thank you for this package. This is exactly what I was looking for!

I have successfully ran the tool using shapefiles but I would prefer to load the vector sources from a postgreSQL/postGIS database. However, I am finding it difficult to define the postGIS vector sources. The structure in the package README.md is somewhat different to the OGR documentation;

The -p argument provides the location for the polygon input. As with the -r argument, this can be a file name or some other location understood by GDAL, such as a PostGIS vector source (-p "PG:dbname=basins[public.basins_lev05]").

According to OGR documentation, referencing a postGIS vector source goes something like;

ogrinfo PG:"host=localhost port=5432 user=user password=pwd dbname=db" -sql "SELECT * FROM schema.table"

For example, I have tried;

-r grid:./data/gpw/raster.tif \
-p PG:"host=localhost port=5432 user=tastatham password=pswd dbname=db" -sql "SELECT * FROM schema.table" \
-f FID \
-s weighted_sum\(grid\) \
-o ./data/zonal_stats.csv

This results in an error message;

ERROR 1: ERROR: column s.consrc does not exist
LINE 1: ...nrelid = c.oid AND a.attnum = ANY (s.conkey) AND (s.consrc L...
HINT: Perhaps you meant to reference the column "s.conkey" or the column "s.conbin".
ERROR 1: ERROR: column s.consrc does not exist
LINE 1: ...nrelid = c.oid AND a.attnum = ANY (s.conkey) AND (s.consrc L...
HINT: Perhaps you meant to reference the column "s.conkey" or the column "s.conbin".

A quick google and this seems to be an (classic!) issue with GDAL. My setup is based on;
ubuntu 18.04, postgreSQL 12, postgis 3, GDAL 2.2.3, released 2017/11/20

I updated my GDAL using the ubuntugis-unstable package

sudo add-apt-repository ppa:ubuntugis/ubuntugis-unstable
sudo apt update
sudo apt-get install libgdal-dev

This gives me GDAL 3.0.4, released 2020/01/28 but has broken my postgreSQL/postGIS setup, in addition to this package;

exactextract: error while loading shared libraries: libgdal.so.20: cannot open shared object file: No such file or directory

Consequently, I downgraded GDAL back to GDAL 2.2.3 for my setup.

I can see you have a Docker image setup for this package, do you have any recommendations with what postgreSQL/postgis setup that is compatible with this particular package?

[dbaston]

The syntax in the example is correct, though there's clearly room for improvement in the documentation. exactextract uses brackets to indicate a band number with a raster (e.g. prec_december:prec.tif[12]) or a layer within a vector source (admin.gpkg[countries]). While exactextract uses GDAL dataset descriptions, it doesn't handle arbitrary GDAL arguments like -sql.

It's also possible to write outputs to Postgres, although there is apparently not the option to choose the table name at this point. (It's output, rather unhelpfully)

If you upgrade GDAL you'll need to rebuild this package, but there's no reason it won't work with 3.x.

As to your Docker question, you should just be able to do docker run -v /home/data:home/data isciences/exactextract -r .... (It looks like this wasn't working previously; I changed the format of theENTRYPOINT in 82a0b12)

[tastatham]

@dbaston thank you for the response.

I see, I did initially apply the logic in the documentation. For postgreSQL inputs for exactextract, I didn't know whether to apply the other declarations such as user and password, so I tried both with and without;
-p "PG:dbname=mydb[myschema.mytable] host=localhost port=5432 user=tastatham password=mypassword"
and
-p "PG:dbname=mydb[myschema.mytable]"
Both return stating Error: ID field 'fid' not found in PG:mydb=mydb but ID field does exist...

I'm performing multiple zonal statistics, so the "output" table name is not helpful.

Overall, exporting the vector tables as shapefiles (as the input vector source for the exactextract function) and exporting the zonal statistics to csv is not a problem but adding I/O directly with postgreSQL would be a nice addition to exactextract.

[scabecks]

Thanks both @dbaston @tastatham for this thread.

I also get the same Error: ID field 'gid' not found in PG:mydb=mydb on a table that has an integer field called gid as its primary key. However, if I create a new integer field on the table called fid extraction occurs as expected (it's such a wonderful tool! Thank you!). Is there something that requires the ID field being to not be a primary key on the table (for postgres tables)?

[dankovacek]

Thanks for posting this! How did you end up incorporating the query? I'm finding as @dbason points out that the -sql flag isn't interpreted -- it throws an error since it thinks it's the -s flag.

Thanks for any help!

[tastatham]

Thanks both @dbaston @tastatham for this thread.

I also get the same Error: ID field 'gid' not found in PG:mydb=mydb on a table that has an integer field called gid as its primary key. However, if I create a new integer field on the table called fid extraction occurs as expected (it's such a wonderful tool! Thank you!). Is there something that requires the ID field being to not be a primary key on the table (for postgres tables)?

I simply opted for exporting the PostgreSQL table to CSV as inputs for exactextract, and then imported the csv result back into PostgreSQL. Things may have changed since the last time I tried this?

[dbaston]

@dankovacek you could put the query into a VRT file and point to that with -p. See https://gdal.org/drivers/vector/vrt.html#example-renaming-attributes for an example you could adapt.