[Top][All Lists]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [Maposmatic-dev] Re: PostGIS help for MapOSMatic

From: Vincent de Chateau-Thierry
Subject: Re: [Maposmatic-dev] Re: PostGIS help for MapOSMatic
Date: Sun, 28 Feb 2010 22:40:58 +0100
User-agent: Thunderbird (Windows/20090812)

Hi all,

Jean-Christophe Arnu a écrit :
Hi Thomas,
could you run the (long running) queries with explain/explain analyze
and post the results on the wiki ?


The information given by explain are quite useful to optimize queries.

Maybe some suggestions below. I have not tested any of them yet since I do not have any PostGis base at home.

- before the run : using a table instead of a view for 'cities_area_by_name' may be of interest in order to build a dedicated spatial index for city boundaries ? If this table/view is built only one time for each base update (daily) the time taken may be of interest,

- during the single city run, as explained by Thomas here : there is a significant performance issue involving the "st_intersects(way, cities_area_by_name.area)" part of the query. A possible cause would be a large scan of the planet_osm_line in order to select the ways located inside the Toulouse_or_whatever_city polygon without usage of a spatial index. You should already have a spatial index on the planet_osm_line table, but despite the note regarding indexes here :
I have sometimes encountered problems with st_intersects where indexes were not taken into account. A way to ensure that spatial indexes are used is to add a "&&" part to the query maybe like this (2nd line added) :

where trim(name) != '' and highway is not null
and way && cities_area_by_name.area
and case
when cities_area_by_name.area is null
then true
else st_intersects(way, cities_area_by_name.area)

Hopte this helps,


reply via email to

[Prev in Thread] Current Thread [Next in Thread]