Obviously, there can be multiple rows with the same value in table1.sri
and / or in table2.seg_guid
. That's the only logical explanation for the multiplied row count in your query result.
Which of both is impossible to tell from the information you provided. But the name "trucknetwork" indicates there should be many "street names" in the result, so your task seems contradictory at the outset.
My educated guess is that the multiplication happens in table1
(many start and end points of roads in the same truck network). There are various techniques to reduce to a single pick. The best one depends on information not in the question. Since you get so many result rows, a LATERAL
join with LIMIT 1
is a hot contender. Be sure to support it with an index.
Since you want to join to exactly 1 row in the result for each row in trucknetwork
, you have to define which row to pick from a set of multiple matches. More information we don't have. I pick an arbitrary winner.
And there is also the possibility that some of the rows in trucknetwork
might find no matching row in table1
and/or table2
at all. In an ideal world, referential integrity would be enforced with FOREIGN KEY
constraints. To make do with what we have, use LEFT [OUTER] JOIN
to avoid eliminating trucknetwork
rows (which would probably indicate data errors in table1
or table2
).
So:
CREATE TABLE network_ex AS
SELECT shp.descriptio AS descr, shp.road_type, shp.geom, t2.s_name AS street_name
FROM trucknetwork shp
LEFT JOIN LATERAL (
SELECT seg_guid
FROM table1
WHERE sri = shp.sri
-- ORDER BY ??? -- define which one to pick if that matters
LIMIT 1 -- ! just the one !
) t1 ON true
LEFT JOIN table2 t2 USING (seg_guid);
Ideally, you would have these two multicolumn indexes to make this fast:
CREATE INDEX table1_sri_seg_guid_idx ON table1 (sri, seg_guid);
CREATE INDEX table2_seg_guid_s_name_idx ON table2 (seg_guid, s_name);
If there can be duplication in table2
(as well), reduce to a single match in a similar fashion. Again, the best technique depends on the complete picture.
Alternatively, you could aggregate all (distinct) road names into a single string or array in the result ...
Related:
Asides:
- Use
CREATE TABLE AS
. SELECT INTO
is discouraged:
- Avoid unquoted CaMeL-case identifiers in Postgres.
create temp table tbl as
select * from
(values (1,01,03),(2,03,03),(3,09,05),(4,08,05),(5,08,03),(6,08,03),(7,08,03),(8,08,03),(9,08,09)) t (id, x2,x3);
First you need to generate a partition by every group of X3 values:
This sentence uses LAG function to mark when X3 changes from the previous row.
select id, x2, x3, case when coalesce(lag(x3) over (order by id), x3) = x3 then 0 else 1 end c
from tbl
order by id
Then you can establish a partition using:
select id, x2, x3, sum(cnt.c) over (order by id) as part
from cnt
order by id
Then simply get the sum of X2 grouping by every partition.
with cnt as
(
select id, x2, x3, case when coalesce(lag(x3) over (order by id), x3) = x3 then 0 else 1 end c
from tbl
order by id
), cnt1 as
(
select id, x2, x3, sum(cnt.c) over (order by id) as part
from cnt
order by id
)
select sum(x2) xx2, min(x3) xx3
from cnt1
group by part
order by part
;
The final result:
+-----+-----+
| X2 | X3 |
+-----+-----+
| 4 | 3 |
+-----+-----+
| 17 | 5 |
+-----+-----+
| 32 | 3 |
+-----+-----+
| 8 | 9 |
+-----+-----+
Check it here: http://rextester.com/YPU13325
Best Answer
I've used this good answer of Erwin Brandstetter
But keep in mind that your solution requires a dynamic query because I'm sure that
creator
table has different records from these you has posted here.I've used a view to get results for
roads
andblocks
.dbfiddle here