1

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?

2

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.