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

Emily Gouge egouge at refractions.net
Mon Nov 27 16:38:56 PST 2006


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,
> 
> Thanks for the reply!!!!!
> 
> 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:
>>
>>  
>>
>> create my_road_segment as
>>
>> 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???
>>
>>
>>
>> Thanks  in advance
>>
>> --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. (Adelaide)
> 
> --------------------------------------------------------------------------------
> 
> 
>       LISAsoft Pty. Ltd.
> 
> <http://www.lisasoft.com>
> *ADELAIDE Office*
> <http://terrapages.net/mapbutton/RetrieveButtonServlet?buttonID=4>
> 38 Greenhill Road
> 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
> 19-21 Pirrama Road
> 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




More information about the postgis-users mailing list