Trying to create a route with two parallel lines with postgis

So I just started working with SQL and PostGIS. For this exercise I need to create a table with a primary key and a geometry field.


Insert into paradezug Values (1,ST_Transform( st_linefromtext('linestring(13.40044325726135 52.51884104088853 , 13.40109986093854 52.51827594967714)',4326),25833));

Insert into paradezug Values (2,ST_Transform( st_linefromtext('LineString(13.40109986093854 52.51827594967714 , 13.39917356833286 52.51769543984515)',4326),25833));

Insert into paradezug Values (3,ST_Transform( st_linefromtext('linestring(13.39917356833286 52.51769543984515 , 13.37837611723877 52.5162977964313)',4326),25833));

This worked well and gave me the route which I can import and view in QGIS.

Now I have to add a line on the left and on the right with a 250m offset.

I've tried:

SELECT ST_AsText(ST_OffsetCurve(geom,
    -250, 'quad_segs=4 join=round')) As notsocurvy
    FROM ST_GeomFromText(
'linestring(13.40044325726135 52.51884104088853 , 13.40109986093854 52.51827594967714)') As geom;

SELECT ST_AsText(ST_OffsetCurve(geom,
    -250, 'quad_segs=4 join=round')) As notsocurvy
    FROM ST_GeomFromText(
'LineString(13.40109986093854 52.51827594967714 , 13.39917356833286 52.51769543984515)') As geom;

SELECT ST_AsText(ST_OffsetCurve(geom,
    -250, 'quad_segs=4 join=round')) As notsocurvy
    FROM ST_GeomFromText(
'linestring(13.39917356833286 52.51769543984515 , 13.37837611723877 52.5162977964313)') As geom;

Which returned something successful. But as I'm new to SQL I don't quite understand how to move on from here.

Any help will be appreciated!

Solution

So ST_OffsetCurve outputs a new geometry derived from an existing one.

You started by computing the curve offset using a column name, and this column comes from a manually created geometry:

SELECT ST_AsText(ST_OffsetCurve(geom,
    -250, 'quad_segs=4 join=round')) As notsocurvy
FROM ST_GeomFromText(
'linestring(13.39917356833286 52.51769543984515 , 13.37837611723877 52.5162977964313)') As geom;

If you want to derive the curve from your table, you can reference it here:

SELECT ST_AsText(ST_OffsetCurve(geom,
    -250, 'quad_segs=4 join=round')) As notsocurvy
FROM paradezug ;

From there, one option is to add the curve offset to the same table. The id should be populated by a sequence (search for serial or identity) so you can simply omit it in the insert statements. Also, you don't need to insert a text but a geometry, so let's remove the st_asText function:

INSERT INTO paradezug (geom)
SELECT ST_OffsetCurve(geom,
        -250, 'quad_segs=4 join=round') As geom
FROM paradezug ;

Now you may want to track which line is the center line and to ensure the offset lines do not exist yet before running this query.