osm2pgsql – Select Relation Member by Role

postgresql

I have a standard postgresql OpenStreetMap database that was filled with data via osm2pgsql. There are different tables for different types (points, lines, polygons, relations) that need to get combined for my specific query:
I want to select all relations with the tags boundary=administrative and admin_level=6. The results need to be searched for a member with the role admin_centre.

Here are my steps by now:

Select member-role list:

SELECT unnest(members)
FROM planet_osm_rels
WHERE ARRAY['admin_level','6']<@tags AND ARRAY['boundary','administrative']<@tags;

Result for one example:

    unnest    
--------------
 n240045827
 admin_centre
 w185481610
 outer
 w191987461
 outer
 w166286278
 outer
 w166290573
 outer
 w65481875
 outer
 w166278211
 outer
 w166278224
 outer
 w166278225
 outer
 w166278227
 outer
 w185148605
 outer
 [...]

The n stands for nodes, the w for ways, followed by the unique id that can be found within the respective table (planet_osm_nodes or planet_osm_line). In this example, the admin_centre is the only node, so we can find members that are nodes with LIKE:

SELECT name
FROM planet_osm_point
JOIN (
    SELECT ltrim(member, 'n')::bigint AS osm_id
    FROM (
        SELECT unnest(members) AS member
        FROM planet_osm_rels
        WHERE ARRAY['boundary','administrative']<@tags AND ARRAY['admin_level','6']<@tags) u
    WHERE member LIKE 'n%') x
USING(osm_id);

Here my question: It is possible that there are several nodes, but with different roles. How could I manage to get only the node that has the role admin_centre? Here it would be n240045827.

The result from above should be sufficient for my question without going too deep into detail about the database structure. The solution would be to take every nth and nth-1 element as a 2D array in order to have the mapping between member and role.

Here is the named relation with its admin_center just for clarification:
https://www.openstreetmap.org/relation/62383

Best Answer

If we can make the assumption that the unnest() operation will always return a set where a member "id" is listed followed by its role in a single column of text entries, then I've written up a quick solution for you.

I made a quick data set as:

CREATE TABLE mylist (entry text);

INSERT INTO mylist VALUES
('n240045827'),
('admin_centre'),
('w185481610'),
('outer'),
('w191987461'),
('outer'),
('w166286278'),
('outer'),
('w166290573'),
('outer'),
('w65481875'),
('outer'),
('w166278211'),
('outer'),
('w166278224'),
('outer'),
('w166278225'),
('outer'),
('w166278227'),
('outer'),
('w185148605'),
('outer');

Assuming we can return this, then the script

WITH numbered AS(
  SELECT row_number() OVER() AS row, entry
  FROM mylist)
  SELECT a.entry
  FROM numbered AS a JOIN numbered AS b
  ON a.row = b.row-1 AND b.entry = 'admin_centre';

will return only n240045827.

How does it work? Well, I just took a straightforward approach to assigning order to the returned list by using row_number() OVER(). In this example, I've used a CTE WITH... to add the row after the fact, but in your case you could add it into your query like so:

...
SELECT ltrim(member, 'n')::bigint AS osm_id
    FROM (
    SELECT row_number() OVER() a AS row, unnest(members) AS member
    FROM planet_osm_rels
...

Finally, I simply join this result to itself, comparing one row to the next by its row number, and looking for the 'admin_centre' text entry.

Here's a SQL Fiddle for testing.

For your perusal:

Common Table Expressions

Window Functions

Edit

If you want to return all rows as column pairs, then use the following approach:

WITH numbered AS(
  SELECT row_number() OVER() AS row, entry
  FROM mylist)
  SELECT a.entry, b.entry
  FROM numbered AS a JOIN numbered AS b
  ON a.row = b.row-1 AND a.row%2 = 1;

Also, here's a SQL Fiddle #2 for testing.

Related Question