Hi,
It sounds like a great idea and I can also implement it to the date
comparisons, but unfortunately the new query is much slower.
Please see the attached log.
With kind regards,
Menashè
On 07/14/2015 12:50 PM, Christian Grün wrote:
Should geo:within of http://docs.basex.org/wiki/Geo_Module help?
The functions of the Geo Module don't use any index structures, so I
am afraid they won't speed up the query.
One more idea: you could convert all latitudes and longitudes to
strings with a fixed number of digits....
_____________________________________
(:~ Allowed range. :)
declare variable $RANGE := 999999;
(:~ Maximum latitude. :)
declare variable $LAT-MIN := -90;
(:~ Maximum longitude. :)
declare variable $LAT-MAX := 90;
(:~
: Converts a double value to a normalized string value
: with a fixed size of digits.
: @param $num number to be converted
: @param $min minimum allowed value
: @param $max maximum allowed value
: @return resulting value
:)
declare function local:normalize(
$num as xs:double,
$min as xs:integer,
$max as xs:integer
) {
let $norm := $RANGE * ($num - $min) div ($max - $min)
return format-number($norm, '000000')
};
(: Run code for various latitude values :)
for $latitude in (-90, -89.9999, -13.345, 0, 89.99999)
return local:normalize($latitude, $LAT-MIN, $LAT-MAX)
_____________________________________
Next, you could to do string comparisons on these values:
for $doc in db:open("CDI")
let $lat := $doc//latitude
let $lon := $doc//longitude
where $lat >= "883387" and $lat <= "893463"
and $lon >= "173467" and $lon <= "178745"
return db:node-pre($doc)
It should be fast enough if the maximum value is not much bigger than
the minimum value.
Compiling:
- inlining $norm_3
- simplifying flwor expression
- pre-evaluating -90
- pre-evaluating -180
- pre-evaluating db:open("CDI")
- inlining local:normalize#3
- removing redundant $num_13 as xs:double cast.
- removing redundant $min_14 as xs:integer cast.
- removing redundant $max_15 as xs:integer cast.
- inlining $num_13
- inlining $min_14
- pre-evaluating (46.733 - -90)
- pre-evaluating (999999 * 136.733)
- inlining $max_15
- pre-evaluating (90 - -90)
- pre-evaluating (1.36732863267E8 div 180)
- pre-evaluating format-number(759627.0181499999, "000000")
- simplifying flwor expression
- pre-evaluating -67.81
- inlining local:normalize#3
- removing redundant $num_16 as xs:double cast.
- removing redundant $min_17 as xs:integer cast.
- removing redundant $max_18 as xs:integer cast.
- inlining $num_16
- inlining $min_17
- pre-evaluating (-67.81 - -90)
- pre-evaluating (999999 * 22.189999999999998)
- inlining $max_18
- pre-evaluating (90 - -90)
- pre-evaluating (2.218997781E7 div 180)
- pre-evaluating format-number(123277.65449999999, "000000")
- simplifying flwor expression
- inlining local:normalize#3
- removing redundant $num_19 as xs:double cast.
- removing redundant $min_20 as xs:integer cast.
- removing redundant $max_21 as xs:integer cast.
- inlining $num_19
- inlining $min_20
- pre-evaluating (72.7006667 - -180)
- pre-evaluating (999999 * 252.7006667)
- inlining $max_21
- pre-evaluating (180 - -180)
- pre-evaluating (2.527004139993333E8 div 360)
- pre-evaluating format-number(701945.5944425925, "000000")
- simplifying flwor expression
- pre-evaluating -79.9666667
- inlining local:normalize#3
- removing redundant $num_22 as xs:double cast.
- removing redundant $min_23 as xs:integer cast.
- removing redundant $max_24 as xs:integer cast.
- inlining $num_22
- inlining $min_23
- pre-evaluating (-79.9666667 - -180)
- pre-evaluating (999999 * 100.0333333)
- inlining $max_24
- pre-evaluating (180 - -180)
- pre-evaluating (1.000332332666667E8 div 360)
- pre-evaluating format-number(277870.0924074075, "000000")
- simplifying flwor expression
- rewriting descendant-or-self step(s)
- rewriting descendant-or-self step(s)
- inlining local:normalize#3
- removing redundant $min_26 as xs:integer cast.
- removing redundant $max_27 as xs:integer cast.
- inlining $num_25 as xs:double
- inlining $min_26
- inlining $max_27
- pre-evaluating (180 - -180)
- simplifying flwor expression
- rewriting descendant-or-self step(s)
- inlining local:normalize#3
- removing redundant $min_29 as xs:integer cast.
- removing redundant $max_30 as xs:integer cast.
- inlining $num_28 as xs:double
- inlining $min_29
- inlining $max_30
- pre-evaluating (90 - -90)
- simplifying flwor expression
- rewriting ($beginPosition_10 >= "1889-01-01")
- rewriting ($beginPosition_10 <= "2015-07-10")
- atomic evaluation of ($lat_12 <= $north_5)
- atomic evaluation of ($lat_12 >= $south_6)
- atomic evaluation of ($lon_11 <= $east_7)
- atomic evaluation of ($lon_11 >= $west_8)
- rewriting ("1889-01-01" <= $beginPosition_10 and $beginPosition_10 <= "2015-07-10" and ($lat_12 <= $north_5) and ($lat_12 >= $south_6) and ($lon_11 <= $east_7) and ($lon_11 >= $west_8))
- inlining $db_4
- inlining $north_5
- rewriting ($lat_12 <= "759627")
- inlining $south_6
- rewriting ($lat_12 >= "123278")
- inlining $east_7
- rewriting ($lon_11 <= "701946")
- inlining $west_8
- rewriting ($lon_11 >= "277870")
- inlining $beginPosition_10
- rewriting where clause(s)
Query:
declare variable $RANGE := 999999; declare variable $LAT-MIN := -90; declare variable $LAT-MAX := 90; declare variable $LON-MIN := -180; declare variable $LON-MAX := 180; declare function local:normalize( $num as xs:double, $min as xs:integer, $max as xs:integer ) { let $norm := $RANGE * ($num - $min) div ($max - $min) return format-number($norm, '000000') }; let $db := db:open("CDI") let $north := local:normalize(46.733, $LAT-MIN, $LAT-MAX) let $south := local:normalize(-67.81, $LAT-MIN, $LAT-MAX) let $east := local:normalize(72.7006667, $LON-MIN, $LON-MAX) let $west := local:normalize(-79.9666667, $LON-MIN, $LON-MAX) for $x in $db let $beginPosition := string($x//startTime) let $lon := local:normalize($x//longitudine, $LON-MIN, $LON-MAX) let $lat := local:normalize($x//latitudine, $LAT-MIN, $LAT-MAX) where $beginPosition>="1889-01-01" and $beginPosition<="2015-07-10" and $lat <= $north and $lat >= $south and $lon <= $east and $lon >= $west return db:node-pre($x)
Optimized Query:
for $x_9 in ((db:open-pre("CDI",0), ...))["1889-01-01" <= string(descendant::*:startTime) <= "2015-07-10"] let $lon_11 := format-number(((999999 * (((: xs:double, true :) $x_9/descendant::*:longitudine) - -180)) div 360), "000000") where ($lon_11 <= "701946" and "277870" <= $lon_11) let $lat_12 := format-number(((999999 * (((: xs:double, true :) $x_9/descendant::*:latitudine) - -90)) div 180), "000000") where ($lat_12 <= "759627" and "123278" <= $lat_12) return db:node-pre($x_9)
Result:
- Hit(s): 374739 Items
- Updated: 0 Items
- Printed: 2048 KB
- Read Locking: local [CDI]
- Write Locking: none
Timing:
- Parsing: 3.36 ms
- Compiling: 14.2 ms
- Evaluating: 2.54 ms
- Printing: 7185.75 ms
- Total Time: 7205.85 ms
Query plan:
<QueryPlan compiled="true">
<GFLWOR>
<For>
<Var name="$x" id="9"/>
<IterFilter>
<DBNodeSeq size="374739">
<DBNode name="CDI" pre="0"/>
<DBNode name="CDI" pre="62"/>
<DBNode name="CDI" pre="151"/>
<DBNode name="CDI" pre="204"/>
<DBNode name="CDI" pre="274"/>
</DBNodeSeq>
<CmpSR min="1889-01-01" max="2015-07-10">
<FnString name="string([item])">
<CachedPath>
<IterStep axis="descendant" test="*:startTime"/>
</CachedPath>
</FnString>
</CmpSR>
</IterFilter>
</For>
<Let>
<Var name="$lon" id="11"/>
<FnFormatNumber name="format-number(number,picture[,format])">
<Arith op="div">
<Arith op="*">
<Int value="999999" type="xs:integer"/>
<Arith op="-">
<TypeCheck type="xs:double" function="true">
<IterPath>
<VarRef>
<Var name="$x" id="9"/>
</VarRef>
<IterStep axis="descendant" test="*:longitudine"/>
</IterPath>
</TypeCheck>
<Int value="-180" type="xs:integer"/>
</Arith>
</Arith>
<Int value="360" type="xs:integer"/>
</Arith>
<Str value="000000" type="xs:string"/>
</FnFormatNumber>
</Let>
<Where>
<And>
<CmpSR min="" max="701946">
<VarRef>
<Var name="$lon" id="11"/>
</VarRef>
</CmpSR>
<CmpSR min="277870" max="">
<VarRef>
<Var name="$lon" id="11"/>
</VarRef>
</CmpSR>
</And>
</Where>
<Let>
<Var name="$lat" id="12"/>
<FnFormatNumber name="format-number(number,picture[,format])">
<Arith op="div">
<Arith op="*">
<Int value="999999" type="xs:integer"/>
<Arith op="-">
<TypeCheck type="xs:double" function="true">
<IterPath>
<VarRef>
<Var name="$x" id="9"/>
</VarRef>
<IterStep axis="descendant" test="*:latitudine"/>
</IterPath>
</TypeCheck>
<Int value="-90" type="xs:integer"/>
</Arith>
</Arith>
<Int value="180" type="xs:integer"/>
</Arith>
<Str value="000000" type="xs:string"/>
</FnFormatNumber>
</Let>
<Where>
<And>
<CmpSR min="" max="759627">
<VarRef>
<Var name="$lat" id="12"/>
</VarRef>
</CmpSR>
<CmpSR min="123278" max="">
<VarRef>
<Var name="$lat" id="12"/>
</VarRef>
</CmpSR>
</And>
</Where>
<DbNodePre name="node-pre(nodes)">
<VarRef>
<Var name="$x" id="9"/>
</VarRef>
</DbNodePre>
</GFLWOR>
</QueryPlan>
