[Gdal-dev] ogr2ogr MapINfo bug?

[Bill Thoen]

Mateusz, Frank,

Thanks for looking into this. I don't really understand what the issue is,
but even when I don't use the asterisk with the -sql switch, I still get an
"unknown" geometry. My Postgres/PostGIS table does have an SRID associated
with it and the geometry column is properly referenced in the
geometry_columns table.

Also, there is still an additional problem with MapInfo output. For
example, when I try the -where switch with the output being MapInfo Tab,
the resulting table has no data in the *.id file (the map feature index). 

This produces a MapInfo table with a corrupt *.id file:
ogr2ogr -f "MapInfo File" -where "fips='17055'" -a_srs WGS84 test.tab
  "PG:dbname=spatial1" counties

But if I issue the same command with the output set to shape file, it
works. The following command works perfectly:

ogr2ogr -f "ESRI Shapefile" -where "fips='17055'" -a_srs WGS84 test.shp
  "PG:dbname=spatial1" counties

What do you recommend I do at this point? Is there a workaround for MapInfo
output?  Should I submit a bug report?  It sounds like you know more about
what's happening here than I do, but I just wanted to know if I need to do
anything else.

- Bill Thoen
 

On Sat, Nov 25, 2006 at 09:27:42PM +0100, Mateusz Loskot wrote:
> 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
> _______________________________________________
> Gdal-dev mailing list
> Gdal-dev at lists.maptools.org
> http://lists.maptools.org/mailman/listinfo/gdal-dev
>