[postgis-users] PostGIS - server side optimization

[Ralph Mason]

I would have more than one table.  Stepped tables with progressively 
simplified and fewer polygons in each one.

So the biggest table has everything in it.  The next one simplifies the 
geometries and removes ones small than a given threshold, the next one 
... etc.  You may only need a single simplified table, or more depending 
on your data and performance requirements.

So if you are zoomed in you select from the full detail one, a little 
further out you switch to the first simplified one, further out still 
you switch to the next one. 

Ralph

>Hi all,
>
>I have a performance issue with PostGIS and I have ideas about how to
>solve it, probably you could help me on taking the right decision.
>
>I'm working with 30.000 row amount table, that on production should go
>up to 650.000. Nonetheless a SQL query should not affect more than
>100.000 rows.
>
>I require to gather geomtries information on a real-time sceario for
>rendering an SVG image. The problem is that I select these geometries
>using a bounding box filter. Here it is a sample of that SQL query:
>
>SELECT primary_key, AsText(geom) AS geometry
>FROM "city-1_table"
>WHERE ( geom @ GeometryFromText('BOX3D(414758.439692
>4596794.509,421338.876 4604365.807929513)', -1) )
>
>In other words all the geometries included on the ROI (region of
>interest, specified by the Box3D).
>
>The problem is that the result set is too big, and some geometries are
>too small to draw, the renderer component removes or ignore a vast
>amount of information. For you information, working with a 7 kilometer
>bounding box, due to the big region of interest and the little draw
>space: 800x600 pixel image, only the 3.5% information is used.
>
>At the end of this email there are some benchmarks, my resume:
>1.- a data export is about 16 seconds
>2.- and a Java database driver connection is about 50 seconds.
>
>My target is an average response time margin is 1 to 5 seconds.
>
>And now my question / solution proposal:
>- It is possible to embed my renderer business logic into the server
>side for performing that low-level geometry information
>discrimination? If can be done then the internet application server
>must only recieve that 3.5% of information that requires for rendering
>an image ?
>
>I sincerely don't know if I can move all that information to a single
>Store Procedure (a server side function). Must I get the "gcc" and
>write "C" code using the PostGIS core kernel. I sincerely do not
>consider seriously to do that.
>
>Thanks in advance for you help.
>
>Best Regards,
>Jordi Ferran Sanchez
>
>PD: do not hesitate to contact me for any question.
>
>------
>Testing System:
>WindowsOS XP Professional
>Pentium 4 3.2GHz HT
>512MB DDR333
>
>Action: Export Data to DBF
>Table: terrassa-1_table
>Fields: the_geometry
>
>Summary:
>Rows, 30518
>Time, 16 seconds
>Speed, 1870 row/sec
>
>Java-JDBC: (not serious timings on a 1.7GHz Pentium 4, running SUSE Linux)
>Rows, 30515
>Time, 50 seconds (aprox.)
>Speed, 610 row/sec
>------
>
>Using that bechmarks gathering the 3.5% of the rows may time,
>theoretically, a 1.7 seconds which is more than acceptable.
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>  
>