Odd behavior with PostGIS

[willt]

Hi all

Another question from me today, sorry.

I've trying to plot a subset of points from a Postgis Multipoint
shapefile, however nothing plots.
I've got "estimate_extent = False" set, yet mapnik tries to estimate
the extent (see log file below) which seems to cause an error.
I have no clue why mapnik appends limit 0 to my query, either.

Nothing untoward shows in the debug output.
When I plot the whole table, it works fine.

My setup:
Ubuntu Hardy
Postgis 1.3.3
Postgresql 8.3.7

Any ideas anyone?

Cheers, Will


=======================
Python:
=======================
            sql = """(SELECT site.geom as geom FROM site WHERE
            geom is not null and
            EXISTS (SELECT * FROM vector_site_sample_period
            WHERE vector_site_sample_period.site_id = site.site_id
            and vector_site_sample_period.anopheline_id = %s)) as pnt
            """ % self.anopheline_id
   |~
            lyr.datasource =
mapnik.PostGIS(estimate_extent=False,user='somename',password='somepass',dbname='vdb',table=sql)

=======================
Logfile:
=======================

 BST LOG:  statement: select f_geometry_column,srid,type from
geometry_columns where f_table_name='vector_site_sample_period'
 BST LOG:  statement: select * from (SELECT site.geom as geom FROM site WHERE
                    geom is not null and
                    EXISTS (SELECT * FROM vector_site_sample_period
                    WHERE vector_site_sample_period.site_id = site.site_id
                    and vector_site_sample_period.anopheline_id = 2)) as pnt
                     limit 0
 BST LOG:  statement: select xmin(ext),ymin(ext),xmax(ext),ymax(ext)
from (select extent() as ext from vector_site_sample_period) as tmp
 BST ERROR:  function extent() does not exist at character 61
 BST HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.
 BST STATEMENT:  select xmin(ext),ymin(ext),xmax(ext),ymax(ext) from
(select extent() as ext from vector_site_sample_period) as tmp
_______________________________________________
Mapnik-users mailing list
Mapnik-users@...
https://lists.berlios.de/mailman/listinfo/mapnik-users

[Jon Burgess-2]

On Thu, 2009-06-11 at 16:15 +0100, William Temperley wrote:
> Hi all
>
> Another question from me today, sorry.
>
> I've trying to plot a subset of points from a Postgis Multipoint
> shapefile, however nothing plots.
> I've got "estimate_extent = False" set, yet mapnik tries to estimate
> the extent (see log file below) which seems to cause an error.

Not exactly. Turing off the estimated extent will force to query the DB
for the real extent, unless you also specify the extent, e.g.

<Parameter name="extent">-20037508,-19929239,20037508,19929239</Parameter>

> I have no clue why mapnik appends limit 0 to my query, either.

It is trying to determine the names and types of the columns returned by
thte query.

> Nothing untoward shows in the debug output.
> When I plot the whole table, it works fine.

The problem is here:

  select extent() as ext from vector_site_sample_period

That should be like this, but the name of the geometry column is
missing:

  select extent(geom) as ext from vector_site_sample_period

Often this means that the user running query can not do a select on the
geometry_columns table. In your case though it looks like maybe Mapnik
is just confused about which table you are querying.

Can you show us which geometry columns are in geometry_columns?

In recent versions of Mapnik you can force the geometry column name by
setting:
        geometry_field = 'geom'

see http://trac.mapnik.org/ticket/139

