# generate spatial geom from lat lon points for map in PostGIS

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/… 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 ? 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
Sep 23, 2013 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;
``````

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 */ )
``````
• 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, 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. 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
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. Sep 24, 2013 at 22:23