mapserver postgis connection

[Frank Warmerdam]

On 11/2/05, Frans Knibbe <frans at geodan.nl> wrote:
>     CLASS
>       EXPRESSION ([POPULATION] > 1000000)
>       NAME "large cities"
>       STYLE
>         COLOR 255 0 0
>         SYMBOL "circle"
>         SIZE 10
>       END
>     END
>     CLASS
>      EXPRESSION ([POPULATION] < 1000000 AND [POPULATION] >100000 )
>      NAME "small cities"
>      MAXSCALE 1000000
>      STYLE
>         COLOR 255 0 0
>         SYMBOL "circle"
>         SIZE 5
>       END
>     END
>
> Now if the data are stored in shapefile format, at small scales all the
> points in the extent would be read, after which only a small part would
> be rendered, if I understand correctly. This could be inefficient and
> lead to poor performance. If the data were stored in PostGIS, MapServer
> could be so smart to make a query for just the points that need to be
> drawn. I don't know if this actually happens...

Frans,

You have the right idea, though to get the advantage of PostgreSQL
attribute queries I think you would need to make two separate layers
instead of just using classes.  I believe the class expressions are always
evaluated inside mapserver so it would process all the features for either
shapefile or postgres input.

To do it maximally efficiently for postres you would use two layers
and express the restriction in the layer level filter (or right in the
expression in the layer DATA statement).

> In this example, the classification could be prepared in the database.
> We could add an extra column "size_class" in which the population
> classes are stored. for example, size_class=1 means a city with more
> than a million inhabitants. Now the example becomes:
>
>     CLASSITEM "size_class"
>      CLASS
>       EXPRESSION /1/
>       NAME "large cities"
>       STYLE
>         COLOR 255 0 0
>         SYMBOL "circle"
>         SIZE 10
>       END
>     END
>     CLASS
>      EXPRESSION /2/
>      NAME "small cities"
>      MAXSCALE 1000000
>      STYLE
>         COLOR 255 0 0
>         SYMBOL "circle"
>         SIZE 5
>       END
>     END
>
> In this case, the attribute index could be used if the data were stored
> in shapefile format. Will MapServer use this index to read only the
> records that will be drawn?

I'm afraid not.  First, MapServers "builtin" shapefile support does
not actually use the limited attribute indexes, only OGR does.  And
OGR only utilizes it in cases where the filter gets passed down.
You could get a similar effect by using two layers in your mapfile,
and connectiontype OGR with the filtering criteria expressed in
the FILTER keyword for the layer (ie. FILTER "WHERE size_class=1").

In this case, the OGR shapefile driver could take advantage of the
attribute index to read only the required features.

Of course, if optimal speed is a primary concern, the more obvious
solution would be to have two shapefiles, one for small cities and
one for large cities.

The Postgres advantage is more obvious if you don't want to do
so much data pre-preparation or if you want more dynamic
segmentation of features.

Best regards,
--
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | Geospatial Programmer for Rent