<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=windows-1252"
http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Wow thanks emily!<br>
<br>
That seems to work with Polygon's... but not with the bbox!<br>
<br>
Problem solvered!<br>
<br>
<br>
Emily Gouge wrote:
<blockquote cite="mid456B8D38.8060802@refractions.net" type="cite">Try
it.
<br>
<br>
This simple example shows that the intersection of a polygon and
linestring returns a linestring.
<br>
<br>
select astext(intersection(geomfromtext('POLYGON((1 1, 1 2, 2 2, 2 1, 1
1))'), geomfromtext('LINESTRING(0 0, 3 3)')));
<br>
<br>
Returns:
<br>
LINESTRING(1 1,2 2)
<br>
<br>
<br>
<br>
Andrew Hughes wrote:
<br>
<blockquote type="cite">Hi Emily,
<br>
<br>
Thanks for that! Unless I am mistaken......
<br>
<br>
I think I am working out that the intersection function returns the
points at which the two geometry's intersect. This is the points at
which my LINESTRING enters and exits the BBox (hence the MULTIPOINT).
So I don't think that this really does what I want.
<br>
<br>
>From : <a class="moz-txt-link-freetext" href="http://postgis.refractions.net/docs/ch06.html#id2526581">http://postgis.refractions.net/docs/ch06.html#id2526581</a>
<br>
<br>
Intersection(geometry, geometry)
<br>
<br>
Returns a geometry that represents the point set intersection
of the
<br>
Geometies.
<br>
<br>
Performed by the GEOS module
<br>
<br>
Do not call with a GeometryCollection as an argument
<br>
<br>
OGC SPEC s2.1.1.3
<br>
<br>
<br>
I am not sure anything does what I am looking for, maybe someone else
knows???
<br>
<br>
<br>
--Thanks Heaps Though!
<br>
<br>
<br>
<br>
<br>
Emily Gouge wrote:
<br>
<blockquote type="cite">Try the intersection function in the select
statement. Something like:
<br>
<br>
select intersection(the_geom, setsrid('BOX3D(138.4925
-34.905393,138.493313 -34.90458)', 4283))
<br>
from route_service_transaction where intersects(the_geom,
SetSRID('BOX3D(138.4925 -34.905393,138.493313 -34.90458)'::box3d,4283))
<br>
<br>
Andrew Hughes wrote:
<br>
<blockquote type="cite">Hi Pedro,
<br>
<br>
Thanks for the reply!!!!!
<br>
<br>
I'm finding some problems with my query (specifically the
"intersection" 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>
<br>
select the_geom from route_service_transaction where
intersects(the_geom,
<br>
SetSRID('BOX3D(138.4925 -34.905393,138.493313
-34.90458)'::box3d,4283))
<br>
<br>
This does sucessfully find the LINESTRINGs I am looking for,
however they
<br>
return the Complete LINESTRING inside AND outside the bbox. I only
want the
<br>
LINESTRING(s) that are inside the BBox.
<br>
<br>
Returns:
<br>
SRID=4283;LINESTRING(138.49680675 -34.89338892,138.496643
<br>
-34.8935516,138.496643 -34.8935516,138.496882 -34.893792,138.496882
<br>
-34.893792,138.496773 -34.893837,138.496186 -34.894212,138.496186
<br>
-34.894212,138.495597 -34.894607,138.495475 -34.894711,138.495422
<br>
-34.894779,138.495422 -34.894779,138.495369 -34.894848,138.495301
<br>
-34.894999,138.49529 -34.895088,138.49529 -34.895088,138.495277
<br>
-34.895194,138.495291 -34.895815,138.495291 -34.895815,138.494732
<br>
-34.895772,138.494732 -34.895772,138.494418 -34.895756,138.494418
<br>
-34.895756,138.494421 -34.895791,138.494368 -34.896227,138.49427
<br>
-34.896491,138.494183 -34.89665,138.493852 -34.897041,138.493775
<br>
-34.897091,138.493775 -34.897091,138.493586 -34.89721,138.493344
<br>
-34.897282,138.493127 -34.897301,138.493127 -34.897301,138.492088
<br>
-34.897342,138.492088 -34.897342,138.492174 -34.898481,138.492174
<br>
-34.898481,138.492178 -34.898537,138.492219 -34.899098,138.492219
<br>
-34.899098,138.492252 -34.899601,138.492252 -34.899601,138.492261
<br>
-34.899752,138.492261 -34.899752,138.492303 -34.900254,138.492303
<br>
-34.900254,138.492415 -34.901815,138.492415 -34.901815,138.492439
<br>
-34.902282,138.492439 -34.902282,138.492453 -34.902282,138.492472
<br>
-34.902285,138.492489 -34.902291,138.492505 -34.902301,138.492518
<br>
-34.902312,138.492527 -34.902326,138.492533 -34.902341,138.492534
<br>
-34.902353,138.492534 -34.902353,138.492535 -34.902357,138.492533
<br>
-34.902373,138.492527 -34.902388,138.492518 -34.902402,138.492505
<br>
-34.902413,138.492489 -34.902423,138.492472 -34.902429,138.492453
<br>
-34.902432,138.492448 -34.902432,138.492448 -34.902432,138.492631
<br>
-34.904971,138.492631 -34.904971,138.492646 -34.904971,138.492666
<br>
-34.904974,138.492685 -34.904981,138.492701 -34.904991,138.492715
<br>
-34.905003,138.492725 -34.905018,138.492731 -34.905033,138.492732
<br>
-34.905044,138.492732 -34.905044,138.492733 -34.90505,138.492731
<br>
-34.905067,138.492725 -34.905082,138.492715 -34.905097,138.492701
<br>
-34.905109,138.492685 -34.905119,138.492666 -34.905126,138.492646
<br>
-34.905129,138.492642 -34.905129,138.492642 -34.905129,138.4927512
<br>
-34.9065512,138.4927512 -34.9065512,138.49314358 -34.90652109)
<br>
<br>
I have tried to limit the above LINESTRING to the geometry with the
following query:
<br>
<br>
select intersection(
<br>
(select the_geom from route_service_transaction where
<br>
intersects(the_geom, SetSRID('BOX3D(138.4925
-34.905393,138.493313
<br>
-34.90458)'::box3d,4283)) )
<br>
,
<br>
(SetSRID('BOX3D(138.4925 -34.905393,138.493313
-34.90458)'::box3d,4283))
<br>
)
<br>
<br>
However this returns something very strange - ???MULIPOINT??? and :
<br>
Returns:
<br>
SRID=4283;MULTIPOINT(138.492602818432 -34.90458
1.7e-308,138.492662270567
<br>
-34.905393 1.7e-308)
<br>
<br>
<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:
<br>
<blockquote type="cite"><br>
Hey Andrew,
<br>
<br>
<br>
<br>
I had to do a similiar thing…
<br>
<br>
“Cut” the road segments belonging to a municipality… and the query
ended like this:
<br>
<br>
<br>
<br>
create my_road_segment as
<br>
<br>
select intersection(r.geometry, f.geometry) as geometry,
<br>
<br>
r.name, r.sec_name, r.city, r.region, r.country, r.pcode, r.road_type,
<br>
<br>
r.route_class, r.speed_class, r.one_way, r.has_dir, r.toll, r.no_car,
<br>
<br>
r.no_bus, r.no_taxi, r.no_bic, r.no_truck, r.no_emerg, r.no_deliv,
r.no_pedes, r.classific
<br>
<br>
from rede_estradas as r, freguesias_ram as f
<br>
<br>
where r.geometry && f.geometry
<br>
<br>
and intersects(r.geometry, f.geometry)
<br>
<br>
and f.nome_freg like 'Fajã da Ovelha%';
<br>
<br>
<br>
<br>
the keyword here is INTERSECTS
<br>
<br>
<br>
<br>
This query creates a table based on a select. This select returns the
road segments **contained** in a polygon defining a municipality.
<br>
<br>
<br>
<br>
Hope this helps.
<br>
<br>
<br>
<br>
_Pedro Doria Meunier_
<br>
<br>
(351) 91 302 49 72 - (351) 96 247 99 12
<br>
<br>
MSN - <a class="moz-txt-link-abbreviated" href="mailto:pdoriam@hotmail.com">pdoriam@hotmail.com</a> <a class="moz-txt-link-rfc2396E" href="mailto:pdoriam@hotmail.com"><mailto:pdoriam@hotmail.com></a>
<br>
<br>
ICQ - 308-182-126
<br>
<br>
Skype: pdoriam
<br>
<br>
<br>
<br>
--------------------------------------------------------------------------------
<br>
<br>
*From:* <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>] *On Behalf Of
*Andrew Hughes
<br>
*Sent:* segunda-feira, 27 de Novembro de 2006 7:22
<br>
*To:* <a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<br>
*Subject:* [postgis-users] Extracting the Linestring Geometry from a
BBox(noob)
<br>
<br>
<br>
<br>
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>
<br>
<br>
<br>
My desired output looks like this:
<br>
<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
<br>
<br>
--------------------------------------------------------------------------------
<br>
<br>
_______________________________________________
<br>
postgis-users mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<br>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
<br>
</blockquote>
<br>
<br>
-- <br>
<br>
Regards,
<br>
<br>
*Andrew Hughes*
<br>
Software Engineer
<br>
LISAsoft Pty. Ltd. (Adelaide)
<br>
<br>
--------------------------------------------------------------------------------
<br>
<br>
<br>
LISAsoft Pty. Ltd.
<br>
<br>
<a class="moz-txt-link-rfc2396E" href="http://www.lisasoft.com"><http://www.lisasoft.com></a>
<br>
*ADELAIDE Office*
<br>
<a class="moz-txt-link-rfc2396E" href="http://terrapages.net/mapbutton/RetrieveButtonServlet?buttonID=4"><http://terrapages.net/mapbutton/RetrieveButtonServlet?buttonID=4></a>
<br>
38 Greenhill Road
<br>
Wayville SA 5034
<br>
Australia
<br>
*Telephone +61 8 8272 1555*
<br>
*Facsimile +61 8 8271 1199* *SYDNEY Office*
<br>
<a class="moz-txt-link-rfc2396E" href="http://terrapages.net/mapbutton/RetrieveButtonServlet?buttonID=1"><http://terrapages.net/mapbutton/RetrieveButtonServlet?buttonID=1></a>
<br>
Suite 112 The Lower Deck
<br>
Jones Bay Wharf
<br>
19-21 Pirrama Road
<br>
Pyrmont NSW 2009 AUS
<br>
*Telephone +61 2 8570 5060*
<br>
*Facsimile +61 2 8570 5099* *MELBOURNE Office*
<br>
<a class="moz-txt-link-rfc2396E" href="http://terrapages.net/mapbutton/RetrieveButtonServlet?buttonID=3"><http://terrapages.net/mapbutton/RetrieveButtonServlet?buttonID=3></a>
<br>
Level 7 520 Collins Street
<br>
Melbourne VIC 3000
<br>
Australia
<br>
*Telephone +61 3 9629 1799*
<br>
*Facsimile +61 3 9629 4955*
<br>
<br>
--------------------------------------------------------------------------------
<br>
<br>
<br>
------------------------------------------------------------------------
<br>
<br>
_______________________________________________
<br>
postgis-users mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<br>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
<br>
</blockquote>
<br>
_______________________________________________
<br>
postgis-users mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<br>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
<br>
<br>
</blockquote>
<br>
<br>
<br>
------------------------------------------------------------------------
<br>
<br>
_______________________________________________
<br>
postgis-users mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<br>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
<br>
</blockquote>
<br>
_______________________________________________
<br>
postgis-users mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<br>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
<br>
<br>
</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:part1.08050203.00060009@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:part1.08050203.00060009@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:part1.08050203.00060009@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>