> My setup:
> Ubuntu Hardy
> Postgis 1.3.3
> Postgresql 8.3.7
>
> Any ideas anyone?
>
> Cheers, Will
>
>
> =======================
> Python:
> =======================
>             sql = """(SELECT site.geom as geom FROM site WHERE
>             geom is not null and
>             EXISTS (SELECT * FROM vector_site_sample_period
>             WHERE vector_site_sample_period.site_id = site.site_id
>             and vector_site_sample_period.anopheline_id = %s)) as pnt
>             """ % self.anopheline_id
>    |~
>             lyr.datasource =
> mapnik.PostGIS(estimate_extent=False,user='somename',password='somepass',dbname='vdb',table=sql)
>
> =======================
> Logfile:
> =======================
>
>  BST LOG:  statement: select f_geometry_column,srid,type from
> geometry_columns where f_table_name='vector_site_sample_period'
>  BST LOG:  statement: select * from (SELECT site.geom as geom FROM site WHERE
>                     geom is not null and
>                     EXISTS (SELECT * FROM vector_site_sample_period
>                     WHERE vector_site_sample_period.site_id = site.site_id
>                     and vector_site_sample_period.anopheline_id = 2)) as pnt
>                      limit 0
>  BST LOG:  statement: select xmin(ext),ymin(ext),xmax(ext),ymax(ext)
> from (select extent() as ext from vector_site_sample_period) as tmp
>  BST ERROR:  function extent() does not exist at character 61
>  BST HINT:  No function matches the given name and argument types. You
> might need to add explicit type casts.
>  BST STATEMENT:  select xmin(ext),ymin(ext),xmax(ext),ymax(ext) from
> (select extent() as ext from vector_site_sample_period) as tmp
> _______________________________________________
> Mapnik-users mailing list
> Mapnik-users@...
> https://lists.berlios.de/mailman/listinfo/mapnik-users

_______________________________________________
Mapnik-users mailing list
Mapnik-users@...
https://lists.berlios.de/mailman/listinfo/mapnik-users

[willt]

2009/6/11 Jon Burgess <jburgess777@...>:

> On Thu, 2009-06-11 at 16:15 +0100, William Temperley wrote:
>>
>> I've trying to plot a subset of points from a Postgis Multipoint
>> shapefile, however nothing plots.
>> I've got "estimate_extent = False" set, yet mapnik tries to estimate
>> the extent (see log file below) which seems to cause an error.
>
> Not exactly. Turing off the estimated extent will force to query the DB
> for the real extent, unless you also specify the extent, e.g.
>
> <Parameter name="extent">-20037508,-19929239,20037508,19929239</Parameter>
>
>> I have no clue why mapnik appends limit 0 to my query, either.
>
> It is trying to determine the names and types of the columns returned by
> thte query.
>
>> Nothing untoward shows in the debug output.
>> When I plot the whole table, it works fine.
>
> The problem is here:
>
>  select extent() as ext from vector_site_sample_period
>
> That should be like this, but the name of the geometry column is
> missing:
>
>  select extent(geom) as ext from vector_site_sample_period
>
> Often this means that the user running query can not do a select on the
> geometry_columns table. In your case though it looks like maybe Mapnik
> is just confused about which table you are querying.
>
> Can you show us which geometry columns are in geometry_columns?
>
> In recent versions of Mapnik you can force the geometry column name by
> setting:
>        geometry_field = 'geom'
>
> see http://trac.mapnik.org/ticket/139
>

Thanks - it's making some sense now, though for me this seems somewhat
confusing behaviour.
I'd been expecting behaviour similar to shp2pgsql where you can
specify arbitrary queries, which don't get messed with. Mapnik
rewrites the query in somewhat non-transparent ways.

