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

[Simon Cropper]

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.

-- 
Cheers Simon

    Simon Cropper - Open Content Creator / Website Administrator

    Free and Open Source Software Workflow Guides
    ------------------------------------------------------------
    Introduction               http://www.fossworkflowguides.com
    GIS Packages           http://www.fossworkflowguides.com/gis
    bash / Python    http://www.fossworkflowguides.com/scripting