ogr2ogr -sql output lost geometry

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

ogr2ogr -sql output lost geometry

Thomas Windholz
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
 
Any help/hints are appreciated.
 
Thanks,
Thomas
 


Yahoo! for Good
Click here to donate to the Hurricane Katrina relief effort.
_______________________________________________
Gdal-dev mailing list
[hidden email]
http://lists.maptools.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: ogr2ogr -sql output lost geometry

Frank Warmerdam-2
On 9/13/05, Thomas Windholz <[hidden email]> 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, [hidden email]
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | Geospatial Programmer for Rent

_______________________________________________
Gdal-dev mailing list
[hidden email]
http://lists.maptools.org/mailman/listinfo/gdal-dev