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 :)

  • This question might be of help: gis.stackexchange.com/questions/58605/… Commented Sep 18, 2013 at 15:22
  • Did you want to generate raster data with PostGIS2 from a serie of points? Or plot points on a raster image ?
    – cavila
    Commented Sep 22, 2013 at 14:49
  • @cavila I want to make tiny space filling 'squares'from the points, like pixels with tooltips (it seems easy to add tooltips to a vector map but not a raster map.
    – Abe
    Commented Sep 23, 2013 at 2:01

3 Answers 3


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;

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.

    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 */ )
  • perhaps I was confusing the issue by linking to the other page. I want to convert the raster points to polygon squares, 1 square per point.
    – Abe
    Commented Sep 24, 2013 at 16:41
  • @Abe: You would have got better answers, if you had followed up with your question, and commented on the answers this question has received. If you yourself fail to pay attention to your own question, you can hardly expect to get good answers. Commented Sep 25, 2013 at 11:50

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.

  • I actually want to make little polygon squares that go to the midpoints, so that I can click them and get tooltips.
    – Abe
    Commented Sep 24, 2013 at 16:40
  • Hmm. You can probably do a SQL query to replace the table or create a view with squares under each point, assuming that the points are regularly gridded.
    – Alex Leith
    Commented Sep 24, 2013 at 22:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.