[postgis-users] st_witin

[paul.malm at lfv.se]

Hi,
I have a layer with lines that I have buffered to a polygon layer, those polygons (not multipolygons) are unioned and containes holes.
I would like to create a line layer (from another line layer) with the line parts that are within buffered area in the polygon layer.
I've tried like this:
update linelayer b set the_geom = ST_MULTI(ST_Intersection(b.the_geom, p.the_geom)) FROM polygonlayer p WHERE ST_Intersects(b.the_geom, p.the_geom)
This leaves me with the line parts inside the buffered area and all lines that had no intersection with the buffered polygons. That's ok, but now I have to erase the lines that had no intersection with the polygons.
I run makevalid on the tables, to be sure
UPDATE polygonlayer SET the_geom = ST_Makevalid(the_geom) WHERE st_isvalid(the_geom)=false
UPDATE linelayer SET the_geom = ST_Makevalid(the_geom) WHERE st_isvalid(the_geom)=false

I create a primary key
ALTER TABLE linelayer ADD COLUMN \"pkkey\" serial NOT NULL PRIMARY KEY

I reindex the tables, to be sure:
REINDEX TABLE linelayer
REINDEX TABLE polygonlayer

I change to LineStrings just to be sure not having several linestings in a MultiLineString
CREATE TABLE dumpedlines AS SELECT *, (ST_Dump(the_geom)).geom AS the_geom2 FROM lineLayer
ALTER TABLE dumpedlines DROP COLUMN IF EXISTS the_geom
ALTER TABLE dumpedlines RENAME COLUMN the_geom2 TO the_geom
ALTER TABLE dumpedlines ALTER COLUMN the_geom TYPE geometry(LineString, 32631)

Then I try to delete the lines outside the buffered polygons:
Delete from dumpedlines b WHERE b.pkkey NOT IN (SELECT b.pkkey FROM dumpedlines b, polygon layer c WHERE ST_within(b.the_geom, c.the_geom))

The result is not correct according to me, all the lines outside the buffered polygons are erased, but also SOME lines inside the buffered polygons (that are already cut by the polygons). I've also tried with _ST_within but with the same result.

Any ideas?
Thanks in advance,
Paul
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190829/ded4e587/attachment.html>