I've got 100+ shapefiles of contours with the following fields:

class (text)

altitude (double)

geom (geometry)

And I need to upload them into a single table in PostgreSQL.

Now, I've managed to upload about 50 of them using QGIS and pgAdmin III. I'll upload them each as their own table using QGIS' DB Manager, and then using pgAdmin III I'll run an insert query to simply tag it onto the end.

The trouble is, the other 50 shapefiles overlap with other shapes I've uploaded. The ones already uploaded are fine - no overlaps - but the remaining 50 all need some amending so we're not doubling up on features. For instance, one shapefile may be directly next to another that's already been inserted, and the two slightly overlap.

I would imagine using an ST_Difference would do this but I'm having trouble with the syntax, as well as preserving the data. There is also some difficulty with the size of the data that's already in there (500,000+ records).

I should mention that the geometry in question is linestring.

Currently I think I'm looking at having to chop this all down manually, which will be pretty time-consuming, and was wondering if anyone would post an example of using ST_Difference with two tables in order to chop table A down so that it doesn't include anything from table B, but still preserve the class and altitude?

EDIT - Alternatively, if anyone knows how to make polygons out of linestrings/polylines (so essentially we get an "outline" of the shapefile coverage) that would be great, as I could just generate and clip on them locally. I did experiment with concave hull, will have to try that again. It always stopped at the end (currently using QGIS 2.18.13).

  • 1
    I'd recommend to use ogr2ogr or shp2pgsql in a batch file (what OS?) to upload your shapefiles (see e.g. here). as for the overlaps, there are multiple options along the way, or after upload, the simplest being to ST_Union based on altitude (or by altitude + connection) or maybe ST_Difference even, as you mentioned, in an UPDATE. it's still not trivial, but, firstly, are command line options possible for you? – geozelot Aug 29 '18 at 7:55
  • oh, also, do you intend on keeping the geometries seperated as in each shapefile? could the class field get merged for adjacent lines with the same altitude? – geozelot Aug 29 '18 at 10:14
  • Hmm... some good options. Don't know about using a command line, haven't done so in the past. And I'm not classifying the geometries separately by shapefile (I assume you mean having a filename field, indicating which file they came from). However, I am worried about the speed of the server and that it is trying to serve operations to other people at the same time - I tried an ST_Union on a single shapefile yesterday (about 10,000 records) and it hung the server. I may well try again, but it does make me hesitant and makes me think a manual process may be best. – user25730 Aug 29 '18 at 22:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.