Improve database performance for geospatial

Question

I have a database with osm data imported with osmosis tool. I want to search for the nearest POIs from a given point. For this purpose, I perform this query:

select id from nodes
where geom='0101000020E6100000546C713216920DC065767176C62C4540'

After this I would like to search tags for the selected node in node_tags table but my problem is that the database has 31 million rows, and the query takes more than 30 seconds. I have this columns in node tables:

• id, version, user_id, tstamp, changeset_id, geom

I run the following sentence to ensure index in "geom" column:

CREATE INDEX idx_nodes_geom
ON nodes USING gist (geom );

But it doesn't work. Query takes more than 30 seconds.

I have the following configuration in PostgreSQL database:

• Shared buffers: 768 MB
• Work_mem: 32MB
• maintenance_work_mem: 256 MB
• wal_buffers: 2 MB
• checkpoint_segments: 12
• random_page_cost: 2.0
• seq_page_cost: 1.0

Any idea to improve it?

Answer 1

When creating a spatial index on a table it is important to run "vacuum analyze <table>" after that.

For finding nearest points you can use operator <-> introduced in PostGIS 2.0. It actually gives you the distance between two points. More info can be found here: http://workshops.opengeo.org/postgis-intro/knn.html

SELECT
  id
FROM
  nodes
ORDER BY
  nodes.geom <-> 'SRID=4326;POINT(60.220 24.930)'::geometry
LIMIT 10;