PostgreSQL/PostGIS ERROR: Third argument must be a POINT

Question

I have a table mytable structured as follows:

id nv_bez parent_rv lfd_nr hierarch_bez snr geom
1 NV064764 None 1 NV064764_1 1 0102000020EC7A00001B000000A3923A0B545B5141D4484B7DE23B5441061E48D7535B514101996C14E13B54413ED565864E5B5141219552D8E13B54412F0B9C1F475B51418802E4B6E13B5441A2AAF439455B5141E2DA4091E13B54410E8B61D1405B514116CDAA5FE13B54413C51E29A3E5B5141C6000469E13B5441718930543C5B514101D3FDD7E13B544178B00DB3395B5141E29239B0E23B54412E5BC72A385B51412F4FCE9EE33B5441708CC33B365B5141B028FC8BE53B5441A5767682355B51418EBC49DAE63B5441D713E51A355B51412A9EF338E83B5441A420290E335B51418B4C0098EF3B5441D8C9736F3A5B5141C24BD456F13B544128B4AD6F3C5B514111643ECFF13B5441E92A9B95425B51410AC27C43F33B54415F92D0AE4E5B5141BA73BF1EF63B5441559469AC505B5141987B27D4F53B54412B4DF2FC515B5141C40C1CA3F53B5441D7EFA9AE525B5141D0793D2AF53B5441B926AB22575B5141981FB621F23B5441DBA0F13A5A5B51414EB5DB89F13B544124C91A9C5D5B51412451A48BF13B5441B6C5DF41625B51410D91CF8EF13B544198AFB2A9655B514114CAF103F23B5441AB73A735675B514157A7B0EFF03B5441
2 NV064764 NV064764_1 1 NV064764_1_1 1 0102000020EC7A00001000000098AFB2A9655B514114CAF103F23B5441AB0FCAB0665B51414972DB8EF23B5441236065C9675B5141680F3468F33B5441F2AB4102675B514192C4F9AFF43B54417C30E558665B51413557D9D7F53B5441E1CF6F35655B5141519006D5F73B54410E4B715D645B5141B5D45D4EF93B5441BB6DA617625B51419155C146FD3B5441655F56F6605B51413FE52E40FF3B5441EB4AD814605B5141B0161ECA003C5441DF2110DD5F5B5141FEA52714013C5441C617D3945E5B5141BF77D0C7023C5441A068AD145D5B514158F6ADC5043C54419DDA08645A5B5141F045E56F073C54418E1ADCFD585B5141B20CAA52083C54411B89B864585B5141B975D49D083C5441
3 NV064764 NV064764_1_1 1 NV064764_1_1_1 1 0102000020EC7A000004000000EB4AD814605B5141B0161ECA003C5441D43CAB1F6A5B514167BB2680033C5441DC759E2B6E5B51410929ED21033C54416E1A98A66E5B5141A41FD42F033C5441
4 NV064764 None 2 NV064764_2 2 0102000020EC7A00001E000000A3923A0B545B5141D4484B7DE23B5441061E48D7535B514101996C14E13B54413ED565864E5B5141219552D8E13B54412F0B9C1F475B51418802E4B6E13B5441A2AAF439455B5141E2DA4091E13B54410E8B61D1405B514116CDAA5FE13B54413C51E29A3E5B5141C6000469E13B5441718930543C5B514101D3FDD7E13B544178B00DB3395B5141E29239B0E23B54412E5BC72A385B51412F4FCE9EE33B5441708CC33B365B5141B028FC8BE53B5441A5767682355B51418EBC49DAE63B5441D713E51A355B51412A9EF338E83B5441A420290E335B51418B4C0098EF3B5441843446182F5B51411E3400A3EE3B544132137467285B514173500EFBEC3B544137C37706285B5141439ABCE2EC3B544153E7347A225B51415631E7EAFD3B54415F732E12285B5141A6C624DFFE3B5441E1172E732C5B514137EE579EFF3B5441E4E56F322E5B51412155800F003C544119763B9E345B5141D25663AF013C54413166300C3A5B5141607B0D0F033C5441A15BD9EE405B5141453F03CD043C5441E722A0FC455B51419EE25714063C5441DCAAC987475B5141E1FB7A85063C54413B2D88624D5B5141663C9432083C54413BFF72BF4F5B51416529C5DF083C54413D459469545B5141CD1376B8093C5441775FD993555B514195694487093C5441
5 NV064764 None 3 NV064764_3 3 0102000020EC7A00000C000000A3923A0B545B5141D4484B7DE23B5441061E48D7535B514101996C14E13B54412F60B672555B514113F332D9E03B544113C2A50D605B5141029E5D58E03B5441D78644706E5B5141AC77F756E13B54416676C4A3705B5141C0D83610E03B5441D976611D765B5141543AFE7FD73B5441794B2B7C765B5141BFC5BCEBD63B5441C07C4694775B5141259CF815D43B544179C0499E785B514121BBB864D13B5441BBA0E46E7B5B5141D58EA909D23B5441163CA1E57B5B514105C9BCE1CF3B5441
6 NV064764 NV064764_3 1 NV064764_3_1 3 0102000020EC7A00000300000079C0499E785B514121BBB864D13B544174E9E8C9725B514149ED4813D03B5441FE28B0BD705B51410DE7E19CCF3B5441
7 NV064764 NV064764_3 2 NV064764_3_2 3 0102000020EC7A000003000000BBA0E46E7B5B5141D58EA909D23B5441D77D575B7F5B5141E98BB0EFD23B5441DE4213E9805B514132CDEC0FD13B5441
8 NV064764 NV064764_3_2 1 NV064764_3_2_1 3 0102000020EC7A000006000000D77D575B7F5B5141E98BB0EFD23B54417CF03A7A845B51415C630FFDD53B5441AA2676C0785B51410BEDC7E7E63B5441CA69B0FD765B5141BF3DB82AE93B5441097C303C755B514132908FA3EA3B54410CB569596C5B514157945F5EF03B5441
[...]

