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

Wed Oct 28 03:22:16 PDT 2015

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 <lr at pcorp.us> 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: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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20151028/45fd3c95/attachment.html>