I have a postgis table of points, that are sitting on road centerlines (I've linear referenced them). And the second table with road centerlines themselves. The point table also has a field roadid and a field "offst", that represents the offset in meters (negative values = offest to the left, positive = offset to the right side of the road). The question is how can I calculate a point geometry, that is offsetted perpendicular to road direction, according to the value of the "offst" field?
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.
There is probably easier way but... in PostGIS 2.0.1 ->
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 :
"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"