[postgis-users] RE : st_offsetcurve
Pedro Costa
pedrocostaarma at sapo.pt
Thu May 17 02:08:07 PDT 2012
Ups....My error. I can see the result now but I found an error in a
line, parallel continues beyond the feature(print1 and print2)...
In print3 i found another error, the isolated lines not have parallel...
does anyone know how to solve?
Thanks
Em 17-05-2012 09:42, Pedro Costa escreveu:
> Thanks Hugues...
>
> In this time the select return the street number, my_single_geom and
> my_union_geom.
> The my_union_geom are the two parallels right?
>
> I tried to see the result in qgis, adding a new column (the_geom)
> equal to the column my_union_geom, and adding a new entry inthe table
> geometry_columns. But I can not see the result, qgis does not
> recognize as a geometry column.
>
> What would have to do to see the result?
>
>
> Thanks
>
>
> Em 15-05-2012 06:56, Francois Hugues escreveu:
>> Ok, the error comes from the select : try using select a.n_street, my_union_geom, ST_Parallel(the_geom,-3.0,0,0) my_single_geom
>>
>> I forgot to add the table reference "a."
>>
>> I think I made another mistake using the over method. It would rather be something like that :
>>
>> select n_street, st_union(ST_Parallel(the_geom,3.0,0,0)) over(partition by n_street) my_union_geom,
>> ST_Parallel(the_geom,-3.0,0,0)) my_single_geom
>> from streets
>>
>> Firts time I read too fast.... this time I wrote too fast !
>>
>> Hugues.
>>
>>
>>
>> -------- Message d'origine--------
>> De:postgis-users-bounces at postgis.refractions.net de la part de Pedro Costa
>> Date: lun. 14/05/2012 17:36
>> À: PostGIS Users Discussion
>> Objet : Re: [postgis-users] st_offsetcurve
>>
>> Thank you Francois but still error.
>>
>> Using this:
>>
>> With my_union as (
>> select n_street, st_union (ST_Parallel(the_geom,3.0,0,0)) my_union_geom
>> from streets
>> group by n_street)
>>
>> select n_street, my_union_geom, ST_Parallel(the_geom,-3.0,0,0) my_single_geom
>> from my_union a
>> join streets b on a.n_street = b.n_street
>>
>>
>>
>> give me this error:
>>
>> column reference "n_street" is ambiguous
>>
>> I put the table name but give me another error:
>>
>> invalid reference to FROM-clause entry for table "my_union"
>>
>>
>>
>>
>> Em 11-05-2012 11:01, Francois Hugues escreveu:
>>> Hi,
>>>
>>> This error is normal. You can not use a group by clause with an aggregate function and keep all of single records from your original database. Which one will you choose to keep for each unique n-street value ?
>>>
>>> To do that you can use two different tricks (but your table will have as many lines as you have unique couple n_street/the_geom):
>>>
>>> Using "with" with a join
>>> With my_union as (
>>> select n_street, st_union (ST_Parallel(the_geom,3.0,0,0)) my_union_geom
>>> from streets
>>> group by n_street)
>>>
>>> select n_street, my_union_geom, ST_Parallel(the_geom,-3.0,0,0) my_single_geom
>>> from my_union a
>>> join streets b on a.n_street = b.n_street
>>>
>>> or using an over function (not totally sure of this particular request but the window function can certainly help you in this case)
>>>
>>> select n_street, st_union (ST_Parallel(the_geom,3.0,0,0)) ,
>>> ST_Parallel(the_geom,-3.0,0,0))over(partition by n_street)
>>> from streets
>>> group by n_street
>>>
>>> Hugues.
>>>
>>> --
>>>
>>> -----Message d'origine-----
>>> De :postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] De la part de Pedro Costa
>>> Envoyé : vendredi 11 mai 2012 10:30
>>> À : PostGIS Users Discussion
>>> Objet : Re: [postgis-users] st_offsetcurve
>>>
>>> Ups, sorry...
>>>
>>> The whole sql:
>>>
>>> create table test4 as
>>> (select n_street, st_union (ST_Parallel(the_geom,3.0,0,0)) ,
>>> (ST_Parallel(the_geom,-3.0,0,0))
>>> from streets
>>> group by n_street)
>>>
>>>
>>>
>>> Em 11-05-2012 00:03, Simon Greener escreveu:
>>>> Pedro,
>>>>
>>>>> greate. The funcion works.
>>>>>
>>>>> I'm doing something like that:
>>>>>
>>>>> ST_Parallel(the_geom,3.0,0,0)
>>>>>
>>>>> but give me error:
>>>>>
>>>>> column "streetss.the_geom" must appear in the GROUP BY clause or be used
>>>>> in an aggregate function
>>>>>
>>>>> anybody know the problem?
>>>> Not without the whole SQL statement.
>>>>
>>>> S
>>>>> thanks
>>>>>
>>>>>
>>>>> Em 10-05-2012 04:40, Simon Greener escreveu:
>>>>>> Folks,
>>>>>>
>>>>>> Sorry about that. The file:
>>>>>>
>>>>>> http://www.spatialdbadvisor.com/file_download/55/PostGIS_Parallel.sql
>>>>>>
>>>>>> Did not contain all the required types and functions. One needed also
>>>>>> to access the types and functions for my ST_GetVector function.
>>>>>>
>>>>>> I have now included all the required types and related functions in
>>>>>> the above script and replaced it on my site.
>>>>>>
>>>>>> I have changed a bit in the code as it didn't handle some situations
>>>>>> as I expected. Hopefully my test cases give good coverage.
>>>>>>
>>>>>> My tests, conducted on PostgreSQL 9.1 and PostGIS 2.0 on Windows 7 64
>>>>>> Bit all work as this example shows:
>>>>>>
>>>>>> With geometries As (
>>>>>> select ST_GeomFromText('LINESTRING(1 1,1 10)') as geom,
>>>>>> 10.0 as offset,2 as roundFactor,0 as curved
>>>>>> union all select ST_GeomFromText('LINESTRING(0 0,1 1,1 2)') as geom,
>>>>>> 0.5 as offset,2 as roundFactor,
>>>>>> generate_series(0,1,1) as curved
>>>>>> union all select ST_GeomFromText('LINESTRING(0.0 0.0, 45.0 45.0, 90.0
>>>>>> 0.0, 135.0 45.0, 180.0 0.0, 180.0 -45.0, 45.0 -45.0, 0.0 0.0)') as
>>>>>> geom,
>>>>>> 10.0 as offset,2 as roundFactor,
>>>>>> generate_series(0,1,1) as curved
>>>>>> union all select ST_GeomFromText('MULTILINESTRING((0 0,1 1,1 2),(2 3,3
>>>>>> 2,5 4))') as geom,
>>>>>> 0.5 as offsetRight,2 as roundFactor,
>>>>>> generate_series(0,1,1) as curved
>>>>>> )
>>>>>> select ST_AsText(g.geom) as origGeom, g.offset,g.curved,
>>>>>> ST_AsText(ST_Parallel(g.geom,g.offset,g.roundFactor,g.curved))
>>>>>> as geomWKTLeft,
>>>>>>
>>>>>> ST_AsText(ST_Parallel(g.geom,0.0-g.offset,g.roundFactor,g.curved)) as
>>>>>> geomWKTRight
>>>>>> from geometries as g;
>>>>>>
>>>>>> "LINESTRING(1 1,1 10)";10.0;0;"LINESTRING(11 1,11 10)";"LINESTRING(-9
>>>>>> 1,-9 10)"
>>>>>> "LINESTRING(0 0,1 1,1 2)";0.5;0;"LINESTRING(0.35 -0.35,1.5 0.79,1.5
>>>>>> 2)";"LINESTRING(-0.35 0.35,0.5 1.21,0.5 2)"
>>>>>> "LINESTRING(0 0,1 1,1 2)";0.5;1;"COMPOUNDCURVE((0.35 -0.35,1.35
>>>>>> 0.65),CIRCULARSTRING(1.35 0.65,1.46 0.81,1.5 1),(1.5 1,1.5
>>>>>> 2))";"LINESTRING(-0.35 0.35,0.5 1.21,0.5 2)"
>>>>>> "LINESTRING(0 0,45 45,90 0,135 45,180 0,180 -45,45 -45,0
>>>>>> 0)";10.0;0;"LINESTRING(7.07 -7.07,45 30.86,90 -14.14,135 30.86,170
>>>>>> -4.14,170 -35,49.14 -35,7.07 7.07)";"LINESTRING(-7.07 7.07,45 59.14,90
>>>>>> 14.14,135 59.14,190 4.14,190 -55,40.86 -55,-7.07 -7.07)"
>>>>>> "LINESTRING(0 0,45 45,90 0,135 45,180 0,180 -45,45 -45,0
>>>>>> 0)";10.0;1;"COMPOUNDCURVE((7.07 -7.07,45 30.86,82.93
>>>>>> -7.07),CIRCULARSTRING(82.93 -7.07,90 -10,97.07 -7.07),(97.07
>>>>>> -7.07,134.71 31.15,170 -4.14,170 -35,49.14 -35,7.07
>>>>>> 7.07))";"COMPOUNDCURVE((-7.07 7.07,37.93 52.07),CIRCULARSTRING(37.93
>>>>>> 52.07,45 55,52.07 52.07),(52.07 52.07,89.71 13.85,127.93
>>>>>> 52.07),CIRCULARSTRING(127.93 52.07,135 55,142.07 52.07),(142.07
>>>>>> 52.07,187.07 7.07),CIRCULARSTRING(187.07 7.07,189.16 4.01,190 0),(190
>>>>>> 0,190 -45),CIRCULARSTRING(190 -45,187.13 -52.01,180 -55),(180 -55,45
>>>>>> -55),CIRCULARSTRING(45 -55,41.27 -54.28,37.93 -52.07),(37.93
>>>>>> -52.07,-7.07 -7.07))"
>>>>>> "MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5
>>>>>> 4))";0.5;0;"MULTILINESTRING((1.65 2.65,3 1.29,5.35
>>>>>> 3.65))";"MULTILINESTRING((2.35 3.35,3 2.71,4.65 4.35))"
>>>>>> "MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))";0.5;1;"MULTICURVE((1.65
>>>>>> 2.65,2.65 1.65),CIRCULARSTRING(2.65 1.65,3 1.5,3.35 1.65),(3.35
>>>>>> 1.65,5.35 3.65))";"MULTILINESTRING((2.35 3.35,3 2.71,4.65 4.35))"
>>>>>>
>>>>>> Sorry for any inconvenience.
>>>>>>
>>>>>> The file is available for download via the link above.
>>>>>>
>>>>>> regards
>>>>>> Simon
>>>>>>
>>>>>>
>>>>>> On Wed, 09 May 2012 23:27:04 +1000, Pedro Costa
>>>>>> <pedrocostaarma at sapo.pt> wrote:
>>>>>>
>>>>>>> No problem Hugues.
>>>>>>>
>>>>>>> Give me the same error:
>>>>>>>
>>>>>>> ERROR: function st_parallel(geometry, numeric, numeric, integer) does
>>>>>>> not exist
>>>>>>> LINE 1: select ST_Parallel(the_geom, 3.0, 0.0, 0) as test from tests
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Em 09-05-2012 13:48, Francois Hugues escreveu:
>>>>>>>> Sorry, I read your message too fast and forgot the PS.
>>>>>>>>
>>>>>>>> Maybe you could try something like :
>>>>>>>> select ST_Parallel(the_geom, 3.0, 0.0, 0) as test from tests
>>>>>>>>
>>>>>>>> Hugues.
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> -----Message d'origine-----
>>>>>>>> De :postgis-users-bounces at postgis.refractions.net
>>>>>>>> [mailto:postgis-users-bounces at postgis.refractions.net] De la part de
>>>>>>>> Pedro Costa
>>>>>>>> Envoyé : mercredi 9 mai 2012 13:06
>>>>>>>> À : PostGIS Users Discussion
>>>>>>>> Objet : Re: [postgis-users] st_offsetcurve
>>>>>>>>
>>>>>>>> Em 09-05-2012 11:28, Pedro Costa escreveu:
>>>>>>>>> PS: I´m already install the function by your file
>>>>>>>> I'm already do that...
>>>>>>>> _______________________________________________
>>>>>>>> postgis-users mailing list
>>>>>>>> postgis-users at postgis.refractions.net
>>>>>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>>>>> _______________________________________________
>>>>>>>> postgis-users mailing list
>>>>>>>> postgis-users at postgis.refractions.net
>>>>>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>>>> _______________________________________________
>>>>>>> postgis-users mailing list
>>>>>>> postgis-users at postgis.refractions.net
>>>>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>>>>
>>>>> _______________________________________________
>>>>> postgis-users mailing list
>>>>> postgis-users at postgis.refractions.net
>>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120517/618a1308/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: print1.bmp
Type: image/bmp
Size: 150354 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120517/618a1308/attachment.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: print2.bmp
Type: image/bmp
Size: 150354 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120517/618a1308/attachment-0001.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: print3.bmp
Type: image/bmp
Size: 150354 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120517/618a1308/attachment-0002.bin>
More information about the postgis-users
mailing list