[Gdal-dev] ogr2ogr MapINfo bug?

[Mateusz Loskot]

Bill Thoen wrote:
> On Sat, Nov 25, 2006 at 11:24:42AM -0500, Frank Warmerdam wrote:
>> Ah, so the problem is, presumably, that OGR isn't seeing the geometry
>> of the PostGIS tables properly.  You might want to verify this with ogrinfo
>> against the postgis table.
> 
> It looks like using an sql statement with ogr2ogr is causing it to miss the
> geometry. When I try ogrinfo on one record, I get these results:
> 
> $ ogrinfo -sql "select * from counties where fips='17055'" 
>   "PG:dbname=spatial1"
> 
> INFO: Open of `PG:dbname=spatial1'
> using driver `PostgreSQL' successful.
> 
> Layer name: sql_statement
> Geometry: Unknown (any)
> Feature Count: 1
> Layer SRS WKT:
> (unknown)
> Geometry Column = the_geom
> gid: Integer (0.0)
> fips: String (0.0)
> state: String (0.0)
> county: String (0.0)
> OGRFeature(sql_statement):0
>   gid (Integer) = 576
>   fips (String) = 17055
>   state (String) = Illinois
>   county (String) = Franklin
> [...]
>
> So I'm not sure if this is a bug or I'm just not using the -sql switch
> properly. Can you tell me what's going on and how to fix it?

I tested current GDAL CVS using two versions of PostGIS databases and
layers:

1. When geometry does not include SRID, it is not PostGIS EWKB/EWKT
format, then using -sql option geometry is returned properly but
with Unknown type:

mloskot:~$ ogrinfo -sql "select * from point where ogc_fid=1"
PG:dbname=gdal_test
INFO: Open of `PG:dbname=gdal_test'
      using driver `PostgreSQL' successful.

Layer name: sql_statement
Geometry: Unknown (any)
Feature Count: 1
Extent: (19.769250, 51.446583) - (19.769250, 51.446583)
Layer SRS WKT:
(unknown)
FID Column = ogc_fid
Geometry Column = wkb_geometry
recnum: String (0.0)
name: String (0.0)
lat: String (0.0)
lon: String (0.0)
altitude: String (0.0)
longname: String (0.0)
OGRFeature(sql_statement):1
  recnum (String) = 1
  name (String) = STATOIL
  lat (String) = +51,4465833
  lon (String) = +19,7692500
  altitude (String) = 215
  longname (String) = STACJA BENZ
  POINT (19.76925 51.446583333333336)


But then I query the same layer and feature using -where option,
it returns geometry type properly:

mloskot:~$ ogr/ogrinfo PG:dbname=gdal_test -where "ogc_fid=1" point
INFO: Open of `PG:dbname=gdal_test'
      using driver `PostgreSQL' successful.

Layer name: point
Geometry: Point
Feature Count: 1
Extent: (11.660233, 48.320915) - (21.675484, 53.763435)
Layer SRS WKT:
(unknown)
FID Column = ogc_fid
Geometry Column = wkb_geometry
altitude: String (6.0)
lat: String (12.0)
lon: String (12.0)
longname: String (17.0)
name: String (17.0)
recnum: String (6.0)
OGRFeature(point):1
  altitude (String) = 215
  lat (String) = +51,4465833
  lon (String) = +19,7692500
  longname (String) = STACJA BENZ
  name (String) = STATOIL
  recnum (String) = 1
  POINT (19.76925 51.446583333333301)


2. The second test I did was against layer with SRID definition, so
EWKB/EWKT stream is returned:


mloskot:~/dev/gdal/_cvs/gdal$ ogr/ogrinfo PG:dbname=ewkb_test ewkt_srid
-sql "select * from ewkt_srid where id=9"
INFO: Open of `PG:dbname=ewkb_test'
      using driver `PostgreSQL' successful.
layer names ignored in combination with -sql.

Layer name: sql_statement
Geometry: Unknown (any)
Feature Count: 1
Layer SRS WKT:
(unknown)
Geometry Column = egeom
id: Integer (0.0)
OGRFeature(sql_statement):0
  id (Integer) = 9


As yo can see, Geometry is still Unknown.
Next,after I define export CPL_DEBUG=ON environment variable, I get
debug message:

OGR: OGRGeometryFactory::createFromWkb() - got corrupt data.

Frank, I suppose the problem is that -sql query with asterix retrieves
data as EWKB, because OGC functions AsBinary or AsText are not used,
so PostGIS internal format is returned.
Do you have similar considerations?

Cheers
-- 
Mateusz Loskot
http://mateusz.loskot.net