I've ended up hacking postgis.cpp, allowing the table parameter to
pass straight through and be executed unadulterated (i.e. I can pass
arbitrary queries shp2pgsql style, provided I use "AsBinary(the_geom)
as geom" to get the geometry. Works well for me. Can provide a patch
if anyone wants.

Best, Will
_______________________________________________
Mapnik-users mailing list
Mapnik-users@...
https://lists.berlios.de/mailman/listinfo/mapnik-users

[Robert Coup-4]

On Fri, Jun 12, 2009 at 10:25 AM, William Temperley <willtemperley@...> wrote:

Thanks - it's making some sense now, though for me this seems somewhat
confusing behaviour.
I'd been expecting behaviour similar to shp2pgsql where you can
specify arbitrary queries, which don't get messed with. Mapnik
rewrites the query in somewhat non-transparent ways.

Yeah, it's something I've been thinking about cleaning up.

Currently by default it:

 - find the geometry from the geometry_columns table 

 - figures out whether it needs to do transforms based on the map projection & the geometry field projection

 - takes all your filters and figures out which attribute fields it needs

 - constructs a query for those attribute fields and the geometry

 - does a where clause for the map extent against the geometry field

 - runs it

Bad things:

 - filters are all done in mapnik (if you're drawing all the data that's fine, otherwise it gets too much data out)

 - if you would rather reproject in postgis it gets messy

 - if you have multiple geometry fields you need to use my geometry_field= thing

 - using custom sql is ... interesting - the general pattern is table="(SELECT blah FROM ... WHERE ...) AS x", but you need to make sure field names all line up.

Good things:

 - doesn't pull unnecessary attribute fields from the database (ala SELECT * FROM)

 - edit the map xml / script, add a new filter and it just works

 - great for simple use cases 

 

I've ended up hacking postgis.cpp, allowing the table parameter to
pass straight through and be executed unadulterated (i.e. I can pass
arbitrary queries shp2pgsql style, provided I use "AsBinary(the_geom)
as geom" to get the geometry. Works well for me. Can provide a patch
if anyone wants.

I've been wondering whether we do something like:

 table= -> current behaviour

 sql= -> all on your own, you need to make sure the fields/geometries/everything are correct.

Opinions?

Rob :)

--
Koordinates Ltd.
PO Box 1604, Shortland St, Auckland 1140, New Zealand
Phone [phone] Fax [phone]
Web http://koordinates.com/

_______________________________________________
Mapnik-users mailing list
Mapnik-users@...
https://lists.berlios.de/mailman/listinfo/mapnik-users

[willt]

2009/6/12 Robert Coup <robert.coup@...>:

>
>
> On Fri, Jun 12, 2009 at 10:25 AM, William Temperley
> <willtemperley@...> wrote:
>>
>> Thanks - it's making some sense now, though for me this seems somewhat
>> confusing behaviour.
>> I'd been expecting behaviour similar to shp2pgsql where you can
>> specify arbitrary queries, which don't get messed with. Mapnik
>> rewrites the query in somewhat non-transparent ways.
>
> Yeah, it's something I've been thinking about cleaning up.
> Currently by default it:
>  - find the geometry from the geometry_columns table
>  - figures out whether it needs to do transforms based on the map projection
> & the geometry field projection
>  - takes all your filters and figures out which attribute fields it needs
>  - constructs a query for those attribute fields and the geometry
>  - does a where clause for the map extent against the geometry field
>  - runs it
> Bad things:
>  - filters are all done in mapnik (if you're drawing all the data that's
> fine, otherwise it gets too much data out)
>  - if you would rather reproject in postgis it gets messy
>  - if you have multiple geometry fields you need to use my geometry_field=
> thing
>  - using custom sql is ... interesting - the general pattern is
> table="(SELECT blah FROM ... WHERE ...) AS x", but you need to make sure
> field names all line up.
> Good things:
>  - doesn't pull unnecessary attribute fields from the database (ala SELECT *
> FROM)
>  - edit the map xml / script, add a new filter and it just works
>  - great for simple use cases
>
>>
>> I've ended up hacking postgis.cpp, allowing the table parameter to
>> pass straight through and be executed unadulterated (i.e. I can pass
>> arbitrary queries shp2pgsql style, provided I use "AsBinary(the_geom)
>> as geom" to get the geometry. Works well for me. Can provide a patch
>> if anyone wants.
>
> I've been wondering whether we do something like:
>  table= -> current behaviour
>  sql= -> all on your own, you need to make sure the
> fields/geometries/everything are correct.
> Opinions?
> Rob :)
> --

