2

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

Improve this question

2 Answers 2

Reset to default
1

Just use COPY, or better \COPY from PSQL,

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]

Looks something like this

\COPY ( SELECT * FROM tbl WHERE ST_Intersects(myextent, tbl.geom) ) TO 'foo.csv' WITH ( format csv )
Improve this answer
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.