Hi Regina, Paul & List Thanks so much for your suggestions - they have directed me to the cause and solution.
The cause was two tables of ~PostGIS 8.4 vintage with geometry columns of unknown SRID which were allocated a SRID of -1 back in those days. With PostGIS 2.0 unknown SRIDs were changed from -1 to 0 ( http://postgis.net/docs/release_notes.html#idp82265344). This did not manifest itself as a problem with earlier versions of PostGIS (at least up to 2.1.7), but does with 2.2.0, when... select * from geometry_columns will output the "invalid input syntax for integer: "'-1'::integer"" error. The solution I chose was to pg_dump the schema in sql format from Postgres 9.4/Postgis 2.1.7 and edit the constraints in this file to reflect the unknown SRID change. from CONSTRAINT enforce_srid_wkb_geometry CHECK ((public.st_srid(wkb_geometry) = (-1))) to CONSTRAINT enforce_srid_wkb_geometry CHECK ((public.st_srid(wkb_geometry) = (0))) then restoring to PostGIS 2.2.0 (although I suppose updating the SRIDs in the source database before dumping would work too). Thanks again Chris On 27 October 2015 at 21:48, Paragon Corporation <[email protected]> wrote: > 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:[email protected]] On > Behalf Of Paul Ramsey > Sent: Tuesday, October 27, 2015 8:45 AM > To: PostGIS Users Discussion <[email protected]> > 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 <[email protected]> > 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 > > [email protected] > > http://lists.osgeo.org/mailman/listinfo/postgis-users > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/mailman/listinfo/postgis-users > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/postgis-users
