Improve Performance of ST_DWithin - H2GIS

Question

I have a table seg_point, that I create like this:

CREATE TABLE seg_point (id INT AUTO_INCREMENT PRIMARY KEY, segmentid TEXT, geom GEOMETRY);

And I'm trying to improve the performance of this query that looks for the nearest match within 5 km (5000 m):

SELECT SEGMENTID, ST_AsText(geom) AS geom, ST_Distance(ST_Transform(seg_point.geom, 3857), ST_Transform(ST_GeomFromText('POINT(' || ? || ' ' || ? || ')', 4326), 3857)) AS DISTANCE 
FROM seg_point 
WHERE ST_DWithin(ST_Transform(seg_point.geom, 3857), ST_Transform(ST_GeomFromText('POINT(' || ? || ' ' || ? || ')', 4326), 3857), 1000) 
ORDER BY DISTANCE 
limit 1

I saw this post and tried creating a spatial index:

Improve performance on a st_dwithin query (in PostGIS)

I can create the index, but it doesn't seem that h2gis supports CLUSTER.

Any other suggestions for improving the query?

Answer 1

I ended up switching over to using a PostGIS database instead of H2GIS and am using the Geography type with clustered indices and am getting much faster (and I think accurate) results.