4

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:

enter image description here

and the following were their attribute table:

  • table tc_line (part)

enter image description here

  • table tc_point

enter image description here

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;

enter image description here

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.

enter image description here

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)

enter image description here

and I successfully loaded as new layer tc_proj, and its attribute table:

enter image description here

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,

enter image description here

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.

  • I don't use DB manager, but aren't you missing the SELEC?T? Yes, ST_LineLocatePoint will easily handle this kind of problem. – John Powell Oct 20 '16 at 6:31
  • 1
    Is tc_line a table name or a geometry column. It can't be both, as you have it in your final edict. tc_point has the same issue. 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
  • 1
    As a general observation, if you get a syntax error, read the docs and play with the examples until you understand them. People will always be happy to help you, but this is not a debugging service for simple SQL errors. – John Powell Oct 20 '16 at 11:27
  • Post your data. – HeikkiVesanto Oct 20 '16 at 12:42
  • I updated the post. – Heinz Oct 20 '16 at 12:51
5

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.

  • Thank you for the reply, I am newbie to SQL, and in the post above 'st_line_locate_point(tc_line, tc_point)' was the full query I ran. I am trying to do what you posted 'Keep the original line segments and use each point to split the closest segment'. – Heinz Oct 20 '16 at 7:14
  • 2
    You still need a SELECT, this is why you get syntax error pointing to the start of the function -- the query can't be parsed. – John Powell Oct 20 '16 at 7:22
  • I edited the post. – Heinz Oct 20 '16 at 11:06
1

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;

  • 2
    According to the docs, ST_Snap only snaps to the vertices of the reference geometry. So it may not snap to the closest point on the line, only to the closest vertex on that line. – amball Oct 20 '16 at 7:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.