[postgis-users] linestring clipping problem

[Martin Davis]

How about something like:


select new_geom from (
select difference(a.the_geom,b.the_geom) as  new_geom
from scaglinks a,
    itn_clip_polygon_1 b
) where NOT ST_IsEmpty(new_geom)

If this is slow you might need to test whether a is in the bounding box 
of b before performing the difference.

James G Wilkinson wrote:
>
> Folks,
>
>  
>
> I am having a problem determining the best way to clip out and disgrad 
> a set of linestrings.  Here is my setup:
>
>  
>
> *****************
>
> *****************
>
> itn=# select postgis_full_version();
>
>                                                
> postgis_full_version                                              
>
> ------------------------------------------------------------------------------------------------------------------
>
>  POSTGIS="1.3.2" GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.4.9, 29 Oct 
> 2004" USE_STATS (procs from 1.1.2 need upgrade)
>
> (1 row)
>
>  
>
> itn=# select version();
>
>                                                  
> version                                                  
>
> ----------------------------------------------------------------------------------------------------------
>
>  PostgreSQL 8.1.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc 
> (GCC) 4.0.0 20050519 (Red Hat 4.0.0-8)
>
> (1 row)
>
>  
>
> *****************
>
> *****************
>
>  
>
> My linestring network has the following attributes (and a picture for 
> visualization):
>
>  
>
> *****************
>
> *****************
>
> itn=# \d itn.scaglinks
>
>                                                   Table "itn.links"
>
>          Column         |       Type       
> |                                  
> Modifiers                                  
>
> ------------------------+------------------+-------------------------------------------------------------------------
>
>  record_id_alternate    | integer          | not null default 
> nextval('itn.scaglinks_record_id_alternate_seq3'::regclass)
>
>  a_node                 | integer          |
>
>  b_node                 | integer          |
>
>  dist                   | integer          |
>
>  taz_id                 | integer          |
>
>  .<clip>
>
>  .
>
>  .<clip>
>
>  the_geom               | geometry         |
>
> Indexes:
>
>     "idx_scaglinks_00" btree (a_node)
>
>     "idx_scaglinks_01" btree (b_node)
>
>     "idx_scaglinks_02" btree (a_node, b_node)
>
>     "idx_scaglinks_03" btree (link_id)
>
>     "pkgis_scaglinks" gist (the_geom)
>
> Check constraints:
>
>     "enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)
>
>     "enforce_geotype_the_geom" CHECK (geometrytype(the_geom) = 
> 'MULTILINESTRING'::text OR the_geom IS NULL)
>
>     "enforce_srid_the_geom" CHECK (srid(the_geom) = 26911)
>
>  
>
> scaglinks.GIF
>
>  
>
> *****************
>
> *****************
>
>  
>
> My clipping polygon has the following attributes (and a picture for 
> visualization):
>
>  
>
> *****************
>
> *****************
>
>  
>
> itn=# \d itn.itn_clip_polygon_1
>
>  Table "itn.itn_clip_polygon_1"
>
>   Column  |   Type   | Modifiers
>
> ----------+----------+-----------
>
>  cyid     | integer  |
>
>  the_geom | geometry |
>
> Indexes:
>
>     "pkgis_itn_clip_polygon_1" gist (the_geom)
>
>  
>
> scaglinks_clip_polygon.GIF
>
>  
>
>  
>
> *****************
>
> *****************
>
>  
>
> When I run the following command to clip the scaglinks linestrings, the
>
> results are what I would expect (again, a picture for visualization):
>
>  
>
> *****************
>
> *****************
>
>  
>
> itn=# create table clipped as select 
> intersection(a.the_geom,b.the_geom) as
>
> new_geom, length(a.the_geom) as original_link_length, a.* from 
> scaglinks a,
>
> itn_clip_polygon_1 b where intersects(a.the_geom,b.the_geom);
>
> SELECT
>
>  
>
> itn=# create index pkgis_clipped on clipped using gist(new_geom);
>
> CREATE INDEX
>
>  
>
> itn=# \d itn.clipped
>
>                   Table "itn.clipped"
>
>          Column         |       Type       | Modifiers
>
> ------------------------+------------------+-----------
>
>  new_geom               | geometry         |
>
>  original_link_length   | double precision |
>
>  record_id_alternate    | integer          |
>
>  a_node                 | integer          |
>
>  b_node                 | integer          |
>
>  dist                   | integer          |
>
>  taz_id                 | integer          |
>
>  .<clip>
>
>  .
>
>  .<clip>
>
>  the_geom               | geometry         |
>
> Indexes:
>
>     "pkgis_clipped" gist (new_geom)
>
>  
>
> scaglinks_clipped.GIF
>
>  
>
> *****************
>
> *****************
>
>  
>
> What I am really after are the links that fall outside the clipping
>
> polygon.  So I attempted the following query:
>
>  
>
> *****************
>
> *****************
>
> itn=# create table clipped2 as SELECT a.*, 
> difference(a.the_geom,b.new_geom) as new_geom from scaglinks a, clipped b;
>
>  
>
> ******** after 12 hours, the query throws the following error ********
>
>  
>
> ERROR: could not extend relation 1663/164540/180960: No space left on 
> device
>
> HINT: Check free disk space
>
>  
>
> *****************
>
> *****************
>
>  
>
> The scaglinks network consists of 108,000 links and the clipped network
>
> consists of 6,200 links.  I have approximately 1.5 TB of free disk so I
>
> do not believe that disk space should be a problem.
>
>  
>
> *****************
>
> *****************
>
>  
>
> Again, what I had hoped is that the query to create clipped2 would return
>
> a table that results in the following graphic:
>
>  
>
> but_want_this.GIF
>
>  
>
> *****************
>
> *****************
>
>  
>
> Can you offer any help in the query that will result in a table that
>
> looks something like the last graphic?
>
>  
>
> Best Regards,
>
>  
>
> Jim
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>   

-- 
Martin Davis
Senior Technical Architect
Refractions Research, Inc.
(250) 383-3022