on which I would like to perform the following query:

with offset_geom as (
select id, nv_bez, parent_rv, lfd_nr, hierarch_bez, snr,
case when snr in (1,3,5) then
 st_offsetcurve(geom, case 
 when snr = 1 then 0.25
 when snr = 3 then 0.75
 when snr = 5 then 1.25
end, 'join=miter')
else
st_reverse(st_offsetcurve(geom, case 
 when snr = 2 then -0.25
 when snr = 4 then -0.75
 when snr = 6 then -1.25
end, 'join=miter'))
end as geom
from mytable)
select id, hierarch_bez, snr,
case when o.parent_rv is not null then
 case
 when st_intersects(o.geom, (select geom from offset_geom where hierarch_bez = o.parent_rv))
 then st_setpoint(o.geom, 0, st_intersection(o.geom, (select geom from offset_geom where hierarch_bez = o.parent_rv)))
 else
 st_addpoint(o.geom, st_closestpoint((select geom from offset_geom where hierarch_bez = o.parent_rv), st_startpoint(o.geom)), 0)
 end
else
 o.geom 
end as geom
from offset_geom as o;

The with part offsets the geometries depending on snr attribute and works as expected. The select part should modify the geometries start points in order to re-connect the offsetted geometries to their parent geometries. The execution of the query results gives the following very short error (ref'd to above), that I fairly do not understand and need advice to interpret it:

ERROR: Third argument must be a POINT

I assume this can only happen in PostGIS function ST_SetPoint whichs third argument is expected to be a point, but ST_Intersection should return one. Is there a chance to get a more detailed and helpful error message on that?

Answer 1

Agree, that your error message is not quite 'verbose', but you are on the right track with ST_Intersection. The misunderstanding here is, that it does not necessarily return a POINT, but a geometry collection, depending on the two input geometries. If you can make sure, that your two Linestrings intersect in only one point, you can use

ST_GeometryN(
 ST_CollectionExtract(
 ST_Intersection(LineStringA, LineStringB), 1), 1)

... where the ,1 in ST_CollectionExtract resticts the results to MultiPoints and the ,1 in ST_GeometryN gives you the 1st Point in a Multipoint.