<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Pedro,<br>
<br>
Thanks for the reply!!!!!<br>
<br>
I'm finding some problems with my query (specifically the "<tt>intersection</tt>"
function).... I can select the entire LINESTRING that intersects a
BBox.... but I can not limit the geometry of that LINESTRING to the
path through the BBox (like in the images below).<br>
<br>
My Query to Find the entire LINESTRING(s) is:<br>
<blockquote><font color="#000099"><tt>select the_geom from
route_service_transaction where
intersects(the_geom, SetSRID('BOX3D(138.4925 -34.905393,138.493313
-34.90458)'::box3d,4283))</tt></font><br>
<br>
This does sucessfully find the LINESTRINGs I am looking for, however
they return the Complete LINESTRING inside AND outside the bbox. I only
want the LINESTRING(s) that are inside the BBox.<br>
<br>
Returns:<tt><br>
<font color="#cc6600">SRID=4283;LINESTRING(138.49680675
-34.89338892,138.496643 -34.8935516,138.496643 -34.8935516,138.496882
-34.893792,138.496882 -34.893792,138.496773 -34.893837,138.496186
-34.894212,138.496186 -34.894212,138.495597 -34.894607,138.495475
-34.894711,138.495422 -34.894779,138.495422 -34.894779,138.495369
-34.894848,138.495301 -34.894999,138.49529 -34.895088,138.49529
-34.895088,138.495277 -34.895194,138.495291 -34.895815,138.495291
-34.895815,138.494732 -34.895772,138.494732 -34.895772,138.494418
-34.895756,138.494418 -34.895756,138.494421 -34.895791,138.494368
-34.896227,138.49427 -34.896491,138.494183 -34.89665,138.493852
-34.897041,138.493775 -34.897091,138.493775 -34.897091,138.493586
-34.89721,138.493344 -34.897282,138.493127 -34.897301,138.493127
-34.897301,138.492088 -34.897342,138.492088 -34.897342,138.492174
-34.898481,138.492174 -34.898481,138.492178 -34.898537,138.492219
-34.899098,138.492219 -34.899098,138.492252 -34.899601,138.492252
-34.899601,138.492261 -34.899752,138.492261 -34.899752,138.492303
-34.900254,138.492303 -34.900254,138.492415 -34.901815,138.492415
-34.901815,138.492439 -34.902282,138.492439 -34.902282,138.492453
-34.902282,138.492472 -34.902285,138.492489 -34.902291,138.492505
-34.902301,138.492518 -34.902312,138.492527 -34.902326,138.492533
-34.902341,138.492534 -34.902353,138.492534 -34.902353,138.492535
-34.902357,138.492533 -34.902373,138.492527 -34.902388,138.492518
-34.902402,138.492505 -34.902413,138.492489 -34.902423,138.492472
-34.902429,138.492453 -34.902432,138.492448 -34.902432,138.492448
-34.902432,138.492631 -34.904971,138.492631 -34.904971,138.492646
-34.904971,138.492666 -34.904974,138.492685 -34.904981,138.492701
-34.904991,138.492715 -34.905003,138.492725 -34.905018,138.492731
-34.905033,138.492732 -34.905044,138.492732 -34.905044,138.492733
-34.90505,138.492731 -34.905067,138.492725 -34.905082,138.492715
-34.905097,138.492701 -34.905109,138.492685 -34.905119,138.492666
-34.905126,138.492646 -34.905129,138.492642 -34.905129,138.492642
-34.905129,138.4927512 -34.9065512,138.4927512 -34.9065512,138.49314358
-34.90652109)</font></tt><br>
<br>
</blockquote>
I have tried to limit the above LINESTRING to the geometry with the
following query:<br>
<blockquote><font color="#000099"><tt>select intersection(<br>
(select the_geom from route_service_transaction where
intersects(the_geom, SetSRID('BOX3D(138.4925 -34.905393,138.493313
-34.90458)'::box3d,4283)) )<br>
,<br>
(SetSRID('BOX3D(138.4925 -34.905393,138.493313
-34.90458)'::box3d,4283))<br>
)</tt></font><br>
<br>
However this returns something very strange - ???MULIPOINT??? and :<br>
Returns:<br>
<tt><font color="#cc6600">SRID=4283;MULTIPOINT(138.492602818432
-34.90458 1.7e-308,138.492662270567 -34.905393 1.7e-308)</font></tt><br>
<br>
</blockquote>
<br>
Any idea how to "trim" or "limit" the LINESTRING in the first query to
that of the path through a BBox??? Also, maintaining this as a
LINESTRING not a multipoint.<br>
<br>
<br>
<br>
Many Thanks!!!! <br>
<br>
<br>
<br>
p.s. sorry if these questions are annoying, I am rather new to PostGIS!<br>
<br>
<br>
<br>
<br>
Pedro Doria Meunier wrote:
<blockquote cite="mid000c01c7121b$4e597150$07d6bed5@oem41cbbf9e178"
type="cite">
<meta http-equiv="Content-Type" content="text/html; ">
<meta name="Generator" content="Microsoft Word 11 (filtered medium)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:"Bauhaus 93";
panose-1:4 3 9 5 2 11 2 2 12 2;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";
color:black;}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-reply;
font-family:Arial;
color:navy;}
@page Section1
{size:595.3pt 841.9pt;
margin:70.85pt 3.0cm 70.85pt 3.0cm;}
div.Section1
{page:Section1;}
-->
</style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="Section1">
<p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;">Hey Andrew,<o:p></o:p></span></font></p>
<p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;"><o:p> </o:p></span></font></p>
<p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;" lang="EN-GB">I
had to do a similiar
thing…<o:p></o:p></span></font></p>
<p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;" lang="EN-GB">“Cut”
the
road segments belonging to a municipality… and the query ended like
this:<o:p></o:p></span></font></p>
<p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;" lang="EN-GB"><o:p> </o:p></span></font></p>
<p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;" lang="EN-GB">create
my_road_segment as<o:p></o:p></span></font></p>
<p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;" lang="EN-GB">select
intersection(r.geometry, f.geometry) as geometry,<o:p></o:p></span></font></p>
<p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;" lang="EN-GB">r.name,
r.sec_name,
r.city, r.region, r.country, r.pcode, r.road_type,<o:p></o:p></span></font></p>
<p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;" lang="EN-GB">r.route_class,
r.speed_class, r.one_way, r.has_dir, r.toll, r.no_car,<o:p></o:p></span></font></p>
<p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;" lang="EN-GB">r.no_bus,
r.no_taxi,
r.no_bic, r.no_truck, r.no_emerg, r.no_deliv, r.no_pedes, r.classific<o:p></o:p></span></font></p>
<p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;">from
rede_estradas as r, freguesias_ram as
f<o:p></o:p></span></font></p>
<p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;" lang="EN-GB">where
r.geometry
&& f.geometry<o:p></o:p></span></font></p>
<p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;" lang="EN-GB">and
intersects(r.geometry, f.geometry)<o:p></o:p></span></font></p>
<p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;" lang="EN-GB">and
f.nome_freg like
'Fajã da Ovelha%';<o:p></o:p></span></font></p>
<p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;" lang="EN-GB"><o:p> </o:p></span></font></p>
<p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;" lang="EN-GB">the
keyword here is
INTERSECTS<o:p></o:p></span></font></p>
<p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;" lang="EN-GB"><o:p> </o:p></span></font></p>
<p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;" lang="EN-GB">This
query creates a
table based on a select. This select returns the road segments *<b><span
style="font-weight: bold;">contained</span></b>* in a polygon defining
a
municipality.<o:p></o:p></span></font></p>
<p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;" lang="EN-GB"><o:p> </o:p></span></font></p>
<p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;" lang="EN-GB">Hope
this helps.<o:p></o:p></span></font></p>
<p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;" lang="EN-GB"><o:p> </o:p></span></font></p>
<div>
<p class="MsoNormal"><u><font color="#0080c0" face="Bauhaus 93"
size="5"><span
style="font-size: 18pt; font-family: "Bauhaus 93"; color: rgb(0, 128, 192);">Pedro
Doria
Meunier</span></font></u><o:p></o:p></p>
<p class="MsoNormal"><font color="black" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial;">(351) 91 302 49 72 -
(351) 96 247 99 12</span></font><o:p></o:p></p>
<p class="MsoNormal"><font color="black" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial;">MSN - <a
href="mailto:pdoriam@hotmail.com">pdoriam@hotmail.com</a></span></font><o:p></o:p></p>
<p class="MsoNormal"><font color="black" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial;">ICQ - 308-182-126<o:p></o:p></span></font></p>
<p class="MsoNormal"><font color="black" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial;">Skype: pdoriam</span></font><o:p></o:p></p>
</div>
<p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: navy;"><o:p> </o:p></span></font></p>
<div>
<div class="MsoNormal" style="text-align: center;" align="center"><font
color="black" face="Times New Roman" size="3"><span
style="font-size: 12pt; color: windowtext;" lang="EN-US">
<hr tabindex="-1" align="center" size="2" width="100%"></span></font></div>
<p class="MsoNormal"><b><font color="black" face="Tahoma" size="2"><span
style="font-size: 10pt; font-family: Tahoma; color: windowtext; font-weight: bold;"
lang="EN-US">From:</span></font></b><font color="black" face="Tahoma"
size="2"><span
style="font-size: 10pt; font-family: Tahoma; color: windowtext;"
lang="EN-US">
<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>] <b><span
style="font-weight: bold;">On Behalf Of </span></b>Andrew Hughes<br>
<b><span style="font-weight: bold;">Sent:</span></b> segunda-feira,
27 de
Novembro de 2006 7:22<br>
<b><span style="font-weight: bold;">To:</span></b>
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<b><span style="font-weight: bold;">Subject:</span></b>
[postgis-users]
Extracting the Linestring Geometry from a BBox(noob)</span></font><font
color="black"><span style="color: windowtext;" lang="EN-US"><o:p></o:p></span></font></p>
</div>
<p class="MsoNormal"><font color="black" face="Times New Roman"
size="3"><span style="font-size: 12pt;"><o:p> </o:p></span></font></p>
<p class="MsoNormal"><font color="black" face="Times New Roman"
size="3"><span style="font-size: 12pt;">Hey All,<br>
<br>
I'm trying to extract some linestrings from a collection of
linestrings
and conditional on being within a BBox.<br>
<br>
I will try and explain this best with some images...<br>
<br>
My input looks like:<br>
<img id="_x0000_i1025" src="cid:part1.02080200.03010109@lisasoft.com"
border="0" height="352" width="516"><br>
<br>
<br>
My desired output looks like this:<br>
<img id="_x0000_i1026" src="cid:part2.07010003.01080203@lisasoft.com"
border="0" height="182" width="200"><br>
(note that I DO want the point where it intersects the bbox)<br>
<br>
<br>
<br>
Is this at all possible to do? or do I need to start writing my own
postgis
fuctions???<br>
<br>
<br>
<br>
Thanks in advance<br>
<br>
--AH<o:p></o:p></span></font></p>
</div>
<pre wrap="">
<hr size="4" width="90%">
_______________________________________________
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>
<br>
<div class="moz-signature">-- <br>
<br>
Regards,<br>
<br>
<b>Andrew Hughes</b><br>
Software Engineer<br>
LISAsoft Pty. Ltd. (Adelaide)<br>
<br>
<hr color="#003466">
<table align="center">
</table>
<table align="center" border="0">
<tbody>
<tr>
<td colspan="5" align="center"><a href="http://www.lisasoft.com">
<h3>LISAsoft Pty. Ltd.</h3>
</a></td>
</tr>
<tr align="center" valign="top">
<td><b>ADELAIDE Office</b><br>
<a
href="http://terrapages.net/mapbutton/RetrieveButtonServlet?buttonID=4"
taget="_blank"><img src="cid:part3.08050800.01060108@lisasoft.com"
border="0"></a><br>
38 Greenhill Road<br>
Wayville SA 5034<br>
Australia<br>
<b>Telephone +61 8 8272 1555</b><br>
<b>Facsimile +61 8 8271 1199</b> </td>
<td width="20"> <br>
</td>
<td><b>SYDNEY Office</b><br>
<a
href="http://terrapages.net/mapbutton/RetrieveButtonServlet?buttonID=1"
taget="_blank"><img src="cid:part3.08050800.01060108@lisasoft.com"
border="0"></a><br>
Suite 112 The Lower Deck<br>
Jones Bay Wharf<br>
19-21 Pirrama Road<br>
Pyrmont NSW 2009 AUS<br>
<b>Telephone +61 2 8570 5060</b><br>
<b>Facsimile +61 2 8570 5099</b> </td>
<td width="20"> <br>
</td>
<td><b>MELBOURNE Office</b><br>
<a
href="http://terrapages.net/mapbutton/RetrieveButtonServlet?buttonID=3"
taget="_blank"><img src="cid:part3.08050800.01060108@lisasoft.com"
border="0"></a><br>
Level 7 520 Collins Street<br>
Melbourne VIC 3000<br>
Australia<br>
<b>Telephone +61 3 9629 1799</b><br>
<b>Facsimile +61 3 9629 4955</b> </td>
</tr>
</tbody>
</table>
<hr color="#003466">
</div>
</body>
</html>