|
»
« Return to Thread: Odd behavior with PostGIS
Re: Odd behavior with PostGIS
by Lennard-2
::
Rate this Message:
Robert Coup wrote:
> Yeah, it's something I've been thinking about cleaning up.
>
> Currently by default it:
> - find the geometry from the geometry_columns table
> - figures out whether it needs to do transforms based on the map
> projection & the geometry field projection
> - takes all your filters and figures out which attribute fields it needs
> - constructs a query for those attribute fields and the geometry
> - does a where clause for the map extent against the geometry field
> - runs it
>
> Bad things:
> - filters are all done in mapnik (if you're drawing all the data that's
> fine, otherwise it gets too much data out)
This is a really important factor in the speed of tile rendering. In the
past week, I've worked on the complete osm.xml file, adjusting filters
and sql queries to optimize what's fetched from the db, and how many
mapnik filters are needed.
In the end, I achieved an average speedup by a factor of 1.75 on my
server*. The downside would be that some sql queries are getting rather
long, and not as easy to understand for some people (although no real
trickery is involved). Adding new filters means you also have to edit
the sql.
I'll be commiting the simpler cases to SVN first. We'll have to see
whether some of the more complex or longer queries can be reworked by
doing more preprocessing in osm2pgsql.
* Running mod_tile's speedtest on a .5x.5 degree area boxed around
Antwerp, z0 to z18.
> - using custom sql is ... interesting - the general pattern is
> table="(SELECT blah FROM ... WHERE ...) AS x", but you need to make sure
> field names all line up.
For my optimisation experiment, I wrote a small perl script to fetch all
the filters from the rules, eliminating duplicates, and all fields used
in the Text/ShieldSymbolizer. I've been thinking about extending it to
do better analysis and even suggest queries for a given layer/style(s)
combination.
> - edit the map xml / script, add a new filter and it just works
> - great for simple use cases
For average and simple stylesheets, this would usually suffice.
> I've been wondering whether we do something like:
>
> table= -> current behaviour
The current behaviour already breaks if you add a filter without adding
it to the select in a subquery, unless you're doing 'select *' there.
> sql= -> all on your own, you need to make sure the
> fields/geometries/everything are correct.
+1 though, although I hope it would still add the bbox, obviously.
--
Lennard
_______________________________________________
Mapnik-users mailing list
Mapnik-users@...
https://lists.berlios.de/mailman/listinfo/mapnik-users
> Yeah, it's something I've been thinking about cleaning up.
>
> Currently by default it:
> - find the geometry from the geometry_columns table
> - figures out whether it needs to do transforms based on the map
> projection & the geometry field projection
> - takes all your filters and figures out which attribute fields it needs
> - constructs a query for those attribute fields and the geometry
> - does a where clause for the map extent against the geometry field
> - runs it
>
> Bad things:
> - filters are all done in mapnik (if you're drawing all the data that's
> fine, otherwise it gets too much data out)
This is a really important factor in the speed of tile rendering. In the
past week, I've worked on the complete osm.xml file, adjusting filters
and sql queries to optimize what's fetched from the db, and how many
mapnik filters are needed.
In the end, I achieved an average speedup by a factor of 1.75 on my
server*. The downside would be that some sql queries are getting rather
long, and not as easy to understand for some people (although no real
trickery is involved). Adding new filters means you also have to edit
the sql.
I'll be commiting the simpler cases to SVN first. We'll have to see
whether some of the more complex or longer queries can be reworked by
doing more preprocessing in osm2pgsql.
* Running mod_tile's speedtest on a .5x.5 degree area boxed around
Antwerp, z0 to z18.
> - using custom sql is ... interesting - the general pattern is
> table="(SELECT blah FROM ... WHERE ...) AS x", but you need to make sure
> field names all line up.
For my optimisation experiment, I wrote a small perl script to fetch all
the filters from the rules, eliminating duplicates, and all fields used
in the Text/ShieldSymbolizer. I've been thinking about extending it to
do better analysis and even suggest queries for a given layer/style(s)
combination.
> - edit the map xml / script, add a new filter and it just works
> - great for simple use cases
For average and simple stylesheets, this would usually suffice.
> I've been wondering whether we do something like:
>
> table= -> current behaviour
The current behaviour already breaks if you add a filter without adding
it to the select in a subquery, unless you're doing 'select *' there.
> sql= -> all on your own, you need to make sure the
> fields/geometries/everything are correct.
+1 though, although I hope it would still add the bbox, obviously.
--
Lennard
_______________________________________________
Mapnik-users mailing list
Mapnik-users@...
https://lists.berlios.de/mailman/listinfo/mapnik-users
« Return to Thread: Odd behavior with PostGIS
| Free embeddable forum powered by Nabble | Forum Help |
