4

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?

6

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://postgis.net/workshops/postgis-intro/knn.html

SELECT
  id
FROM
  nodes
ORDER BY
  nodes.geom <-> 'SRID=4326;POINT(60.220 24.930)'::geometry
LIMIT 10;
  • Thank you very much! This was very helpful for me, now I get results in 1 second aprox.!! – Iñigo Nov 14 '12 at 21:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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