Re: [OSGeo-Discuss] Need for to to convert/deconstruct a shapefile to create a relational table
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@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 / Pythonhttp://www.fossworkflowguides.com/scripting ___ Discuss mailing list Discuss@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/discuss
Re: [OSGeo-Discuss] Need for to to convert/deconstruct a shapefile to create a relational table
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@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@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/discuss
Re: [OSGeo-Discuss] Need for to to convert/deconstruct a shapefile to create a relational table
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@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@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 / Pythonhttp://www.fossworkflowguides.com/scripting
___
Discuss mailing list
Discuss@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/discuss
Re: [OSGeo-Discuss] Need for to to convert/deconstruct a shapefile to create a relational table
On 2012-03-18, at 6:46 PM, Stephen Woodbridge wrote: Does this get you any closer to what you need: SELECT OGR_GEOM_WKT, * FROM data; -Steve That's some nice ogr-foo to learn :) Simon, it sounds like you could wrap this up pretty quickly in a python script too. A rough approach example: from osgeo import ogr ds = ogr.Open(/tmp/on_geoname.shp) l1 = ds.GetLayerByIndex(0) for f in range(l1.GetFeatureCount()): printme = [] g1 = l1.GetFeature(f) for fld in range(g1.GetFieldCount()): printme.append(g1.GetFieldAsString(fld)) gr1 = g1.GetGeometryRef() printme.append(str(gr1.GetX()) + - + str(gr1.GetY())) print printme For what it's worth, Tyler ___ Discuss mailing list Discuss@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/discuss
[OSGeo-Discuss] Need for to to convert/deconstruct a shapefile to create a relational table
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. -- 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 / Pythonhttp://www.fossworkflowguides.com/scripting ___ Discuss mailing list Discuss@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/discuss
Re: [OSGeo-Discuss] Need for to to convert/deconstruct a shapefile to create a relational table
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@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/discuss
Re: [OSGeo-Discuss] Need for to to convert/deconstruct a shapefile to create a relational table
On 03/16/2012 06: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.
Since spatialite can be run with SQL commands as parameters on
the command line (i.e. spatialite data.sqlite "SELECT * FROM
table1;") you could pass the '.loaddbf' command to spatialite, and
with some wildcard trickery loop thru all your shapefiles:
for f in *.shp; do spatialite data.sqlite ".loaddbf ${f}"; done
or so.
--
Micha Silver
Arava Development Co. +972-52-3665918
http://surfaces.co.il
___
Discuss mailing list
Discuss@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/discuss
RE: [OSGeo-Discuss] Need for to to convert/deconstruct a shapefile to create a relational table
This could also likely be scripted using OGR’s Python bindings.
From: discuss-boun...@lists.osgeo.org [mailto:discuss-boun...@lists.osgeo.org]
On Behalf Of Micha Silver
Sent: Friday, March 16, 2012 11:04 AM
To: OSGeo Discussions
Subject: Re: [OSGeo-Discuss] Need for to to convert/deconstruct a shapefile to
create a relational table
On 03/16/2012 06: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.
Since spatialite can be run with SQL commands as parameters on the command line
(i.e. spatialite data.sqlite SELECT * FROM table1;) you could pass the
'.loaddbf' command to spatialite, and with some wildcard trickery loop thru all
your shapefiles:
for f in *.shp; do spatialite data.sqlite .loaddbf ${f}; done
or so.
--
Micha Silver
Arava Development Co. +972-52-3665918
http://surfaces.co.il
___
Discuss mailing list
Discuss@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/discuss
