I have a line shapefile tc_line with 166 segments and a point shapefile tc_point with 8 points, and I loaded them into PostGIS database with DB manager:
and the following were their attribute table:
- table tc_line (part)
- table tc_point
Because these points are just nearby the line, not right on it, I try to use ST_ClosestPoint
first, then use ST_Split
to split the line.
@amball, I tested your code in the edited answer and the result showed:
SELECT ST_Split(ln.geom, ST_Collect(newpt_geom))
FROM tc_line AS ln,
(SELECT ST_ClosestPoint(ST_Collect(ln.geom), pt.geom) AS newpt_geom
FROM tc_line AS ln, tc_point AS pt GROUP BY pt.geom) t1 GROUP BY ln.geom;
The result was strange because it had 166 rows just the same as the rows original line had, which means that ST_split didn't work. So I loaded the result as new layer to check it.
But In QGIS I could not see it even I set its CRS, and as I wanted to export it as ESRI shapefile, error message showed up:
Export to vector file failed.
Error: creation of layer failed (OGR error:Geometry type of `Geometry Collection' not supported in shapefiles.
Type can be overridden with a layer creation option
of SHPT=POINT/ARC/POLYGON/MULTIPOINT/POINTZ/ARCZ/POLYGONZ/MULTIPOINTZ.
)
So I tried to split the line with 2 stages:
- ST_ClosestPoint
- ST_split
In the first stage I ran the code:
SELECT
tc_point.id as pointid,
St_ClosestPoint(ST_collect(tc_line.geom), tc_point.geom) as closest,
st_astext(St_ClosestPoint(st_collect(tc_line.geom), tc_point.geom)) as text
FROM tc_point, tc_line GROUP BY tc_point.id;
The result were fine: (8 closest points)
and I successfully loaded as new layer tc_proj, and its attribute table:
Then I ran stage 2:
SELECT ST_Split(ln.geom, ST_Collect(tc_proj.geom))
from tc_line as ln, tc_proj GROUP BY ln.geom;
error showed up:
Splitting a Line by a GeometryCollection is unsupported
I am newbie to SQL and SQL open source such as PostGIS. How to work the above task successfully? I am working with PostGIS 2.2.1
Update#1
@amball, I tested your following code,
SELECT ST_Split(ln.geom, newpt_geom)
FROM tc_line AS ln,
(SELECT ST_ClosestPoint(ST_Collect(ln.geom), pt.geom) AS newpt_geom
FROM tc_line AS ln, tc_point AS pt
GROUP BY pt.geom) t1
WHERE ST_Intersects(ln.geom, newpt_geom);
and its result,
I don't know why there were 4 rows in the result, and I could see nothing using Create a view
, also, Load as new layer
outputted nothing. Thus, I still can't determine whether result is correct.
select st_closestpoint(st_collect(line_geom), point_geom) as newpts from line_table, point_table
; replacing as appropriate for the real names – John Powell Oct 20 '16 at 11:26