ST_split returned error or wrong result

Question

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:

1. ST_ClosestPoint
2. 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.

Answer 1

First, if you are getting a syntax error, it would help to post your full query.

Second, you probably want ST_ClosestPoint(tc_line, tc_point), which returns a point (which you can use to split the line) rather than ST_LineLocatePoint(), which returns the fraction along the line where the closest point lies.

The exact query also depends on what you want to do. Merge all line segments and then split them with the points? Keep the original line segments and use each point to split the closest segment?

EDIT based on the clarification in the comments.

You need to first find the closest point on the lines. There are several ways to do this, but I think the easiest is something like this:

SELECT ST_ClosestPoint(ST_Collect(ln.geom), pt.geom) AS newpt_geom 
    FROM tc_line AS ln, tc_point AS pt;

Then use these new points to split your geometry:

SELECT ST_Split(ln.geom, ST_Collect(newpt_geom));

Putting them together:

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;

Note that you need PostGIS 2.2.0+ to use the MultiPoint as the blade in ST_Split. Otherwise, a more complex query is needed.

Answer 2

ST_Snap is the function I would use to stick the points to the line. The full query to split lines at points would be something like this SELECT ST_Split(ln.geom,ST_Union(ST_Snap(pt.geom, ln.geom, ST_Distance(pt.geom, ln.geom)*1.01))) FROM points pt, lines ln;