[GIS] generate spatial geom from lat lon points for map in PostGIS

Question

I am interested in a PostGIS implementation of the R-based soulution from a previous question, How can I convert data in the form of lat, lon, value into a raster file using R?

Currently, I am hoping to generate tiles for a map in CartoDB from a table with columns "lat, lon, value".

I have found some hints on the CartoDB google group, which boil down to "insert polygonized raster/values into a valid empty polygon table in CartoDB"

But the examples include a deprecated function (ST_MapAlgebraExpr) and a suite of nested ST_* functions, including st_transform(st_setsrid(st_multi(st_makevalid((.... Being new to PostGIS, this is fairly difficult to understand and apply, and the help of the individual functions requires some basic understanding.

I am new to PostGIS so please pardon that I haven't gotten very far, and the scope of new data types and transformations are daunting. If I can get my toe in here, I promise to show improvement before my next post 🙂

Answer 1

I'm assuming that your input lat-long are based on the WGS84 datum. Making a point geometry under this assumption is as easy as using the ST_MakePoint() function:

The problem with ST_MakePoint() is that the geometry that is created does not not have a spatial reference. We can set the spatial reference by using the ST_SetSRID() function.

So the simplest way of making a point geometry in the correct spatial reference is to chain both these function calls like this:

--Return point marked as WGS 84 long lat
SELECT ST_SetSRID(ST_MakePoint(-71.1043443253471, 42.3150676015829),4326);

I just noticed that you have a table with columns "lat, lon, value". In that case, you can use the following query to get a point and value

SELECT ST_SetSRID(ST_MakePoint(lon, lat),4326) as the_geom, value from mytable;