postgis export each row to shapefile

Question

I'm trying to export the boundaries for lots of series of points from a csv. The input data is geometry POINT type, the output geometries would be multipart polygons.

My workflow so far -

load raw csv to postgis,
create geometries and indices,
singles points to multipoint geometries and buffer

SELECT seam, ST_Buffer(ST_Buffer(ST_Multi(ST_Union(the_geom)), 50),-25) as the_geom INTO res_buffers FROM res_points GROUP BY seam;

(Points are regularly spaced at 50m, hence the expansion/reduction of the buffer)
I now want to export each row of the res_buffers table into a shapefile. So 50 rows = 50 shapefiles.

Open to solutions in Python, Postgis/PSQL, ogr or Windows shell.

Does windows shell include cygwin? I would do this using a simple loop in bash, assuming you have some kind of incrementing column in res_buffers that goes from 1 to 50, and wrap that around pgsql2shp using the id in the where clause. I have no idea how to do it with power shell or dos, though I'm sure you can. — John Powell

John Powell John Powell · Accepted Answer · 2014-11-10T08:45:21

Add a unique id to your res_buffers table, if you don't have one already.

Alter table res_buffers add column id serial;

Then you can use a simple loop structure and call pgsql2shp 50 times. I can tell you how to do this with bash, so if you are in windows, you will need cygwin or similar, or adapt this to powershell or dos (which I can't help you with, sorry). The following one-liner run in a shell,

for i in {1..50} ; do pgsql2shp -f $i.shp db_name  "select * from res_buffers where id = $i"; done

will output 1.shp, 2.shp, etc, where db_name is your database name. You can put a path in front of the $i.shp also.

You could also put this in file and make it executable, if you don't like one liners, as,

for i in {1..50}
  do 
    pgsql2shp -f $i.shp db_name  "select * from res_buffers where id = $i"
 done

If you want to do this with Python, MikeT has written an excellent answer on using shapely and fiona to write shape files. This approach is great if you are doing more general work in Python, but, personally, for the single task at hand in this question, nothing beats the simplicity of the pgsql2shp aproach, imho.

postgis export each row to shapefile

1 Answers