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;

Answer 2

For a PostGIS version of R my guess isthat you should use the ST_AsRaster to generate raster from vector data, tiled or untiled. The case below generate a serie of squared raster. For tiled raster the upperleft values should also be provided. After you could merge all those rasters into a single composition.

SELECT 
  ST_AsRaster(
    st_setSRID( st_makePoint( t.long, t.lat), 4326 )
  , 256
  , 256
  , '8BUI'
  , t.value /* assume value column is numeric val */
  , 0 /* value for places where there is not a match */
  --, double precision upperleftx=NULL
  --, double precision upperlefty=NULL
  --, double precision skewx=0, double precision skewy=0
  --, boolean touched=false
  )
FROM "table" AS t;

To make the inverse operation, generate geometry from raster data for each raster point you could issue something like this easily:

SELECT st_astext( geom ), val FROM st_pixelAsPolygons( /* your raster */ )

Answer 3

I think what you're after is interpolation, right?

If you have a set of lat, lon, observations and you want them to be a raster, this post suggests that you should do the interpolation outside of postgis: http://postgis.17.x6.nabble.com/PostGIS-Raster-and-IDW-td4662998.html

This agrees: PostGIS interpolation / triangulation options

So, what I'd do is load the points into PostGIS (see @Devdatta's post above), open the layer in QGIS and then use the interpolation plugin. If you just want to rasterise the vector, use the rasterise vector function in QGIS.