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

Sat Dec 17 11:10:15 PST 2022

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] On Behalf Of Denis Rykov
Sent: Friday, December 16, 2022 1:30 PM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto: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 <mailto: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 <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 <mailto: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 <mailto: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/aedfa15c/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/20221217/aedfa15c/attachment.png>