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:
So, how to avoid the multiplication of values, and get the real aggregated number?