2

I have a table planet_osm_roads. The table has one column ref that indicates the code of the road.

Let's say that I have a road coded 'AA1' which is represented by 5 segments (or rows in the database)

osm_id  |   ref | way(geometry)
--------------------
 1         AA1    ...
 2         AA1    ...
 3         AA1    ...
 4         AA1    ... 
 5         AA1    ...

I want a new table where the 5 segments are collapsed into 1, based on their common value in ref.

Is this the correct way?

CREATE TEMP TABLE ref_roads AS
SELECT ref,
       ST_Multi(ST_Union(f.way)) as the_geom
     FROM planet_osm_roads As f
GROUP BY ref;
  • Yes. Is there something in the output that makes you doubt it? – John Powell Mar 9 '16 at 7:31
  • Yes, that should work. Have you tested it? – PyMapr Mar 9 '16 at 7:31
  • How long is it suppose to take on a 400k rows table? – Francesco Mar 9 '16 at 8:15
  • @Francesco, the time it takes to run the query depends on a few variables and without additional info we cannot really answer that. Have you created indexes on the table? and if you have, on which columns? – PyMapr Mar 9 '16 at 8:24
  • 1
    Test with small dataset first or add "limit 100" to your test run. – user30184 Mar 9 '16 at 21:22
1

Your method will work.

You can run this statement to see how many distinct records you have:

SELECT Count(DISTINCT ref) as count_of_ref
FROM planet_osm_roads As f;

And compare that count to the number of records in your new table:

SELECT Count(ref) as count_of_ref
FROM ref_roads;

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.