[postgis-users] PostGIS - server side optimization

[Jordi Ferran]

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.