4

I want to make a multiple conditioned spatial query in PostGIS 2.0/PostgreSQL 9.3., OS Windows 7. There are 2 tables, cadastre (Polygon) and roads (Linestring).

Query: Select the sum (one number) of the length of all roads which are longer than 500 m, and which intersect cadastral parcels with area greater than 2000 m2.

This code produces duplicate sums for all roads >500 intersecting parcels >2000, which is obvious if the sums are grouped by parcel id.

SELECT sum(ST_Length(r.geom)) AS total_length
FROM roads AS r
JOIN cadastre AS c
ON ST_Intersects(r.geom,c.geom)
WHERE ST_Length(r.geom)>500 AND ST_Area(c.geom)>2000;

Duplicate values of road lengths:

Duplicate values:

So, how to avoid the multiplication of values, and get the real aggregated number?

  • If you are using sum without a group by your result should be a single number. I am not totally clear what your result set is showing in relation to the posted query. – John Powell Jan 27 '15 at 10:27
  • Result is a single number, but it is much greater than it should be, because of those duplicated values which are included in final result. I want to exclude these duplicates and get real number. The point is that if there are two parcels >2000 and road >500 is crossing both of them, the road is still one, but it is summed twice (for each parcel). – geof Jan 27 '15 at 10:44
  • Can't you group by road id? – John Powell Jan 27 '15 at 11:09
  • I tried, but it still gives too big values. In the original table the longest road is 22 383 m, second is only 8 250 m. In summed table grouped by road_id there are values 30 722 m, 24 752 m, 22 383 m... which are somehow exaggerated. Number of elements is similar, 14 roads >500m in original, and 13 roads in summed table. Sum of all values should be the same in both tables, but it isn't. – geof Jan 27 '15 at 11:52
  • Do the road lengths in the above table have the same road id? – John Powell Jan 27 '15 at 14:59
4

Try this

    SELECT SUM(length) As length
      FROM  (SELECT DISTINCT ON (r.gid) r.gid, ST_Length(r.geom) As length 
                FROM roads AS r
                     JOIN cadastre AS c
                          ON ST_Intersects(r.geom,c.geom)
             WHERE ST_Length(r.geom)>500 AND ST_Area(c.geom)>2000
                ORDER BY r.gid ) As rc;
  • Thank you very much, this works :) Could you just explain what you did in this query, because I don't understand SELECT DISTINCT ON...? In my case, I tested first query in Python by putting counter on every time he adds length. It has added 30 times, instead of 13-14. This yours gives exact result. – geof Jan 28 '15 at 15:07
  • DISTINCT ON is a construct unique to PostgreSQL. What it does is whatever is in the ON(...) it considers a unique identifier and it will return at most one record for that ON(.. ) set of fields. The ORDER BY must start with the fields in the ON(..) and then followed by the order you want to use to break ties. In your case since all will be the same for a given r.gid, it doesn't matter which one you pick since they'll all have the same length so no additional ORDER BY fields required. – LR1234567 Jan 28 '15 at 15:10
  • Oh BTW we have all these tricks covered in our PostgreSQL: Up and Running and PostGIS In Action books. PostGIS in Action 2nd is due out soon. PostgreSQL: Up and Running 2nd came out just last month. postgis.us – LR1234567 Jan 28 '15 at 15:14
  • @LR1234567. Nice plug. Btw, I have found you and Regina's blog to be an amazing source of Postgres/Postgis info. Keep up the good work. – John Powell Jan 29 '15 at 21:39

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.