There is an easier *but less optimized* way to manage such kind of
Pre-filter (spatial index). If in my ST_Intersection function I could
retrieve more information on the two parameters I could internally use
Index first (pre-computed bounding box in RTree) then full intersection
test.
Something like this:
public static Boolean ST_Intersects(Connection connection, Field<Geometry>
> leftField, Field<Geometry> rightField ) {
> Geometry geom1 = leftField.getValue();
> Geometry geom2 = rightField.getValue();
> // Bounding box test through RTree
> if( !leftField.getTable().isEmpty()) {
>
> if(!SpatialRTreeIndex.BBoxIntersects(connection,leftField.getTable(),leftField.getFieldIndex(),
>
> geom2)) {
> return false;
> }
> } else if( !rightField.getTable().isEmpty()) {
>
> if(!SpatialRTreeIndex.BBoxIntersects(connection,rightField.getTable(),rightField.getFieldIndex(),
>
> geom1)) {
> return false;
> }
> }
> // BBox intersects, or there is no precomputed rtree available,
> test full geometry
>
> return geom1.intersects(geom2);
>
> }
>
original
(https://github.com/nicolas-f/H2GIS/blob/h2spatial-osgi-snapshot/h2spatial/src/main/java/org/h2spatial/internal/function/spatial/predicates/ST_Intersects.java)
Then in this sample the function get more than field value, it get also
table name and column index for each parameter.
I repeat, this solution is less effective than pre-filter row index by
using rtree first, but it should run quickly as the intersection test is
done fewer time than original ST_Intersects version.
What do you think about it ?
Le mardi 21 mai 2013 19:22:40 UTC+2, Thomas Mueller a écrit :
>
> Hi,
>
> It's a good question. The optimizer currently only knows a few ways to use
> an index: range conditions (<=, >=, >, <, =, between), in(list),
> in(select), like (which is converted to a range condition), is null, is not
> null, min(x), max(x), count(*).
>
> I wonder if there is a "generic" way to support index usage. It's won't be
> that easy I guess. For the MVStore it would be nice to have a good solution
> (so that the r-tree can be used), but we might end up "hardcoding" it
> within H2.
>
> Could you tell me what are possible operations that could use a (spatial
> or other) index, and how such an index could be used?
>
> Regards,
> Thomas
>
>
>
> On Tue, May 21, 2013 at 1:27 PM, Nicolas Fortin (OrbisGIS) <
> [email protected] <javascript:>> wrote:
>
>> I know function table but this lead to modifying the sql request and it
>> is not in standard.
>>
>> Le mardi 21 mai 2013 11:52:15 UTC+2, Noel Grandin a écrit :
>>>
>>> See the section title "Using a function as a table" here
>>> http://h2database.com/html/**features.html#user_defined_**functions<http://h2database.com/html/features.html#user_defined_functions>
>>>
>>> On 2013-05-21 10:07, Nicolas Fortin (OrbisGIS) wrote:
>>>
>>> Discussion about user defined index on OTHER type.
>>>
>>> In spatial SQL the following request on an indexed geometry column would
>>> use spatial index:
>>>
>>> select * from spatialTable where ST_Intersects(the_geom,
>>>> ST_PolyFromText('POLYGON ((67 13, 67 18, 59 18, 59 13, 67 13))',1));
>>>>
>>>
>>> Usage of ST_Intersects filter the_geom field by using BoundingBox stored
>>> index of the spatialTable, then the method is applied on filtered rows.
>>>
>>> Can I do this by creating my own TableEngine ? There is an easier way
>>> without modifying the sql request ? Maybe a way to let special kind of
>>> function to alter the execution plan ?
>>>
>>> Thanks for your support.
>>>
>>> --
>>> You received this message because you are subscribed to the Google
>>> Groups "H2 Database" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to h2-database...@**googlegroups.com.
>>> To post to this group, send email to [email protected].
>>>
>>> Visit this group at
>>> http://groups.google.com/**group/h2-database?hl=en<http://groups.google.com/group/h2-database?hl=en>
>>> .
>>> For more options, visit
>>> https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out>
>>> .
>>>
>>>
>>>
>>>
>>> --
>> You received this message because you are subscribed to the Google Groups
>> "H2 Database" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected] <javascript:>.
>> To post to this group, send email to [email protected]<javascript:>
>> .
>> Visit this group at http://groups.google.com/group/h2-database?hl=en.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>>
>>
>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
