Create a point x meters from origin using st_translate in postgis

Question

How do I create a point 1000 meters away from a reference point?

The ST_Translate only supports geometry type.

I also tried using ST_GeomFromText but that returns a unit and not in meters.

Here is my sample query:

SELECT ST_AsText ( ST_Translate ( ST_GeogFromText('SRID=4326;POINT(-70.01 43.27)'), 1000, 0 ) );

Is there a way that I can convert the output of ST_Translate into meters?

Answer 1

You want ST_Project, which takes in a point, direction and distance http://postgis.net/docs/ST_Project.html

Answer 2

To make a meter translation you need a geometry that has a spatial reference system in meter unit. The srid code 31997 for example is metric, so what need to do is feed a metric geometry to st_translate and get back into the desired output spatial reference system.

SELECT st_AsText ( st_transform( st_translate( st_transform( st_GeomFromText('SRID=4326;POINT(-70.01 43.27)'), 31997 ), 1000, 0 ), 4326 ) );

Answer 3

Use Geometry contructor

ST_GeomFromText()

http://postgis.net/docs/ST_GeomFromText.html

Answer 4

@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;