# [postgis-users] Extracting the Linestring Geometry from a BBox(noob)

egouge at refractions.net egouge at refractions.net
Mon Nov 27 19:34:42 PST 2006

```Interesting - learn something new everyday!  Glad it works for you.

Quoting Andrew Hughes <azza at lisasoft.com>:

>
>
>
>
>
>
> Wow thanks emily!
>
>
>
> That seems to work with Polygon's... but not with the bbox!
>
>
>
> Problem solvered!
>
>
>
>
>
> Emily Gouge wrote:
> Try
> it.
>
>
>
>
> This simple example shows that the intersection of a polygon and
> linestring returns a linestring.
>
>
>
>
> select astext(intersection(geomfromtext('POLYGON((1 1, 1 2, 2 2, 2 1, 1
> 1))'), geomfromtext('LINESTRING(0 0, 3 3)')));
>
>
>
>
> Returns:
>
>
>  LINESTRING(1 1,2 2)
>
>
>
>
>
>
>
>
> Andrew Hughes wrote:
>
>
>   Hi Emily,
>
>
>
>
> Thanks for that! Unless I am mistaken......
>
>
>
>
> 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.
>
>
>
>
>  >From : http://postgis.refractions.net/docs/ch06.html#id2526581
>
>
>
>
>     Intersection(geometry, geometry)
>
>
>
>
>         Returns a geometry that represents the point set intersection
> of the
>
>
>         Geometies.
>
>
>
>
>         Performed by the GEOS module
>
>
>
>
>         Do not call with a GeometryCollection as an argument
>
>
>
>
>         OGC SPEC s2.1.1.3
>
>
>
>
>
>
> I am not sure anything does what I am looking for, maybe someone else
> knows???
>
>
>
>
>
>
> --Thanks Heaps Though!
>
>
>
>
>
>
>
>
>
>
> Emily Gouge wrote:
>
>
>     Try the intersection function in the select
> statement.  Something like:
>
>
>
>
> select intersection(the_geom, setsrid('BOX3D(138.4925
> -34.905393,138.493313 -34.90458)', 4283))
>
>
> from route_service_transaction where intersects(the_geom,
> SetSRID('BOX3D(138.4925 -34.905393,138.493313 -34.90458)'::box3d,4283))
>
>
>
>
> Andrew Hughes wrote:
>
>
>       Hi Pedro,
>
>
>
>
>
>
>
>
> 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).
>
>
>
>
> My Query to Find the entire LINESTRING(s) is:
>
>
>
>
>     select the_geom  from route_service_transaction where
> intersects(the_geom,
>
>
>     SetSRID('BOX3D(138.4925 -34.905393,138.493313
> -34.90458)'::box3d,4283))
>
>
>
>
>     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.
>
>
>
>
>     Returns:
>
>
>     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)
>
>
>
>
> I have tried to limit the above LINESTRING to the geometry with the
> following query:
>
>
>
>
>     select intersection(
>
>
>       (select the_geom  from route_service_transaction where
>
>
>     intersects(the_geom, SetSRID('BOX3D(138.4925
> -34.905393,138.493313
>
>
>     -34.90458)'::box3d,4283)) )
>
>
>       ,
>
>
>       (SetSRID('BOX3D(138.4925 -34.905393,138.493313
> -34.90458)'::box3d,4283))
>
>
>     )
>
>
>
>
>     However this returns something very strange - ???MULIPOINT??? and :
>
>
>     Returns:
>
>
>     SRID=4283;MULTIPOINT(138.492602818432 -34.90458
> 1.7e-308,138.492662270567
>
>
>     -34.905393 1.7e-308)
>
>
>
>
>
>
> 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.
>
>
>
>
>
>
>
>
> Many Thanks!!!!
>
>
>
>
>
>
>
>
> p.s. sorry if these questions are annoying, I am rather new to PostGIS!
>
>
>
>
>
>
>
>
>
>
> Pedro Doria Meunier wrote:
>
>
>
>
> Hey Andrew,
>
>
>
>
>
>
>
>
>
> I had to do a similiar thing

>
>
>
>
> “Cut” the road segments belonging to a municipality
and the query
> ended like this:
>
>
>
>
>
>
>
>
>
>
>
>
>
> select intersection(r.geometry, f.geometry) as geometry,
>
>
>
>
> r.name, r.sec_name, r.city, r.region, r.country, r.pcode, r.road_type,
>
>
>
>
> r.route_class, r.speed_class, r.one_way, r.has_dir, r.toll, r.no_car,
>
>
>
>
> r.no_bus, r.no_taxi, r.no_bic, r.no_truck, r.no_emerg, r.no_deliv,
> r.no_pedes, r.classific
>
>
>
>
> from rede_estradas as r, freguesias_ram as f
>
>
>
>
> where r.geometry && f.geometry
>
>
>
>
> and intersects(r.geometry, f.geometry)
>
>
>
>
> and f.nome_freg like 'Fajã da Ovelha%';
>
>
>
>
>
>
>
>
>
> the keyword here is INTERSECTS
>
>
>
>
>
>
>
>
>
> This query creates a table based on a select. This select returns the
> road segments **contained** in a polygon defining a municipality.
>
>
>
>
>
>
>
>
>
> Hope this helps.
>
>
>
>
>
>
>
>
>
> _Pedro Doria Meunier_
>
>
>
>
> (351) 91 302 49 72 - (351) 96 247 99 12
>
>
>
>
> MSN - pdoriam at hotmail.com <mailto:pdoriam at hotmail.com>
>
>
>
>
> ICQ - 308-182-126
>
>
>
>
> Skype: pdoriam
>
>
>
>
>
>
>
>
>
> --------------------------------------------------------------------------------
>
>
>
>
> *From:* postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] *On Behalf Of
> *Andrew Hughes
>
>
> *Sent:* segunda-feira, 27 de Novembro de 2006 7:22
>
>
> *To:* postgis-users at postgis.refractions.net
>
>
> *Subject:* [postgis-users] Extracting the Linestring Geometry from a
> BBox(noob)
>
>
>
>
>
>
>
>
>
> Hey All,
>
>
>
>
> I'm trying to extract some linestrings from a collection of
> linestrings  and conditional on being within a BBox.
>
>
>
>
> I will try and explain this best with some images...
>
>
>
>
> My input looks like:
>
>
>
>
>
>
>
>
> My desired output looks like this:
>
>
>
>
> (note that I DO want the point where it intersects the bbox)
>
>
>
>
>
>
>
>
> Is this at all possible to do? or do I need to start writing my own
> postgis fuctions???
>
>
>
>
>
>
>
>
>
>
>
>
> --AH
>
>
>
>
> --------------------------------------------------------------------------------
>
>
>
>
> _______________________________________________
>
>
> postgis-users mailing list
>
>
> postgis-users at postgis.refractions.net
>
>
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
>
>
>
>
> --
>
>
>
> Regards,
>
>
>
>
> *Andrew Hughes*
>
>
> Software Engineer
>
>
>
>
>
>
> --------------------------------------------------------------------------------
>
>
>
>
>
>
>       LISAsoft Pty. Ltd.
>
>
>
>
> <http://www.lisasoft.com>
>
>
>
>
> <http://terrapages.net/mapbutton/RetrieveButtonServlet?buttonID=4>
>
>
>
>
> Wayville SA 5034
>
>
> Australia
>
>
> *Telephone +61 8 8272 1555*
>
>
> *Facsimile +61 8 8271 1199*        *SYDNEY Office*
>
>
> <http://terrapages.net/mapbutton/RetrieveButtonServlet?buttonID=1>
>
>
> Suite 112 The Lower Deck
>
>
> Jones Bay Wharf
>
>
>
>
> Pyrmont NSW 2009 AUS
>
>
> *Telephone +61 2 8570 5060*
>
>
> *Facsimile +61 2 8570 5099*        *MELBOURNE Office*
>
>
> <http://terrapages.net/mapbutton/RetrieveButtonServlet?buttonID=3>
>
>
> Level 7 520 Collins Street
>
>
> Melbourne VIC 3000
>
>
> Australia
>
>
> *Telephone +61 3 9629 1799*
>
>
> *Facsimile +61 3 9629 4955*
>
>
>
>
> --------------------------------------------------------------------------------
>
>
>
>
>
>
> ------------------------------------------------------------------------
>
>
>
>
> _______________________________________________
>
>
> 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
>
>
>
>
>
>
>
>
>
> --
>
>
>
> Regards,
>
>
>
> Andrew Hughes
>
> Software Engineer
>
>
>
>
>
>
>
>
>
>
>
>       LISAsoft Pty. Ltd.
>
>
>
>
>
>
>
> Wayville SA 5034
>
> Australia
>
>       Telephone +61 8 8272 1555
>
>       Facsimile +61 8 8271 1199
>
>
>
>       SYDNEY Office
>
>
>
> Suite 112 The Lower Deck
>
> Jones Bay Wharf
>
>
> Pyrmont NSW 2009 AUS
>
>       Telephone +61 2 8570 5060
>
>       Facsimile +61 2 8570 5099
>
>
>
>       MELBOURNE Office
>
>
>
> Level 7 520 Collins Street
>
> Melbourne VIC 3000
>
> Australia
>
>       Telephone +61 3 9629 1799
>
>       Facsimile +61 3 9629 4955
>
>
>
>
>
>
>
>

```