[postgis-users] PostgreSQL 9.5 beta/PostGIS 2.2.0 fails with OGR

Tue Oct 27 14:48:49 PDT 2015

Chris,

This could be a data specific problem like possible a view or something gdal or geometry_columns is tripping on.  
1) Can you do:

SELECT * FROM geometry_columns;  

Okay 

2) Do you have the same set of data in your PostgreSQL 9.5beta1 as you do in PostgreSQL 9.4?

To test I did this on a windows 2012 R2 64-bit database server 
PostgreSQL 9.5beta1, compiled by Visual C++ build 1800, 64-bit
POSTGIS="2.2.0 r14208" GEOS="3.5.0-CAPI-1.9.0 r4090" SFCGAL="1.2.0" PROJ="Rel. 4.9.1, 04 March 2015" GDAL="GDAL 2.0.1, released 2015/09/15" LIBXML="2.7.8" LIBJSON="0.12" TOPOLOGY RASTER

And queried from my desktop where I have an old dev gdal:

GDAL 1.12dev, released 2014/04/16

ogrinfo -q PG:"dbname=pgr host=y user=postgres password=whatever port=5440"

1: airports (Point)
2: routes (Line String)
3: routes_vertices_pgr (Point)
4: ospr.ways_vertices_pgr (Point)
5: ospr.ways (Line String)
6: ospr.osm_nodes (Point)
7: ospr.vw_sample (Line String)
8: tiger.county (Multi Polygon)
9: tiger.state (Multi Polygon)
10: tiger.place (Multi Polygon)
11: tiger.cousub (Multi Polygon)
12: tiger.edges (Multi Line String)
13: tiger.addrfeat (Line String)
14: tiger.faces (Multi Polygon)
15: tiger.zcta5 (Multi Polygon)
16: tiger.tract (Multi Polygon)
17: tiger.tabblock (Multi Polygon)
18: tiger.bg (Multi Polygon)

Also I tried pramsey's query on my PostgreSQL 9.5beta1 instance:
select '-1'::integer;

and it works just fine.

I even tried with an unprivileged account that just has read access to a couple of tables:
ogrinfo -q PG:"dbname=pgr host=y user=test password=test port=5440"

and got:

1: airports (Point)
2: routes (Line String)
3: routes_vertices_pgr (Point)

I also tried with a brand new db with just postgis and it doesn't error out, just gives no result as expected.

So at a glance there is nothing wrong (unless it is specific to GDAL 1.11).

It is also possible maybe the GDAL 2.0 settings (especially if you said YES to override system env variables during PostGIS 2.2 install) are overriding your GDAL 1.11 in some horrible way (though I can't think of a setting that would cause an issue like this).

I've run into issues only with OSM data that GDAL 2.0 default config throws in an extra column that GDAL 1.11 doesn't emit and that causes issues if say you have a discrepancy .

Thanks,
Regina

-----Original Message-----
From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Paul Ramsey
Sent: Tuesday, October 27, 2015 8:45 AM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] PostgreSQL 9.5 beta/PostGIS 2.2.0 fails with OGR

I'm unable to reproduce any of this, though admittedly I'm not on a
9.5 install, but on a "9.6" (aka git master) install.

pramsey=# select '-1'::integer;

 int4

------

   -1

(1 row)

Crane:~/Sites/crackedtiles/pgsample pramsey(master)$ ogrinfo -q PG:"dbname=gis"

1: continent (Multi Polygon)

So, keep on investigating...

P

On Tue, Oct 27, 2015 at 2:49 AM, Chris Hill <chill at confusedsea.co.uk> wrote:
> Hi folks,
>
> I'm exploring Postgres 9.5 beta with PostGIS 2.20 (full versions 
> listed
> below) on Windows 7 64 bit. All works as expected except for OGR which 
> fails when attempting to access a PostGIS enabled database (but works 
> for non-PostGIS databases).
>
> This command succeeds with Postgres 9.4 and PostGIS 2.1.7:
>
> C:\>ogrinfo -q PG:"dbname=postgis user=postgres port=5436"
>
> Whereas this fails on a new installation of Postgres 9.5 and PostGIS 2.2.0:
>
> C:\>ogrinfo -q PG:"dbname=postgis user=postgres port=5437"
> ERROR 1: ERROR:  invalid input syntax for integer: "'-1'::integer"
>
> ERROR 1: ERROR:  invalid input syntax for integer: "'-1'::integer"
>
> FAILURE:
> Unable to open datasource `PG:dbname=postgis user=postgres port=5437' 
> with the following drivers.
>   -> FileGDB
>   -> ESRI Shapefile
>  [...]
>   -> PostgreSQL
>  [...]
>
> As does any call to ogr2ogr. Yet this command succeeds:
>
> C:\>ogrinfo --config PG_LIST_ALL_TABLES YES PG:"dbname=postgis 
> user=postgres port=5437"
> INFO: Open of `PG:dbname=postgis user=postgres port=5437'
>       using driver `PostgreSQL' successful.
> 1: tiger.loader_variables (None)
> 2: tiger.loader_lookuptables (None)
> 3: raster_columns
> [...]
>
> This page http://www.gdal.org/drv_pg.html (see FAQs at the bottom) 
> suggests that permissions on geometry_columns and/or spatial_ref_sys 
> tables are the likely culprits, yet these tables appear to have full 
> permissions granted for user postgres.
>
> Can anyone suggest a solution?
>
> Many thanks for any assistance.
>
> Chris
>
> -- Versions:
> PostgreSQL 9.5beta1, compiled by Visual C++ build 1800, 64-bit
>
> POSTGIS="2.2.0 r14208" GEOS="3.5.0-CAPI-1.9.0 r4090" SFCGAL="1.2.0"
> PROJ="Rel. 4.9.1, 04 March 2015" GDAL="GDAL 2.0.1, released 2015/09/15"
> LIBXML="2.7.8" LIBJSON="0.12" TOPOLOGY RASTER
>
> GDAL 1.11.3, released 2015/09/16
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users