[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Maposmatic-dev] Re: PostGIS help for MapOSMatic
From: |
Vincent Picavet |
Subject: |
[Maposmatic-dev] Re: PostGIS help for MapOSMatic |
Date: |
Mon, 1 Mar 2010 12:53:39 +0100 |
Hi all,
I just read very quickly the problem description, hence this is a first
intuitive idea, not thoroughly thought about :
Could you try creating the following index, analyze planet_osm_line and re-run
the original query ?
create index idx_pol_citybound on planet_osm_line (name) where
boundary='administrative' and admin_level='8';
Other solutions coming to my mind are, most of them already suggested :
* a city table with indexed geometry recreated by a trigger on the way's table
(or by a batch on import)
* a stored procedure as suggested by JC
* rewriting the query slightly to be sure that the maximal selectivity is
choosen first, and indexes (on geometries particularly) are used.
I'll have more time at the end of the week to look into this problem, but we'll
definitly need an explain output or a database access to be able to
troubleshoot correctly.
A+
Vincent
--
Vincent Picavet -- address@hidden
Oslandia - Engineering your GIS
Le sam 27/02/10 12:36 , Thomas Petazzoni a écrit::
Hi Vincent, Jean-Christophe, Guilhem and Sébastien,
[ This message is CC'ed to the development mailing list of MapOSMatic,
which is why I'm writing to you in english. Please keep the list in
the CC list even if you're not subscribed. I'll moderate your
messages manually. Thanks! ]
I'm writing to you concerning a PostgreSQL/PostGIS request optimization
problem we have in MapOSMatic (http://www.maposmatic.org). The request
in question is the one that gives us the list of the streets and the
squares they intersect for a given city.
Currently, for a city like Toulouse, the request takes 4 minutes and 39
seconds, but our experiment show that it could be optimized down to 3.2
seconds. But we don't know exactly how to generalize our experiments of
optimization.
Inside of writing a long e-mail, I've written a detailed explanation of
the request, the problem and the analysis we've done so far at
http://wiki.maposmatic.org/doku.php?id=dev:request_optimization.
If you have the time, could you have a look and tell us what we are
doing wrong ? Solving this problem would probably allow us to limit the
load on the server and to generate maps for larger cities. Of course,
if you need details about the request or the environment, don't
hesitate.
Thanks a lot for your help,
Thomas
--
Thomas Petazzoni http://thomas.enix.org
Promouvoir et défendre le Logiciel Libre http://www.april.org
Logiciels Libres à Toulouse http://www.toulibre.org