[postgis-users] linestring clipping problem

[James G Wilkinson]

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090930/5f4e7c32/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.png
Type: image/png
Size: 162705 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090930/5f4e7c32/attachment.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image004.png
Type: image/png
Size: 251639 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090930/5f4e7c32/attachment-0001.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image009.png
Type: image/png
Size: 169373 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090930/5f4e7c32/attachment-0002.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image010.png
Type: image/png
Size: 76578 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090930/5f4e7c32/attachment-0003.png>