How to dump a postgreSQL/postgis sample database filtered by a spatial extent? My goal is to extract a subset of my postgres/gis database to create a sample dump which has to have the same data model with relations between tables and views, but which has a smaller spatial extent.

My first idea was to select only the features in my spatial extent and then extract these but I'm not sure I can do it without losing some information from the data model.

The second idea is to import only the data that I want in a new db, filtered with spatial extent (quite long).

The third idea is to copy completely the db and then filter the undesired features.

Which idea is the best? Is there a 4th idea that could be easier/quicker? The data model is quite complex : https://github.com/qwat/qwat-data-model
Might not be the most elegant solution, but you can use the pg_dump utility to copy the DB and then overwrite the data in the spatial tables of the new, subset DB using the ogr2ogr utility. Run pg_restore and then ogr2ogr with your clipping method of choice. To overwrite the spatial data so that it is constrained to the extent of a shapefile, for example, run something like:

ogr2ogr -overwrite -update -clipsrc clip_shape.shp PG:"host=subsethost user=subsetloginname dbname=subsetdb password=subsetpassword" PG:"host=originalhost user=originalloginname dbname=originaldb password=originalpassword"


Check pg_dump and ogr2ogr documentation (linked) for details on the optional switches. Combining the two utilities should get you squared away.