2

I have a vector file of river networks in QGIS. Some of the rivers are split up it multiple rows. What I'd like to do is to aggregate the rivers based on the attribute in one column (S_NAME) and afterwards calculate the length of each aggregated river.

I know there must be some PostGIS commands to do that. I'd be fine with any pointer to anything that relates to it. The pseudo-code I have in my head would look something like this:

output = layer -> groupyby(S_NAME) -> st_distance(.)

I know this is really bad, but maybe someone has any idea:.

The link to the data (18k rows...) is here: https://geoportal.bafg.de/inspire/download/reporting_units/riverWaterBodyForWFD/datasetfeed.xml

  • 1
    Since the data covers all of Germany, I suggest you transform the data to 25832. – Erik Sep 2 at 7:24
  • Right, valid point:) – Lenn Sep 2 at 9:11
  • If the data has a geographic reference, I'd rather suggest to keep it that way; you have full GEOGRAPHY support via direct casting and functional indexing -> no projection selection, no distortions, maximum precision. Also, _ST_BestSRID(geog) will give you UTM based CRS from geographic reference systems where possible (not on poles, geometry fits in one zone +- half a zone; handle with care, as it eventually falls back to Mercator) – geozelot Sep 2 at 9:12
5

The data is in EPSG:4258 - ETRS89 - Geographic, so the units aren't usable for length, etc.

You need to transform the geometry to something in units you can work with - meters, I'm guessing, so what the UTM zone in Germany that would be appropriate for this data still needs adding, but here is the SQL:

select
    r.s_name
    , ST_Length(ST_Transform(st_union(geom), ????)) as river_length
from
    public."RWBODY_DE" as r
group by
    r.s_name

Where ???? is of course the SRID of the cooredinate system in meters you'll use to get length in meters.

| improve this answer | |
  • 1
    If high precision is of importance, while having the least hassle to cover the area of Germany within the minimal tolerance of distortion, you can simply cast to GEOGRAPHY; ST_Length([ST_Union|ST_Collect](geom)::GEOGRAPHY) – geozelot Sep 2 at 8:51
  • Thanks so much for the help:)! – Lenn Sep 2 at 9:08
  • Just another, really really beginner question (I'm just trying to get a little more into spatial sql): Can I do all this in the database manager in qgis, or would it be better to use something like pgadmin? – Lenn Sep 2 at 9:10
  • 2
    @Lenn Yes. DB Manager is a fully capable, yet simplistic DB client, made for working with spatial data and its seamless integration into the QGIS environment. Go for it. pgAdmin is a bare DB admin tool, often my tool of choice for it's simplicity (at least as long as pgAdmin3 is still maintained...), but has no mature analytical interface. I'm sure DPSSpatial will suggest dBeaver as their favorite tool; a massive, multi-platform, open source DB client suite with rich analytical features, but a rather steep entry for beginners due to its functionality overload. – geozelot Sep 2 at 9:26
  • 1
    haha thanks so much for the discussion:)! I think I'll first just get starting a little bit more with postgis and afterwards I chose the right tool for it. Any resource on get starting-l with postgis and dive a little deeper is also super welcome:) I already saw that the postgis-webiste has a great tutorial. And actually the qgis-website has also some slides I think. – Lenn Sep 4 at 7:19
2

You also could use the GUI.

Run dissolve on the river layer based on S_NAME, then use the field calculator to add the $length to each river. If your CRS is not suited for this, you need to save your layer to a fitting one first.

| improve this answer | |
  • Thanks a lot for this as well:) I almost always used to use the gui, but I think this time I'm trying it with some sql – Lenn Sep 2 at 9:09

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.