amat-mi / osmopgviews Goto Github PK

Allow user to define views on relations.
We should be able to specify geometry (multilinestring, multipoint or geometrycollection), and which roles to include in the geometry collection.

An example for routes:

CREATE OR REPLACE VIEW osm_views.routes AS
SELECT r.id,
g.geom,
r.tags -> 'ref'::text AS ref,
r.tags -> 'route'::text AS route,
r.tags -> 'from'::text AS inizio,
r.tags -> 'to'::text AS fine,
r.tags -> 'name'::text AS nome,
r.tags -> 'operator'::text AS operator,
r.tags -> 'network'::text AS network,
r.tags
FROM relations r
LEFT JOIN ( SELECT m.relation_id,
st_collect(w.linestring) AS geom
FROM relation_members m
LEFT JOIN ways w ON m.member_type = 'W'::bpchar AND m.member_id = w.id
GROUP BY m.relation_id) g ON r.id = g.relation_id
WHERE (r.tags -> 'type'::text) = 'route'::text
ORDER BY r.tags -> 'ref'::text;

Optionally show username by joining with users table.

• Parse a specific option in meta, i.e. users.name or username
• Generate SQL accordingly

Documentation on how to write the view files is missing.

We should be able to optionally encapsulate the generated sql in a raw sql:

raw=select v.*, n.* from v left join nodes n on ... where ...

If raw is present, the generated sql becomes:

create view xyz as
with v as (<previous view sql select >) <raw_statement>

Sometimes I may need to use a tag in the where clause, but I don't want it to be in the column list.

We could use a modifier for tag names, like "-tag_name", in tags list.