PostGIS Plugin: Expose BBOX substitution to enable override of auto-appended BBOX #415

Closed
artemp opened this Issue Oct 11, 2011 · 8 comments

Comments

Projects
None yet
1 participant
Owner

artemp commented Oct 11, 2011

This is needed for complex sub-queries to achieve the optimal query plan - where tacking the '&& BBOX' at the end would not correctly limit the geometries.

Owner

artemp commented Oct 11, 2011

[springmeyer] HUGE thanks to migurski for figuring this one out.

The goal would be to have a query like this:

{{{
#!xml

(Select ST_Collect(way) as way
from planet_osm_line
where way && !bbox!) as line
}}}

be turned into:

{{{
#!sql
SELECT AsBinary("way",'NDR') AS geom from (select st_collect(way) as way from planet_osm_line where way && SetSRID('BOX3D(-7376504.99433164 2059892.878778907,-7374670.50566836 2061115.871221093)'::box3d,900913)) as line
}}}

rather than being limited to using:

{{{
#!xml
(Select ST_Collect(way) as way from planet_osm_line) as line
}}}

which by default gets turned into:

{{{
#!sql
SELECT AsBinary("way",'NDR') AS geom from (Select ST_Collect(way) as way from planet_osm_line) as line WHERE "way" && SetSRID('BOX3D(-7376504.99433164 2059892.878778907,-7374670.50566836 2061115.871221093)'::box3d,900913)) as line
}}}

and in this case tries to collect all geometries in the table before performing the bbox query.

Owner

artemp commented Oct 11, 2011

[springmeyer] Usage could be either:

{{{
[..snip..] where geometry && !bbox!

...or...

[..snip..] where ST_Intersects(geometry,!bbox!)
}}}

The token is case insensitive, so either !BBOX! or !bbox! would work.

Owner

artemp commented Oct 11, 2011

[springmeyer] note: quick testing reveals that the ST_Intersects(geometry,!BBOX!) syntax is faster.

Owner

artemp commented Oct 11, 2011

[springmeyer] added in r1290

Owner

artemp commented Oct 11, 2011

[springmeyer] This substitution needs to happen in other parts of the code otherwise queries will fail... reopening.

Owner

artemp commented Oct 11, 2011

[springmeyer] r1292 improving the previous commit by handling the possibility of a bbox token in other parts of postgis.cpp

Owner

artemp commented Oct 11, 2011

[springmeyer] re-opening to give thought to also passing the current map !scale! as a token.

Owner

artemp commented Oct 11, 2011

[springmeyer] ah nevermind about the !scale!, will look into it for 0.7.0 in #465

@artemp artemp closed this Oct 11, 2011

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment