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

[Simon Cropper]

On 19/03/12 12:46, Stephen Woodbridge wrote:
> 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
> _______________________________________________
> Discuss mailing list
> Discuss at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/discuss


That kind of worked. At least it cut out some time. Here is the small 
script to create a copy of the attribute table in a DBF directory that 
contains the coordinate data. Unfortunately you still need to parse the 
field to get the X,Y coordinates.

#!/bin/bash

# Extracts attribute table from shapefile and attaches geometry
# data to each record, essentially making a flat file

# Get names of all shapefiles in current directory
for TheFile in *.shp; do

   # Get just name so we can use in SQL
   FileName="${TheFile%.*}"

   # Provide some feedback
   echo "Extracting attribute data from $TheFile file.."

   # Copy, rather than convert, attribute table with coordinate
   # data to new file
   ogr2ogr -sql "SELECT OGR_GEOM_WKT AS GEOM_WKT, * FROM $FileName"
           -overwrite ./dbf $TheFile

done


-- 
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