Re: Odd behavior with PostGIS

>
>
> 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 :)
> --