0

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. enter image description here

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.

4

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).

  • Thanks for your answer. This essence is to calculate LINESTRING's geohash by vertices , but because the limitation of max_segment_length 's , doing so will lead to missing. You can see what I added to the question. – giser_yugang Feb 28 at 6:03

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.