Postgis table with multiple geometry fields

[Max Demars]

Hi,

In the case I have a table with multiple geometry fields, let say a multipoint , a multilinestring and a multipolygon fields. Each feature of the table has only one of this 3 fields used at a time. Is it possible to use a single mapnik PostGis Datasource to display all type of features present in the table?

If I do not specify the geometry field in the datasource, it seems that mapnik will use the first geometry field of the table even if it is empty. Am I right?

Is it possible to tell mapnik to use the fist non-empty geometry field instead?

I hope my question is clear enough.

Thank you for your help,

--Max Demars

[Sandro Santilli]

On Wed, Jan 21, 2015 at 12:36:55PM -0800, Max Demars wrote:
> Hi,
>
> In the case I have a table with multiple geometry fields, let say a
> multipoint , a multilinestring and a multipolygon fields. Each feature of
> the table has only one of this 3 fields used at a time. Is it possible to
> use a single mapnik PostGis Datasource to display all type of features
> present in the table?

A Mapnik datasource has a single field name, so no.

> If I do not specify the geometry field in the datasource, it seems that
> mapnik will use the first geometry field of the table even if it is empty.
> Am I right?

As of version 2.1.0, Mapnik allows you to mix geometry types just fine.
You can use rules to specify which style is applied to which type.

From the 2.1.0 section in the CHANGELOG file:

