Getting geohash of LINESTRING in PostGIS?

Question

I have some LINESTRING in PostGIS.

 LINESTRING(104.011 30.637,104.014 30.638,104.022 30.640,104.026 30.645)
 LINESTRING(104.022 30.666,104.031 30.659)
 LINESTRING(104.044 30.655,104.056 30.655)

I want to get all the geohash that intersects LINESTRING on level 6 (or others). So I excepted result like:

[wm3yqc,wm3yr1,wm3yr4]
[wm3yrn,wm3yrj,wm3yrm]
[wm3yrs,wm3yru]

I know ST_GeoHash to get geohash in PostGIS. But as the document says:

For non-points, the starting point of the calculation is the center of the bounding box of the geometry.

Add

There is a bug to compute a geohash for vertices in adding vertices by ST_Segmentize method. As shown in the figure below, if parameter max_segment_length of ST_Segmentize is larger than the length of LINESTRING passing through the grid geohash, it will be omitted.

The length of the length of LINESTRING passing through the grid geohash may be close to zero. We can't avoid this result by adjusting the parameters.

Does anyone have a good solution? It is also acceptable to use geopandas or shapely without complex loop.

Answer 1

You can extract the vertices from your linestrings and let PostGIS to compute a geohash for each.

select ST_GeoHash((g.gdump).geom) as geohash
from
(select ST_DumpPoints(ST_GeomFromText('LINESTRING(104.011 30.637,104.014 30.638,104.022 30.640,104.026 30.645)')) as gdump) as g;

Result:
"wm3yqc63rywzdjsm3vp9"
"wm3yqckxmc5ubvje34ve"
"wm3yr1fdbmcp27grpn80"
"wm3yr4v2j6p9s14kvgtk"

You can control the number of vertices in geometries at least with ST_Segmentize (add points) and ST_SnapToGrid (combine close points).