1

I need to make a query for my local OSM server which returns a table with 2 columns: user.display_name and the total of nodes of the ways with tag k='waterway' created, edited or deleted by that user.

I think it should be something like:

SELECT
    users.display_name,
    COUNT(current_way_nodes)
FROM
    users
INNER JOIN changesets ON users.id = changesets.user_id
INNER JOIN current_ways ON changesets.id = current_ways.changeset_id
INNER JOIN current_way_tags ON current_ways.id = current_way_tags.way_id
INNER JOIN current_way_nodes ON current_ways.id = current_way_nodes.way_id
WHERE users.display_name = 'foo' AND current_way_tags.k = 'waterway';

But this query gives me the following error:

ERROR:  column "users.display_name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2:     users.display_name,

If someone need to have a look at the schema, it's available here: https://wiki.openstreetmap.org/w/images/5/58/OSM_DB_Schema_2016-12-13.svg

0

1 Answer 1

1

The following code did the job:

SELECT
    users.display_name,
    COUNT(way_nodes)
FROM
    users
INNER JOIN changesets ON users.id = changesets.user_id
INNER JOIN ways ON changesets.id = ways.changeset_id
INNER JOIN way_tags ON ways.way_id = way_tags.way_id
INNER JOIN way_nodes ON ways.way_id = way_nodes.way_id
WHERE way_tags.k = 'waterway'
GROUP BY
    display_name;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.