+1 for that - exactly what I'd like to see, though I guess you knew that :-)
The ability to use ad-hoc spatial and non-spatial queries is, after
all, the reason I use PostGIS.

Will
_______________________________________________
Mapnik-users mailing list
Mapnik-users@...
https://lists.berlios.de/mailman/listinfo/mapnik-users

[Lennard-2]

Robert Coup wrote:

> Yeah, it's something I've been thinking about cleaning up.
>
> Currently by default it:
>  - find the geometry from the geometry_columns table
>  - figures out whether it needs to do transforms based on the map
> projection & the geometry field projection
>  - takes all your filters and figures out which attribute fields it needs
>  - constructs a query for those attribute fields and the geometry
>  - does a where clause for the map extent against the geometry field
>  - runs it
>
> Bad things:
>  - filters are all done in mapnik (if you're drawing all the data that's
> fine, otherwise it gets too much data out)

This is a really important factor in the speed of tile rendering. In the
past week, I've worked on the complete osm.xml file, adjusting filters
and sql queries to optimize what's fetched from the db, and how many
mapnik filters are needed.

In the end, I achieved an average speedup by a factor of 1.75 on my
server*. The downside would be that some sql queries are getting rather
long, and not as easy to understand for some people (although no real
trickery is involved). Adding new filters means you also have to edit
the sql.

I'll be commiting the simpler cases to SVN first. We'll have to see
whether some of the more complex or longer queries can be reworked by
doing more preprocessing in osm2pgsql.

* Running mod_tile's speedtest on a .5x.5 degree area boxed around
Antwerp, z0 to z18.

>  - using custom sql is ... interesting - the general pattern is
> table="(SELECT blah FROM ... WHERE ...) AS x", but you need to make sure
> field names all line up.

For my optimisation experiment, I wrote a small perl script to fetch all
the filters from the rules, eliminating duplicates, and all fields used
in the Text/ShieldSymbolizer. I've been thinking about extending it to
do better analysis and even suggest queries for a given layer/style(s)
combination.

>  - edit the map xml / script, add a new filter and it just works
>  - great for simple use cases

For average and simple stylesheets, this would usually suffice.

> I've been wondering whether we do something like:
>
>  table= -> current behaviour

The current behaviour already breaks if you add a filter without adding
it to the select in a subquery, unless you're doing 'select *' there.

>  sql= -> all on your own, you need to make sure the
> fields/geometries/everything are correct.

+1 though, although I hope it would still add the bbox, obviously.


--
Lennard
_______________________________________________
Mapnik-users mailing list
Mapnik-users@...
https://lists.berlios.de/mailman/listinfo/mapnik-users

[Robert Coup]

(cc'ing to mapnik-devel)

On Sat, Jun 13, 2009 at 5:55 AM, Lennard <ldp@...> wrote:

Robert Coup wrote:

I've been wondering whether we do something like:

 table= -> current behaviour

The current behaviour already breaks if you add a filter without adding it to the select in a subquery, unless you're doing 'select *' there.

Very true.

 

 sql= -> all on your own, you need to make sure the fields/geometries/everything are correct.

+1 though, although I hope it would still add the bbox, obviously.

Except the "though" part makes a mess of it :(

Maybe the ability to do a simple substitution in the querystring would suffice: 

"SELECT ... FROM ... WHERE my_geom && %(bbox)s"

%(bbox)s gets replaced with st_geomfromtext('POLYGON(( ... ))', 1234)

Make it substitutable multiple times, and if people are doing clever [stupid] things with projections (like i do) then they can always wrap it in st_setsrid() or st_transform() or whatever they need.

Improvements? What am i missing?

Rob :)

_______________________________________________
Mapnik-users mailing list
Mapnik-users@...
https://lists.berlios.de/mailman/listinfo/mapnik-users