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/… – Devdatta Tengshe Sep 18 '13 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 Sep 22 '13 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 Sep 23 '13 at 2:01

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;
| improve this answer | |

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 */ )
| improve this answer | |
  • 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 Sep 24 '13 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. – Devdatta Tengshe Sep 25 '13 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.

| improve this answer | |
  • I actually want to make little polygon squares that go to the midpoints, so that I can click them and get tooltips. – Abe Sep 24 '13 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 Sep 24 '13 at 22:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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