search

orbisgis / h2gis

A spatial extension of the H2 database.
http://www.h2gis.org
GNU Lesser General Public License v3.0
208 stars 63 forks source link

Using ST_DWithin with indexes #1251

Open j3r3m1 opened 2 years ago

j3r3m1 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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.