- Added support for filtering on a features geometry type, either `point`,
`linestring`, 'polygon`, or `collection` using the expression keyword of
`[mapnik::geometry_type]` (#546)

--strk;

() Free GIS & Flash consultant/developer
/\ http://strk.keybit.net/services.html

[Max Demars]

Hi Sandro,

Thank you for your reply.

However, I dont understand the use to filter the geometry type at the Style level if you cannot mix the geometry type at the Layer level? In my comprehension, if only a geometry type can be assigned to a Layer (because only a geometry field can be defined in the datasource), each Styles of this Layer would need to be defined according to that geometry type.

Maybe can you give a simple xml example where filtering geometry type at the Style level would be useful?

--Max Demars

[Sandro Santilli]

On Thu, Jan 22, 2015 at 08:38:09AM -0800, Max Demars wrote:
> Hi Sandro,
>
> Thank you for your reply.
>
> However, I dont understand the use to filter the geometry type at the Style
> level if you cannot mix the geometry type at the Layer level?

Who told you that you can't mix geometry types at the Layer level ?

--strk;

[Jukka Rahkonen]

Hi,

Wouldn't it be most clear and most useful for people who will read the
mail archives in the future to write a snippet about how to configure
such layer for Mapnik? For me it seems that you two are talking about
two different solution on the database side:

- table with three distinct geometry columns for points, lines, and
polygons
- table with only one geometry column of general geometry type, holding
a mixture of points, lines, and polygons

-Jukka Rahkonen-

[Sandro Santilli]

Here's an example to turn a PostGIS table with multiple columns into
a single datasource with a single column and Filters to render them
differently:

<Map>
<Style name="multicol">
<Rule>
<Filter>[mapnik::geometry_type]=point</Filter>
<PointSymbolizer/>
</Rule>
<Rule>
<Filter>[mapnik::geometry_type]=linestring</Filter>
<LineSymbolizer/>
</Rule>
<Rule>
<Filter>[mapnik::geometry_type]=polygon</Filter>
<PolygonSymbolizer/>
</Rule>
</Style>
<Layer name="multicol">
<StyleName>multicol</StyleName>
<Datasource>
<Parameter name="extent"><![CDATA[-20,-20,20,20]]></Parameter>
<Parameter name="type"><![CDATA[postgis]]></Parameter>
<Parameter name="geometry_field"><![CDATA[g]]></Parameter>
<Parameter name="table"><![CDATA[(SELECT coalesce(a,b,c) g from multigeom) x]]></Parameter>
</Datasource>
</Layer>
</Map>

NOTE: the query could be written differently to give planner a chance
to use spatial indexes defined on it.

[Max Demars]

Thank you a lot Sandro, now I get it!

--
You received this message because you are subscribed to a topic in the Google Groups "mapnik" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mapnik/vglnygyPR10/unsubscribe.
To unsubscribe from this group and all its topics, send an email to mapnik+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Stack Overflow: http://stackoverflow.com/users/1914034/burton449

GIS Overflow: http://gis.stackexchange.com/users/14426/burton449

LastFm: http://www.lastfm.fr/user/burton449

[Max Demars]

Jukka, the example Sandro has provided show how you cand bind three different geometry field into one with postgres coallesce() function. That's a good demonstration of the utility of geometry type filtering at the rule level, and that answer perfectly my Initial question.

--Max Demars

[Max Demars]

Sandro, I am not a postgis expert. Do you mean spatial indexes of a,b,c  cannot be used in the view g ? Why is that? What would be the solution to be sure that the planner use them?

Thank you!
 

[Max Demars]

Hi Sandro,
I am using Mapnik 2.2.0 and with the query below I got the error g column does not exists. Is is because there is an error in my query or because this version does not support such operation?

<?xml version="1.0" encoding="utf-8"?>
<Map srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0.0 +k=1.0 +units=m +nadgrids=@null +no_defs +over">
    <Style name="mun_flat_Style6">
        <Rule>
            <Filter>(([shapefile_id]=28) and ([mapnik::geometry_type]=3))</Filter>
            <PolygonSymbolizer fill="rgb(0,255,0)"/>
            <LineSymbolizer stroke="rgb(255,255,255)"/>
        </Rule>
    </Style>
    <Layer name="tiled_layer" srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0.0 +k=1.0 +units=m +nadgrids=@null +no_defs +over" cache-features="true">
        <StyleName>mun_flat_Style6</StyleName>
        <Datasource>
            <Parameter name="geometry_field">g</Parameter>
            <Parameter name="geometry_table">layers_feature</Parameter>
            <Parameter name="simplify_geometries">1</Parameter>
            <Parameter name="table">(select shapefile_id, coalesce(geom_multipoint, geom_multilinestring, geom_multipolygon) g from layers_feature where shapefile_id in (28,29)) as geom</Parameter>
            <Parameter name="type">postgis</Parameter>
        </Datasource>
    </Layer>
</Map>

[Sandro Santilli]

On Mon, Jan 26, 2015 at 08:37:41AM -0800, Max Demars wrote:
> Hi Sandro,

> I am using Mapnik 2.2.0 and with the query below I got the error g column
> does not exists. Is is because there is an error in my query or because
> this version does not support such operation?

I think there's still a bug in Mapnik that fails to extract an extent
from a subquery. Try adding an "extent" parameter. If it works please
check whether such bug was filed already or file it yourself otherwise.
Thanks!

As for the indexes, the problem is that Mapnik will append:
WHERE g && <the_map_extent>
to the query, but as "g" is a derivate column it is unlikely to
have an index defined on.

You can define an index on it:

CREATE INDEX on layer_features using gist (
coalesce(geom_multipoint, geom_multilinestring, geom_multipolygon)
):

Or, you can use the !bbox! token in your query so that Mapnik will know
you embedded the filter yourself in the query:

<Parameter name="table">
(SELECT shapefile_id,
coalesce(geom_multipoint, geom_multilinestring, geom_multipolygon) g
FROM layers_feature
WHERE shapefile_id in (28,29)
AND (
geom_multipoint && !bbox!
OR geom_multilinestring && !bbox!
OR geom_multipolygon && !bbox!
)
) as geom
</Parameter>

[Max Demars]

I did open an issue on github: https://github.com/mapnik/mapnik/issues/2660

but it seems Dane did not took it seriously. Is it possible that the bug is present only in my mapnik version ie: v2.2.0 for windows ?