[Gdal-dev] ogr2ogr -sql output lost geometry

[Frank Warmerdam]

On 9/13/05, Thomas Windholz <thomw00 at yahoo.com> wrote:
>  
> All, 
>   
>  
> I used ogr2ogr to export a point layer from
> postgres/postgis into a shapefile ... everything worked
> fine (I can open it and load/view locations). Then I used a basic SQL
> statement (-sql "SELECT * FROM ksamples WHERE chloroph=108") and all
> geometry was lost (cannot load locations), yet ksamples.dbf table is
> returned ok (only the records having chloroph=108 are in the dbf table). 
>   
> Originally I tried to use gdal (ogr), specifically poDS->ExecuteSQL(...) to
> retrieve spatial locations based on a joined non-spatial table's attribute
> information. 
>   
> pszSQLStatement = "SELECT * FROM test INNER JOIN ksample ON test.c_id =
> ksample.chloroph WHERE something = 467"; 
> poResultSet = poDS->ExecuteSQL( pszSQLStatement, NULL, NULL ); 
>   
> The poResultSet had not spatial attributes, but all the non-spatial values
> were ok. First, since I am new to gdal and postgis (I am -was- from the ESRI
> world) I thought it was just my portion of the code ... but as mentioned
> above ogr2ogr does the same? 
>   
> I noticed in the archives of April 05 someone posting a similar issue ...
> but no solutions that I could find. 
>   
> Using: FWTools0.9.8, PostgreSQL 1.2.1, PostGIS 1.0.2 

Thomas, 

There were substantial fixes made to the Postgres driver shortly
before GDAL 1.3.0 was release (august).  One major fix was
related to capturing geometries from SELECT statements for
PostGIS 1.0+ databases.  

I would suggest testing with FWTools 0.9.9 which should include
these fixes.

I'm not to clear whether the join you referenced was evaluated
against a PostGIS database, or again shapefiles.  If PostGIS
then the problem is likely the one fixed in 0.9.9.  If shapefiles, 
please be aware that spatial data from the joined table is discarded.
If none of that explains your issue then please restate, possibly 
with some test data. 

Best regards,
-- 
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | Geospatial Programmer for Rent