[OSGeo-Discuss] Need for to to convert/deconstruct a shapefile to create a relational table

[Stephen Woodbridge]

On 3/18/2012 7:50 PM, Simon Cropper wrote:
> On 17/03/12 00:06, Stephen Woodbridge wrote:
>> On 3/16/2012 12:52 AM, Simon Cropper wrote:
>>> Hi,
>>>
>>> Does anyone know of a simple means to take a shapefile and create a
>>> either a SQLite or xBase table?
>>>
>>> Essentially it is taking an attached attribute table, inserting the
>>> coordinates in a field and saving the new file in a designated format.
>>>
>>> Most of the data being converted is point data or fixed area samples.
>>> Ideally the converter could record the centroid for grid cells with
>>> details of the furthest point.
>>>
>>> I know of various tools that can do this 'manually' one step at a time
>>> but as I have many files that come regularly, I would like to somehow
>>> automate the process.
>>>
>>
>> I think ogr2ogr that is part of the GDAL release will do this.
>>
>> -Steve W
>> _______________________________________________
>> Discuss mailing list
>> Discuss at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/discuss
>
> Unfortunately ogr2ogr transfers data from one format to another but
> maintains the geospatial data separate from the attribute table.
>
> So if you have a shape file and export to sqlite for example you end up
> with one table with the attribute data and the other with the geospatial
> data. If you export to CSV only the attribute data gets converted -- no
> spatial data is bundled with the info.
>
> I know you can use SQL but you can't easily access the geometry table
> data using ogr2ogr.
>
> What I need is "select data.*, geometry.lat, geometry.long from data,
> geometry where data.siteid==geometry.siteid into newtable" but I can't
> seem to access the spatial data in the shapefile in this way and have
> the data exported into a simple flat table (DBF, CSV).
>
> I tried to see if I could convert to SQLite then export from there but
> the geometry data is stored as a blob field.
>
Does this get you any closer to what you need:

SELECT OGR_GEOM_WKT, * FROM data;

-Steve