mapserver postgis connection

[Frans Knibbe]

Thank you for your insights, Frank and Stephen. It is good to know that 
both methods of accessing shapefiles use the same spatial index. Still, 
some aspects of this matter are still not clear to me, I'm afraid. Maybe 
it would be best to use an example. Let's say we have a data set with 
points representing population centers. At small scales, only the large 
cities should be drawn and when the user zooms in also the smaller 
places should be drawn. A common enough example, I think. If we just use 
two classes then the relevant bit in the map file would look something 
like this:

    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...

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?

And the general question of course is: will shapefile really be faster 
in both cases?

Regards,

Frans


Stephen Woodbridge wrote:

> Frans Knibbe wrote:
>
>> Hello Frank,
>>
>> Thank you for the comments. I will certainly use 
>> CLOSE_CONNECTION=DEFER if I use PostGIS. But regarding the difference 
>> in performance between PostGIS and shapefile, doesn't the indexing 
>> PostGIS/PostgreSQL can do count for anything? For large datasets, you 
>> need a spatial index. I understand that the native shapefile driver 
>> (which is the recommended way to access shapefiles according to the 
>> MapServer Reference Guide for Vector Data Access 
>> (http://www.maptools.org/dl/docs/mapserv/MsVectorDataGuide.pdf)) does 
>> not support ESRI's spatial index for shapefiles. OGR, on the other hand, 
>
>
> This is true BUT mapserver has its own spatial index. See the 
> utilities shptree and every *.shp should have an index *.qix
>
> find /pat/to/data -name \*.shp -exec shptree {} \;
>
> will add the spatial indexes in Linux.
>
>> can use a spatial index (see 
>> http://www.gdal.org/ogr/drv_shapefile.html). Better still, it also 
>> supports an attribute index. An attribute index could be used by 
>> MapServer if a layer has a classification based on attribute values. 
>> I really don't know how MapServer reads and sorts its data, but I can 
>> imagine an attribute index will be beneficial for layers with 
>> symbolization based on attribute values. Still, if I understand 
>> correctly, the attribute index that OGR uses can not be used if a 
>> classification based on value ranges is wanted.
>
>
> Other than the spatial index mentioned above mapserver does not use 
> any other indexes like for attribute joins. Once you know what 
> entities spatially overlap the map it cycles through all of them and 
> renders each in turn so there is no need or benefit for other indexes.
>
> PostGIS is needed if you want to do thematic maps because a join is 
> required is usually require to generate the theme CLASS column.
>
>> So is it really true that shapefiles always outperform PostGIS data, 
>> even if we are talking about big datasets with classifications?
>
>
> Yes, unless you require a join for rendering or need to filter out a 
> lot of data from the file/table.
>
> -Steve W.
>
>> Regards,
>>
>> Frans
>>
>>
>>
>> Frank Warmerdam wrote:
>>
>>> On 10/28/05, Frans Knibbe <frans at geodan.nl> wrote:
>>>  
>>>
>>>> An interesting thread.
>>>> Shouldn't the connection step go faster if you have FastCGI enabled in
>>>> the MapServer?
>>>>   
>>>
>>>
>>>
>>> Frans,
>>>
>>> My understanding is that PostGIS connection times are very fast
>>> so FastCGI is unlikely to give much benefit.  However, if you have
>>> many PostGIS layers in the map, I would encourage at least using
>>> the CLOSE_CONNECTION=DEFER processing option to ensure
>>> the same connection is used for all the layers.  This does not require
>>> FastCGI.   FastCGI basically just allows preserving the connection
>>> from cgi request to cgi request.
>>>
>>>  
>>>
>>>> Also, I wonder if PostGIS would improve relative to shapefile if you
>>>> have many concurrent users requesting the same data...
>>>>   
>>>
>>>
>>>
>>> I am dubious about that.  The operating system will already preserve
>>> the shapefile data in RAM from request to request.  I would think
>>> that PostGIS would pay off in speed in cases that shapefiles don't
>>> optimize well, for instance if you want to filter out most data based
>>> on an attribute column, that could be done very fast in Postgres.
>>>
>>> Other than that, as folks mention Postgres' big benefit is flexability
>>> of data integration and updatability.
>>>
>>> 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
>>>
>>>
>>>  
>>>
>>
>
>