<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Thanks Hugues...<br>
<br>
In this time the select return the street number, my_single_geom and
my_union_geom.<br>
The my_union_geom are the two parallels right?<br>
<br>
<span id="result_box" class="" lang="en"><span class="hps">I tried
to</span> <span class="hps">see the result in</span> <span
class="hps">qgis</span><span>,</span> <span class="hps">adding
a new column</span> <span class="hps atn">(</span><span>the_geom</span><span>)
equal</span> <span class="hps">to the column</span> <span
class="hps">my_union_geom</span><span class="">,</span> <span
class="hps">and adding a new entry in</span><span class="hps">
the table</span> <span class="hps">geometry_columns</span><span>.</span>
<span class="hps">But I can not</span> <span class="hps">see the
result</span><span>,</span> <span class="hps">qgis</span> <span
class="hps">does not recognize as</span> <span class="hps">a</span>
<span class="hps">geometry column</span><span>.</span><br>
<br>
<span class="hps">What would have</span> <span class="hps">to do
to see</span> <span class="hps">the result</span><span class="">?</span></span><br>
<br>
<br>
Thanks<br>
<br>
<br>
Em 15-05-2012 06:56, Francois Hugues escreveu:
<blockquote
cite="mid:3CB901080554B04881D30F111F62D93001471C14@nadia.grenoble.cemagref.fr"
type="cite">
<pre wrap="">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: <a class="moz-txt-link-abbreviated" href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a> 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:
</pre>
<blockquote type="cite">
<pre wrap="">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 : <a class="moz-txt-link-abbreviated" href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a> [<a class="moz-txt-link-freetext" href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</a>] 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:
</pre>
<blockquote type="cite">
<pre wrap="">Pedro,
</pre>
<blockquote type="cite">
<pre wrap="">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?
</pre>
</blockquote>
<pre wrap="">Not without the whole SQL statement.
S
</pre>
<blockquote type="cite">
<pre wrap="">thanks
Em 10-05-2012 04:40, Simon Greener escreveu:
</pre>
<blockquote type="cite">
<pre wrap="">Folks,
Sorry about that. The file:
<a class="moz-txt-link-freetext" href="http://www.spatialdbadvisor.com/file_download/55/PostGIS_Parallel.sql">http://www.spatialdbadvisor.com/file_download/55/PostGIS_Parallel.sql</a>
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
<a class="moz-txt-link-rfc2396E" href="mailto:pedrocostaarma@sapo.pt"><pedrocostaarma@sapo.pt></a> wrote:
</pre>
<blockquote type="cite">
<pre wrap="">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:
</pre>
<blockquote type="cite">
<pre wrap="">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 : <a class="moz-txt-link-abbreviated" href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>
[<a class="moz-txt-link-freetext" href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</a>] 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:
</pre>
<blockquote type="cite">
<pre wrap="">PS: I´m already install the function by your file
</pre>
</blockquote>
<pre wrap="">I'm already do that...
_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
<pre wrap="">
_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
<br>
</body>
</html>