How do I add an offset to a lat/lon to get a new point using PostGIS

Question

Using PostGIS I have a lat/lon point and want to add an east/west and north/south offset in metres to get a new point. Is that possible?

Answer 1

You can use ST_Translate:

The ST_Translate function takes any geometry (linestring, multiline etc) returns a new geometry that is the original geometry moved by a vector defined by X,Y,Z. Note the units of measurement are always in the units of the spatial reference system of the geometry argument. There are two forms of it ST_Translate. ST_Translate(geometry, X, Y, Z) and ST_Translate(geometry, X,Y).

Source: http://www.bostongis.com/postgis_translate.snippet

You'll have to transform lat/lon to meters first. You can use ST_Transform to achieve this:

ST_Transform — Returns a new geometry with its coordinates transformed to the SRID referenced by the integer parameter.

Source: http://postgis.refractions.net/documentation/manual-svn/ST_Transform.html