0

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?

|improve this question|||||
  • Calculating distances based on Web Mercator is a major flaw in your plan. Even if you manage to get fast results, they'll still be unreliable. – Vince Jun 5 '19 at 20:16
  • @Vince Thanks for the response. The goal of the query is to map match a given lat/lon to the closest point. All points lie within North America (the continental US and lower region of Canada). Is there a different projection you'd recommend? I'm not a GIS expert, but based on my research I was wondering if Albers Equal Conic would be better? – dunnj515 Jun 10 '19 at 15:47
  • If this were PostGIS, I'd use transform to 4326 and a cast to geography, the a geodetic ST_DWithin. Deprojecting is less expensive than reprojecting, especially when datum transformation is required. – Vince Jun 11 '19 at 2:06
  • Thanks. That makes sense. I had read somewhere that points should be stored in 4326, so that's how I've been storing them. Unfortunately, I don't think H2GIS has the geography type. I'll have to consider if it's worth the effort to switch to PostGIS for our project. – dunnj515 Jun 11 '19 at 2:39
-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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.