Dotaz: Jak v hstore najít klíče odpovídající reg. výrazu

[Petr Bolf]

Dobrý den, prosím o radu. Mám vytvořenou postgresql databázi projektu openstreetmap. Princip je takovýto, mám tři typy geografických objektů, node, way a relation. Můj problém je pro všechny tři typy stejný, takže dále budu hovořit jen o relations.

Relation může mít libovolný počet tagů. Tag je údaj typu klíč: hodnota. V databázi to je řešeno takto:

CREATE TABLE relations
(
  id bigint NOT NULL,
  "version" integer NOT NULL,
  user_id integer NOT NULL,
  tstamp timestamp without time zone NOT NULL,
  changeset_id bigint NOT NULL,
  CONSTRAINT pk_relations PRIMARY KEY (id)
)

CREATE TABLE relation_tags
(
  relation_id bigint NOT NULL,
  k text NOT NULL,
  v text NOT NULL
)

A já chci najít všechny relace, které nějak souvisí se železnicí, tedy mají klíč rail, railway, rail_station atd. Je to snadné:

SELECT relation_id FROM relation_tags WHERE v LIKE 'rail%' GROUP BY relation_id ORDER BY relation_id

No, jenže strukturu tabulky osm změnili tak, že místo tabulky pro tagy je sloupec typu hstore. To se mi líbí, ale mám problém.

CREATE TABLE relations
(
  id bigint NOT NULL,
  "version" integer NOT NULL,
  user_id integer NOT NULL,
  tstamp timestamp without time zone NOT NULL,
  changeset_id bigint NOT NULL,
  tags hstore, --- toto nahrazuje tabulku relation_tags
  CONSTRAINT pk_relations PRIMARY KEY (id)
)

V manuálu jsem našel dost informací. Umím třeba najít všechny řádky, které mají pro určitý klíč hodnotu odpovídající nějakému výrazu :

SELECT id FROM relations WHERE ((tags->'route') LIKE 'rail%');

Ale já nepotřebuji hodnoty, ale klíče a neznám název klíče. Umím najít všechny řádky s klíčem a pomocí OR bych to třeba nějak zvládl:

SELECT id FROM relations WHERE tags ? 'rail' OR tags ? 'railway' ;

ale vidíte, že to není dobrá cesta. Potřebuji najít řádky, které mají ve sloupci tags typu hstore rail%:

SELECT id FROM relations WHERE tags ? 'rail%';

Ale to takhle nefunguje. Řešení, která mně napadají jsou velice krkolomná. Lze to nějak snadno?

díky moc

Petr Bolf

---

---

Odpovědi

[l0gik]

Použij funkci each - rozvine Ti hstore zpátky na tabulku. Něco jako

WHERE exists(SELECT key FROM each(tags) WHERE key LIKE 'rail%')

Akorát to nebudeš moct rozumně zaindexovat, takže asi by si na to chtělo napsat SQL funkci a zaindexovat tu. Pokud ale hodláš vyhledávat ještě silniční, vodní a nevímjaké věci, pak bych si tam dal trigger na změnu hstore a udržoval vedle tabulku s typama.

[Petr Bolf]

Díky moc za nakopnutí. To je přesně ono. Asi mi to pořád myslí spíše v duchu MySQL, tak se dost ztrácím v těch možnostech PostgreSQL