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.

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;
  • geometry typo: ESPG:900913 :-) – Mikko Koivunalho Jun 23 '15 at 13:51
  • A third possibility: create an index on ST_Transform(way,4326) (useful if you need to support a mix of 4326-queries and 900913-queries). – Toby Speight Jun 23 '15 at 16:17

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.