I'm going to preface this with it's my first time using PostGIS.
I'm starting out with a 5 km x 5 km grid cell in which I know the center point lon/lat (SRID=4326). I'm trying to create a polygon for each of these grid cells in PostGIS using this command:
create table lonlat_poly as
select ST_MakeEnvelope(lon-2.5 km, lat-2.5 km, lon+2.5 km, lat+2.5 km)
as newlonlat from lonlat;
where lonlat contains the two columns lon and lat.
The potential pitfall is that the simple subtraction and addition of 2.5 km is not going to be correct due to projection issues (right?). I'm sure there's a more precise way of doing this, but I haven't thought or stumbled upon anything yet. Thanks in advance for any hints you can provide!
Best Answer
You have two options: make a perfect 5 km grid, projected inperfectly to lat/long, or make a perfect lat/long grid that approximates a 5 km grid. See How to create a regular polygon grid in PostGIS? to help make a grid.
Perfect 5 km grid
For example, using this function, here is a 4 x 6 grid with 5 km x 5 km cells somewhere in Vancouver using NAD83 UTM Zone 10N (EPSG:26910), then transformed back into WGS 84 lat/long (EPSG:4326):
You can approximate your
x0
andy0
origin reference from your projected grid points (subtracting 2500 m from each x and y).Perfect lat/long grid
Similar to above, but you must approximate 5 km in degrees latitude and longitude. (I just used one point, but you might want to approximate this distance at the centroid of your points).
Both results are pretty close to each other due to the small scale, but will look more warped over larger regions.