[postgis-users] PostGIS - server side optimization

[Jordi Ferran]

Hi,

Thanks to all for your information. I must say that you helped me.

- About data organization: split the data using multiple tables is not
a solution. If you need to use all that information does not matter
where the data is located.

- About data optimization: now I use a column to recover and use
information about geometry much faster. The SQL processing time gain
is about 0.8 seconds working with a 30500 row set. The new query:

SELECT AsText(Simplify(geom, 14)) AS geometry
FROM "TERRASSA-1_TABLE"
WHERE (
  geom_length > 14
  AND geom @ GeometryFromText('BOX3D(414758.439692
4596794.509,421338.876 4604365.807929513)', -1)
  )

- I realized thanks to Andreas Neumann that the Simplify() operation
performs a business logic similar to the one I done on the renderer.
Now I combine the PostGIS Simplify() function and the Renderer
component optimization algorithm. The overall response time greatly
decreased and it is now a mere 1.6 seconds.

- Searching information about Simplify I found an old thread that
perfectly describe my problematic:
http://postgis.refractions.net/pipermail/postgis-users/2003-January/001979.html

It is worth reading to "understand" the problematic root cause. I also
find another interesting link about linear feature simplification:
http://www.geovista.psu.edu/sites/geocomp99/Gc99/020/gc_020.htm

The biggest problem is now solved.

There are some issues about the Andreas Neumann post I do not
understand your post at all:

Issue 1:
" * I use relative coordinates where applicable "

Relative coordinates ? what are you doing ?

Issue 2:
"I use Intersection() of the data and the map bounding box"

Intersection is another problem, how you draw a geometry that have
points outside the image ? Do your renderer compute the point of the
line that intersects the bounding box line and re-write the geometry
with new points ? or do you draw a bigger image and properly set a
viewport ?

Issue 3:
"I reduce the number of decimals using Klaus AsSVG patch"

I perform the SVG creation outside the PostGIS, and I work with
pixels, do you work in SVG with decimals ? how do you generate images
? you do not convert coordinates to a cartesian I/J axis based system
using pixels ?

Issue 4:
"* I reduce the number of elements. F.e. one multipolygon in svg is
preferable to several polygons if the other attributes are the same. the
number of elements in the SVG DOM tree is also important for the
performance."

Yes, I agree. I reduced the SVG tree too using a polyline and polygon
instead of little line parts. But exists the "MultiPolugon" shape on
the SVG format ? I think not. Which SVG element are you using ?

Thanks in advance,

Best Regards,
Jordi Ferran Sanchez