I imported a large area of OpenStreetMap's planet.osm file into a
postgresql database. The database contains a table called nodes. Each node
has a geometry column called geom and a hstore column called tags. I need
to extract nodes along a line that have certain keys in the tags column. To
do that I use the following query:
SELECT id, tags FROM nodes WHERE ST_DWithin(nodes.geom,
ST_MakeLine('{$geom1}', '{$geom2}'), 0.001) AND tags ? '{$type}';
$geom1 and $geom2 are geometries for start and end points of my line.
The $type variable contains the key I want to search for. Now, it can have
one of the following values: 'historic' or 'tourist'.
The query given above works but it is too slow. I guess searching for a key
in tags column takes too much time. I read about GIN and GIST indexes and I
generated a GIN index using the following query:
CREATE INDEX nodes_tags_idx ON nodes USING GIN(tags);
After creating the index I searched again for nodes using the same first
query but there is no change in performance.
How can I properly use GIN and GIST to index tags column so I can faster
search for nodes that have a certain key in tags column?
Thank you,
Radu-Stefan
