« Return to Thread: Odd behavior with PostGIS

Re: Odd behavior with PostGIS

by Robert Coup-4 :: Rate this Message:

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 +64-9-966 0433 Fax +64-9-969 0045
Web http://koordinates.com/

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

« Return to Thread: Odd behavior with PostGIS