@user181677 for the question and @Paul Ramsey for the idea. I wanted generate a series of nodes. I didn't need a precise direction or offset really. I just wanted to space out 80 mile markers for OSM. I am still in the process of actually placing the nodes and have not committed the OSM change set yet. All this query does is generate 80 mile markers as the crow flies. However, that ST_Project was a great idea. Here's the pgsql/PostGIS query that I used.
/*
* Finally, We generate most of the OSM XML file format
* for a node.
*/
select '<node id=''' || mile_markers2 *-1 ||
''' action=''modify'' visible=''true'' lat=''' ||
mile_marker_y || ''' lon=''' ||
mile_marker_x || '''>' || chr(10) ||
'<tag k=''distance'' v=''' || mile_markers2 ||
' miles'' />' || chr(10) ||
'<tag k=''highway'' v=''milestone'' />' || chr(10) ||
'<tag k=''ref'' v=''' || mile_markers2 || ''' />' || chr(10) ||
'<tag k=''source'' v=''Mapillary'' />' || chr(10) ||
'<tag k=''traffic_sign'' v=''milestone'' />' || chr(10) ||
'</node>' as mc85_mile_marker_nodes
from (
select mile_markers,
/*
* Here we just need to adjust the mile makers so that
* they start at one.
*/
mile_markers+1 as mile_markers2,
meter_constant, meter_offset, general_direction,
/*
* A sample st_project fuction call with both the x,y
* coordinates. We can see the point in the result set.
* The -112... 32... numbers are from the first mile
* marker.
*/
ST_asText(st_project(
'POINT( -112.73352547993 32.9328758707 )'::geography,
meter_offset,
general_direction )) as mile_marker_geom,
/*
* We really need the X for our final presentation.
*/
ST_X(ST_asText(st_project(
'POINT( -112.73352547993 32.9328758707 )'::geography,
meter_offset,
general_direction ))) as mile_marker_x,
/*
* We really need the y for our final presentation.
*/
ST_Y(ST_asText(st_project(
'POINT( -112.73352547993 32.9328758707 )'::geography,
meter_offset,
general_direction ))) as mile_marker_y
from (
select /*
* We need a zero mile marker even though one does
* not exist. The zero marker is used to establish
* our first offset from where the real mile maker
* exits. Hence the (0, 80) in the generate series
* call.
*/
mile_markers,
/*
* One mile in meters for the function call.
*/
1609.344 as meter_constant,
/*
* Generate an offset for each function call to
* create a mile marker.
*/
mile_markers * 1609.344 as meter_offset,
/*
* This is the general direction that we are heading
* to Lukeville/Mexico.
*/
radians(190.00) as general_direction
from generate_series(0,80) as mile_markers
) as mc85_mile_markers
order by mile_markers
) as final_mc85_mile_markers;