OpenStreetMap Data Lookup Slow in PostGIS

Question

I have imported OpenStreetMap data (Europe) into PostgreSQL 9.1 database with the help of osm2pgsql and filtered out everything expect administrative areas. I have GIST index for polygon (way) column. My planet_osm_polygon table has about 250 000 rows.

I want to make lookups to find all the administrative areas on which a set of coordinates is located. Currently I'm using SQL

SELECT osm_id,admin_level,boundary,name FROM planet_osm_polygon \
WHERE ST_Contains(ST_Transform(way,4326), \
ST_GeomFromText('POINT(val1 val2)', 4326)) ORDER BY admin_level;

But the query is too slow. How can I optimize the query and/or datastructure?

Some things that I have considered are:

• Put bigger areas (countries) in an another table and connect them to the main table with foreign keys.
• Change polygon datatype from geometry to geography (but then I have to convert all lookups because they are grades?).
• Simplify the polygons with ST_SimplifyPreserveTopology().
• Move index to a faster disk or memory if possible.

Answer 1

The slow part of your query is probably the ST_Contains(ST_Transform(way,4326),..), postgres is having to transform every way in the table to run this query. There are two options to fix this:

If the main purpose of your database is this type of query it might be more efficient to reimport and store the geometries as EPSG:4326 instead of the ESPG:900913 that osm2pgsql defaults to.

The easier option is to rewrite your query to transform the geometry your querying on rather than all the geometries in the database:

SELECT osm_id,admin_level,boundary,name FROM planet_osm_polygon 
WHERE ST_Contains(way,
ST_Transform(ST_GeomFromText('POINT(val1 val2)', 4326), 900013))
ORDER BY admin_level;