Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
1  
What Server is Postgres on? How much RAM? 4-16GB would ideal. Indexing postgis.refractions.net/documentation/manual-1.3/… –  Mapperz Nov 14 '12 at 21:10
    
User has cloned this post here: stackoverflow.com/q/13387640/398670 –  Craig Ringer Nov 14 '12 at 23:30
1  
BTW, you should really show explain analyze output when asking performance questions. Paste it to explain.depesz.com and link to it in the question. –  Craig Ringer Nov 14 '12 at 23:32
add comment

1 Answer

up vote 6 down vote accepted

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;
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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