Using ST_DWithin with indexes

[j3r3m1]

I wonder whether ST_DWithin can be used with ST_EXPAND to use the indexes and limit the number of geometries tested. We do that in GeoClimate but I am not sure it is actually efficient: https://github.com/orbisgis/geoclimate/blob/78c95a53deb24e1e61570276927ba03fbc194676/geoindicators/src/main/groovy/org/orbisgis/geoclimate/geoindicators/RsuIndicators.groovy#L199

In PostGis they can create an index directly on an expand, which would probably lead to better performance: CREATE INDEX ON broadcasting_towers using gist (ST_Expand(geom, sending_range));

Is there such possibility in H2 ?

[ebocher]

H2 supports only column. So this kind of command

CREATE SPATIAL INDEX ON broadcasting_towers  (ST_Expand(geom, sending_range));

perhaps in the future ;-)

@katzyn Am I right ?

[katzyn]

Current documentation of CREATE INDEX is up to date: https://h2database.com/html/commands.html#create_index

H2 doesn't support partial indexes and indexes on expressions, but supports indexes on computed columns. Index on computed column can only be used if this computed column is used directly in the filter criteria of the query with a compatible operator on the top level or within an AND.