How to convert latitude/longitude into central position of grid?

Question

I want to take a lat/long and convert it into the center point of a grid with squares of size X/miles^2. Is there an easy way to accomplish this?

For example divide earth into a grid of 1 square mile rectangles, any lat/lot found within a specific square would get converted to the center point of that square. The purpose is to store approximate location information without storing exact location information.

Why over-complicate things? GPS resolution is based on decimal places: If you cut off the lat/long at 2 decimals, now your coordinate is the center of "every location within about a kilometer" at the equator, and "within about half a kilometer" at 65N/S. If you need approximate location, just remove decimal precision. — Mike 'Pomax' Kamermans, Mar 11 '23 at 22:35
Please note, that cutting of decimals will lead to a systematic error, as it always rounds down. — MughtyWinky, Apr 20 '23 at 17:31

score 1 · Answer 1 · answered Mar 22 '23 at 12:19

Try ST_SnapToGrid. Please note, that it's possible to set the origin. The following example uses data in WGS84. To complete your task, you need to transform the geometries to a crs, which uses miles.

DROP TABLE IF EXISTS gps_fakes;
SELECT setseed(0.16031984);
SELECT ST_SetSRID(
        ST_MakePoint(
            (-180 + random() * 360),
            (-90 + random() * 180)
        ),
        4326
    ) as geom_original
INTO
    gps_fakes
FROM
    generate_series (0,1500);

ALTER TABLE gps_fakes ADD COLUMN geom_approx geometry(POINT, 4326);

UPDATE
    gps_fakes
SET 
    geom_approx = ST_SnapToGrid(geom_original, 0, 0, 10, 10);

Which will produce

How to convert latitude/longitude into central position of grid?

1 Answers1