# Circular Linestring: Make a new start and endpoint along the line (with normalized distance) via PostGIS

I want swap/change the order of the coordinates inside a circular string which should be easy as it is a circle.

-- circular linestring --
`CIRCULARSTRING(5 2,-3 1.999999, -2 1, -4 2, 5 2)`

to something like
`CIRCULARSTRING(-3 1.999999, -2 1, -4 2, 5 2, -3 1.999999)`

The ideal solution would also contain a get me the normalize distance on some point of the line like I want the point at 0.6 to be my new start and end point.

Are there any options to do that in PostGIS in a SQL Statement?

• well, you could simply rebuild those cirlces. that will guarantee the start and end point will be at pi/2 rad, to then be able to rotate the circle with full control. what is that normalized distance all about? Aug 1, 2018 at 14:41
• I am trying to do that. The normailized distance is a position on the line (measured from the start point of the line) where it is described with 0 as start point and 1 as endpoint. postgis.net/docs/ST_LineSubstring.html I use this to split the line. Aug 2, 2018 at 8:05
• `ST_LineInterpolatePoint` returns a point geometry at a given fraction of linelength. do you have centroids of those circles? it might be easier to reconstruct the circle with the boundary of a buffer and rotate that. Aug 2, 2018 at 11:17
• ST_LineInterpolationPoint, ST_LineSubstring as any other function from the Linear Referencing system of PostGIS will only work with LINESTRINGs (not CIRCULARSTRINGs) Aug 2, 2018 at 11:57
• @CarlosMSF true, I do assume that one will convert between types with `ST_LineToCurve`/`ST_CurveToLine`! Aug 2, 2018 at 12:43

To reverse the order of the vertices within the CIRCULARSTRING I would suggest to use ST_Reverse, but it actually doesn't work with CIRCULARSTRING: it returns no error, but returns the original geometry unaltered.

Therefore, I suggest this workaround:

1. Convert the geometry to text - ST_AsText
2. Replace the CIRCULARSTRING for LINESTRING in the WKT of the geometry
3. Create a LINESTRING from the text - ST_GeomFromText
4. Reverse the LINESTRING - ST_Reverse
5. Convert the resulting geometry to text - ST_AsText
6. Replace the LINESTRING for CIRCULARSTRING in the WKT of the geometry
7. Create a CIRCULARSTRING from the text - ST_GeomFromText

Here's the sample code:

``````WITH t AS
(
SELECT ST_GeomFromText('CIRCULARSTRING(5 2,-3 1.999999, -2 1, -4 2, 5 2)') AS geom
)
SELECT
ST_AsText(
ST_GeomFromText(
REPLACE(ST_AsText(
ST_Reverse(
ST_GeomFromText(REPLACE(ST_AsText(geom), 'CIRCULARSTRING', 'LINESTRING'))
)
), 'LINESTRING', 'CIRCULARSTRING')
)
)
FROM t
``````

Result from the query above: "CIRCULARSTRING(5 2,-4 2,-2 1,-3 1.999999,5 2)" • Awesome input, but reverse is not my question at all. The question is how i can make a new start and endpoint from a given CIRCULARSTRING. The given CIRCULARSTRING has already a start and end point, like in my example (CIRCULARSTRING(5 2,-3 1.999999, -2 1, -4 2, 5 2)) the start and endpoint would be 5 2(first index position) and 5 2 (last index position). As it is circular i thought there is maybe an easy solution to define a new start and end point at some normalized (0-1) distance from the first index position. Aug 3, 2018 at 12:16
• In your question, the first line says that one of the objectives was to "change the order of the coordinates inside a circular string", which I interpreted as reversing the geometry. As for the extracting part of the geometry, as referred in previous comments, there are no native functions that would work with CIRCULARSTRING. You would have to convert it into linestring (check ST_CurveToLine) to then be able to use ST_LineSubstring. If you want to work only with the original vertices and put then in a different order, try to convert the coordinates to an array Aug 3, 2018 at 14:19
• Thanks for pointing that out, i will and have to use the coordinates as it seems that there is no way around, i just wanted to avoid extra steps when there could be a fency postgis function for that Aug 3, 2018 at 15:48