Re: [Maposmatic-dev] PostGIS help for MapOSMatic

[Emilie Laffray]

On 27/02/2010 11:36, Thomas Petazzoni wrote:
> 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
>   
Hello,

 As someone mentioned, having the explain analyze would give us a better
clue.
One quick thing to mention which won't have much impact initially, but
try to get rid of your ST_Transform, as it can be quite costly. Why do
you create your grid initially in a different SRID? You could create
your grid in the SRID you have chosen and then store it directly in 900913.
Also, how are you creating your database?
I don't see why you are doing a ST_Buildarea. If you are using osm2pgsql
it should create the polygon when you import the data. Also, if you want
to speed up the system without using a view, you could always create a
partial index on the view element, but again, I suspect it doesn't
matter much here.
I think the culprit is your case statement. Try moving the ST_Intersects
(way, cities_area_by_name.area) inside the left join parameter. Also,
can't you do the test if the city exist or not before (whether in the
code or in the function it doesn't matter)? If you could do that, then
you could write two different select statements depending on whether the
system exists or not, getting rid of that ugly case statement.
One other solution to your problem is that you could create a different
grid which is a result of ST_Intersection with the geometry of the town
and the grid. Your final query would then be like your last query
without the ST_Intersects.
Again, only experimentation will tell. Do you have a test account which
has access to the database? I don't mind doing some quick evaluation
tomorrow. It is very hard to tell when you cannot see the data in it itself.

Emilie Laffray

signature.asc
Description: OpenPGP digital signature