[postgis-users] How to generate a map grid given a GeoHash?

[Shaozhong SHI]

I find this is very interesting and will look into this further.  When
Geohash is understood properly, it is actually very useful.

I just wonder whether Converting between grid eastings and northings and
ellipsoidal latitude and longitude can be used to replace the use of
geohash.  I.e., use The National Grid reference convention (e.g. HM, HN and
etc.) to substitute GeoHash.

guide-coordinate-systems-great-britain (ordnancesurvey.co.uk)
<https://www.ordnancesurvey.co.uk/documents/resources/guide-coordinate-systems-great-britain.pdf>

It appears to be feasible.

Regards,

David

<https://www.ordnancesurvey.co.uk/documents/resources/guide-coordinate-systems-great-britain.pdf>

On Saturday, 17 December 2022, Regina Obe <lr at pcorp.us> wrote:

> Hadn’t noticed you were using a precision level on that ST_GeoHash. I
> forgot that feature existed.
>
>
>
> But still I don’t think that would work even if you applied the same
> precision to your inputs
>
>
>
> WITH
>
> -- grid hashes
>
> h AS ( SELECT i, j, ST_GeoHash(geom, 4) AS geohash, geom FROM
> ST_SquareGrid(0.5, ST_MakeEnvelope(-78,38,-77, 39, 4326)) )
>
> -- random points representing input data
>
> , p AS (
>
> SELECT geom,  ST_GeoHash(geom, 4) AS geohash
>
> FROM ST_DumpPoints(ST_GeneratePoints(ST_MakeEnvelope(-78,38,-77, 39,
> 4326), 1000, 1)) AS geom
>
>
>
>     )
>
> -- do all fall in a grid, no
>
> SELECT h.geom, count(p.geom), ST_Collect(ST_Collect(p.geom), h.geom)
>
> FROM p LEFT JOIN h ON p.geohash LIKE (h.geohash || '%')
>
> GROUP BY h.geom;
>
>
>
> What we’d like to see is all our points to same precision fall in one of
> our grids, but as you can see, most points do not.
>
>
>
> i   | j  | count
>
> ------+----+-------
>
> -156 | 76 |    53
>
> -155 | 76 |    62
>
> -155 | 77 |    61
>
> -156 | 77 |    54
>
>       |    |   770
>
> (5 rows)
>
>
>
>
>
>
>
>
>
>
> *From:* Regina Obe [mailto:lr at pcorp.us]
> *Sent:* Saturday, December 17, 2022 12:58 PM
> *To:* 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org>
> *Subject:* RE: [postgis-users] How to generate a map grid given a GeoHash?
>
>
>
> Denis,
>
>
>
> Hadn’t thought about that but no I don’t think so.
>
> If I try to convert your example, there are big gaps of area not covered.
>
>
>
>
>
>
>
> I think if you wanted to use hashes to represent a grid, you’d be better
> off with using h3 hashes.
>
> They would be hexagons instead of squares but still griddy.
> https://github.com/zachasme/h3-pg
>
>
>
> I covered this a little bit in my PostGIS Day talk.
>
>
>
> https://youtu.be/Hx17Ia7wn6Y?t=822
>
>
>
> PostGIS windows latest bundle (3.3.2) which I packaged shortly after this
> talk, has it included now.
>
>
>
> Of course there is also no reason you can’t create a grid with
> ST_SquareGrid and hash the j,j coordinates.
>
> Those wouldn’t change given the same spatial ref sys and same grid size.
>
>
>
> Thanks,
>
> Regina
>
>
>
>
>
> *From:* postgis-users [mailto:postgis-users-bounces at lists.osgeo.org
> <postgis-users-bounces at lists.osgeo.org>] *On Behalf Of *Denis Rykov
> *Sent:* Friday, December 16, 2022 1:30 PM
> *To:* PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> *Subject:* Re: [postgis-users] How to generate a map grid given a GeoHash?
>
>
>
> Thank you Regina for your detailed answer! Let me jump-in.
>
>
> > For example this grid many of the tiles have the same geohash
>
>
>
> That is correct, but can't it be solved just by specifying a proper
> geohash level, like:
>
> postgres=# select i, j, ST_GeoHash(geom, 4) from ST_SquareGrid(0.5,
> ST_MakeEnvelope(-78,38,-77, 39, 4326));
>   i   | j  | st_geohash
> ------+----+------------
>  -156 | 76 | dqb9
>  -156 | 77 | dqbs
>  -156 | 78 | dqbx
>  -155 | 76 | dqc1
>  -155 | 77 | dqch
>  -155 | 78 | dqcp
>  -154 | 76 | dqc3
>  -154 | 77 | dqck
>  -154 | 78 | dqcr
> (9 rows)
>
>
>
> On Fri, Dec 16, 2022 at 5:58 PM Regina Obe <lr at pcorp.us> wrote:
>
> Not sure what you are looking for here, but I suspect the answer is no.
> You wouldn’t get a grid. More like a set of nested squares. Geohash is not
> of much use except possibly for very very small grids. Even then it’s not
> that great.
>
>
>
> Maybe you can read this to get some ideas
>
>
>
> https://en.wikipedia.org/wiki/Geohash
>
>
>
>
>
> For example this grid many of the tiles have the same geohash:
>
>
>
> SELECT i, j, geom , ST_GeoHash(geom) AS geohash
>
> FROM ST_SquareGrid(0.5, ST_MakeEnvelope(-78,38,-77, 39, 4326))
>
> ORDER BY geohash;
>
>
>
>
>
>
>
> You could compute a bounding box from a GeoHash with
> https://postgis.net/docs/ST_GeomFromGeoHash.html
>
>
>
> So if I take the above and compute a bounding box based on distinct
> GeoHash
>
>
>
> SELECT DISTINCT ST_GeoHash(geom) AS geohash,
> ST_GeomFromGeoHash(ST_GeoHash(geom)) AS geom
>
> FROM ST_SquareGrid(0.5, ST_MakeEnvelope(-78,38,-77, 39, 4326))
>
> ORDER BY geohash;
>
>
>
> I would get a result that looks like this
>
>
>
>
>
>
>
>
>
> If you pick a smaller tile size such as:
>
> SELECT DISTINCT ST_GeoHash(geom) AS geohash,
> ST_SetSRID(ST_GeomFromGeoHash(ST_GeoHash(geom)),4326) AS geom
>
> FROM ST_SquareGrid(0.005, ST_MakeEnvelope(-78,38,-77, 39, 4326))
>
> ORDER BY geohash;
>
>
>
> The story is better, but still not something you can count on for
> gridding.  So you were right the first time you asked about there being
> more to it than bounding box, the shorter than bounding box hash doesn’t
> necessarily mean the bounding box you generated from is bigger than another
> bounding box located elsewhere that has a longer hash.  Also depends on
> position on the earth.
>
>
>
>
>
>
>
> *From:* postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] *On
> Behalf Of *Shaozhong SHI
> *Sent:* Friday, December 16, 2022 8:39 AM
> *To:* PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> *Subject:* [postgis-users] How to generate a map grid given a GeoHash?
>
>
>
> Can a map grid be worked out or generated given a Geohash?
>
>
>
> Regards,
>
>
>
> David
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20221218/7e73cf64/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.png
Type: image/png
Size: 193321 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20221218/7e73cf64/attachment.png>