Offseting point perpendicular to line direction in PostGIS

There is probably easier way but... in PostGIS 2.0.1 ->

https://postgis.net/docs/ST_OffsetCurve.html

take road, offset it needed amount using ST_OffsetCurve and then use ST_Line_Locate_Point to offsetted line and then ST_Line_Interpolate_Point to find correct place

**SELECT ST_Line_Interpolate_Point(ST_Line_Locate_Point(ST_OffsetCurve(roadcenter_geom, pointoffset), point_geometry ) from xx where yyy**

Another way is get vertex where point intersects line and calculate azimuth there and then use trigometry to figure where new point should be

Edit : Also i found this one :

http://trac.osgeo.org/postgis/wiki/UsersWikiExamplesInterpolateWithOffset

"This code creates another st_line_interpolate_point with a 3rd argument for offset of the point to the left or right side of the line segment. Useful for Geocoding."

Which is ready made implementation of "Another way"

In mathematical terms would need to calculate the normal of the road section and unit vector of that normal. The first will find which direction you need to move to get your new point and the latter will help you find out how far to go in that direction (i.e. the translation).

I'm not completely clear on your schema, so difficult to give an sql example, but here's the theory: Points p1 and p2 are the start and end points of your road section, point p3 is "centerline" on that road section and d is your offset distance. The normal to your road section, where dx = p2(x) - p1(x) and dy = p2(y) - p1(y), is the vector (-dy,dx),(dy,-dx). To make this a unit vector you should normalise it by dividing each component by it's length, where length = sqrt((dx * dx) + (dy * dy)). So the unit vector would be (-dy,dx)/length,(dy,-dx)/length. You can then add that unit vector, times by your chosen offset distance, d. So the offset point (to the right of your road) would be offset_point(x) = p3(x) - ( d * ((-dy,dx)/length) ) offset_point(y) = p3(y) - ( d * ((dy,-dx)/length) )

Exactly how this is best achieved in a postgis sql statement depends on how the geometries are stored.