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.