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?
geography
, the a geodeticST_DWithin
. Deprojecting is less expensive than reprojecting, especially when datum transformation is required. – Vince Jun 11 '19 at 2:06