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

[Shaozhong SHI]

All these look pretty good to me.  Very interesting.

I just wonder whether we can simplify things like the following:

Given a geometry, we can determine which UK National Grid it involves or
rest in or cover.

Mark these results in some sort of index like geohash index in a column.

Why do this ?  - trying to mark each row/geometry with an effective index
so that when we want to filter out relevant records for a particular area,
it is efficient.

Can this be make a powerful function in PostGIS?

Regards,

David

On Sat, 17 Dec 2022 at 18:00, Regina Obe <lr at pcorp.us> wrote:

> 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] *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
>
> _______________________________________________
> 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/20221217/143bfc4f/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 119023 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20221217/143bfc4f/attachment.png>
-------------- 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/20221217/143bfc4f/attachment-0001.png>