Tutorial: Switching from `add_row()` to `insert()`

In 1.7.0 we introduced a new way of doing geometry transformations in flex config files. Instead of declaring a geometry transformation in the add_row() function (or using the default one) you have to explicitly say how to transform the geometry and then use that like any normal value in the insert() function. This is a tiny bit more work, but it make is much more obvious what’s happening. And it gives you more options, allowing you to do transformations which weren’t possible before.

This document described how to change flex config files which are using the old add_row() method to the new way of doing things with insert().

The add_row() function is deprecated now and will be removed in a future version of osm2pgsql.

For Point Layers

For Point layers it is easy to switch. Lets say you have a table that should get all names and locations of restaurants. Change the add_row to insert and the { create = 'point' } to object:as_point(). Because { create = 'point' } is the default, you might not have that line at all. But you still need the new one.

local restaurants = osm2pgsql.define_node_table('restaurants', {
    { column = 'name', type = 'text' },
    { column = 'geom', type = 'point' }
})

function osm2pgsql.process_node(object)
    if object.tags.amenity == 'restaurant' then
       restaurants:add_row({
       restaurants:insert({
            name = object.tags.name,
            geom = { create = 'point' }
            geom = object:as_point()
        })
    end
end

For (Multi)LineString Layers

For LineString geometries from ways, you have to use the: as_linestring() function. Because { create = 'linestring' } is the default for LineString geometries, you might not have that line at all. But you still need the new one.

local railroads = osm2pgsql.define_way_table('railroads', {
    { column = 'geom', type = 'linestring' }
})

function osm2pgsql.process_way(object)
    if object.tags.railway == 'rail' then
        railroads:add_row({ geom = { create = 'line' } })
        railroads:insert({ geom = object:as_linestring() })
    end
end

If you were using split_at to split LineStrings into shorter ones, you’d magically get multiple rows added to your database for long LineStrings.

With the insert() function this is no longer the case. You explicitly have to call segmentize() now and then iterate over the results. Note that geometries are in 4326 (lat/lon) coordinates, so you have to convert them to 3857 (WebMercator) coordinates first, before calling segmentize() to achieve the same result as before.

local railroads = osm2pgsql.define_way_table('railroads', {
    { column = 'geom', type = 'linestring' }
})

function osm2pgsql.process_way(object)
    if object.tags.railway == 'rail' then
        railroads:add_row({
            geom = { create = 'line', split_at = 100 }
        })
        local mgeom = object:as_linestring():transform(3857):segmentize(100)
        for sgeom in mgeom:geometries() do
            railroads:insert({ geom = sgeom })
        end
    end
end

If you have a MultiLineString geometry you can feed it the split geometry directly:

local railroads = osm2pgsql.define_way_table('railroads', {
    { column = 'geom', type = 'multilinestring' }
})

function osm2pgsql.process_way(object)
    if object.tags.railway == 'rail' then
        railroads:add_row({ geom = { create = 'line', split_at = 100 } })
        railroads:insert({ geom = object:as_linestring():transform(3857):segmentize(100) })
    end
end

For Polygon Layers

If the (closed) way should be interpreted as a Polygon, the { create = 'area' } has to be replaced by a call to as_polygon(). Because { create = 'area' } is the default for Polygon geometries, you might not have that line at all. But you still need the new one.

local forests = osm2pgsql.define_way_table('forests', {
    { column = 'geom', type = 'polygon' }
})

function osm2pgsql.process_way(object)
    if object.is_closed and object.tags.natural == 'wood' then
        forests:add_row({ geom = { create = 'area' } })
        forests:insert({ geom = object:as_polygon() })
    end
end

If you are creating (Multi)Polygons from relations, you had to set { create = 'area' } explicitly. This is now replaced by a call to as_multipolygon().

local forests = osm2pgsql.define_area_table('forests', {
    { column = 'geom', type = 'multipolygon' }
})

function osm2pgsql.process_relation(object)
    if object.tags.type == 'multipolygon' and object.tags.natural == 'wood' then
         forests:add_row({ geom = { create = 'area' } })
         forests:insert({ geom = object:as_multipolygon() })
    end
end

If you were using split_at = 'multi', do the split explicitly now by iterating over the polygons in the multipolygon:

local forests = osm2pgsql.define_area_table('forests', {
    { column = 'geom', type = 'polygon' }
})

function osm2pgsql.process_relation(object)
    if object.tags.type == 'multipolygon' and object.tags.natural == 'wood' then
        forests:add_row({
            geom = { create = 'area', split_at = 'multi' }
        })
        local mgeom = object:as_multipolygon()
        for sgeom in mgeom:geometries() do
            forests:insert({ geom = sgeom })
        end
    end
end

The “Magic” Area Data Type

Sometimes you want a column with the area of a polygon. In the old way of doing things you used a special column of type area for this and osm2pgsql would magically fill in the area of the corresponding polygon. For the new way you have to use a normal column type, probably real and explicitly call the area() function on the polygon object to calculate the area.

local forests = osm2pgsql.define_way_table('forests', {
    { column = 'geom', type = 'polygon' },
    { column = 'area', type = 'area' }
    { column = 'area', type = 'real' }
})

function osm2pgsql.process_way(object)
    if object.is_closed and  object.tags.natural == 'wood' then
        forests:add_row({ geom = { create = 'area' } })
        local poly = object:as_polygon():transform(3857)
        forests:insert({ geom = poly, area = poly:area() })
    end
end

Note that we need to transform the geometry into WebMercator (3857) coordinates before calculating the area to get the same effect as before. We store the transformed geometry in a local variable so we don’t have to do the creation and transformation of the polygon geometry twice.

Because the area calculation is now explicit, you can decide how to calculate the area. If you want to have it in lat/lon coordinates instead, remove the transform(). Or you can use the spherical_area() function (available in osm2pgsql 1.9.0 or greater) instead.

local forests = osm2pgsql.define_way_table('forests', {
    { column = 'geom', type = 'polygon' },
    { column = 'area', type = 'real' }
})

function osm2pgsql.process_way(object)
    if object.is_closed and  object.tags.natural == 'wood' then
        local poly = object:as_polygon()
        forests:insert({ geom = poly, area = poly:spherical_area() })
    end
end

If you are splitting a multipolygon into its constituent polygons, you can calculate the area on the whole or on the parts:

local forests = osm2pgsql.define_area_table('forests', {
    { column = 'geom', type = 'polygon' },
    { column = 'marea', type = 'real' },
    { column = 'sarea', type = 'real' }
})

function osm2pgsql.process_relation(object)
    if object.tags.type == 'multipolygon' and object.tags.natural == 'wood' then
        local mgeom = object:as_multipolygon():transform(3857)
        local marea = mgeom:area()
        for sgeom in mgeom:geometries() do
            forests:insert({ geom = sgeom, marea = marea, sarea = sgeom:area() })
        end
    end
end

NULL Geometries

The old code with add_row() did never generate NULL values in the geometry columns, it just suppressed those rows entirely. This has changed with insert() because now a table can have multiple geometry columns, so a situation could arise where some of those columns are NULL and others have a valid geometry. If you want the old behaviour, you have to declare the geometry columns as NOT NULL.

Example:

local forests = osm2pgsql.define_area_table('forests', {
 { column = 'geom', type = 'polygon' }
 { column = 'geom', type = 'polygon', not_null = true }
})

Tutorial: Switching from add_row() to insert()