1

I try to use ST_ClosestPoint function to find the closest point to find the MultiLineString layer. My line layer (Reach) has 42 attributes and point layer (Stations) has 7 attributes. My code is:

"select AsWKT(ST_ClosestPoint(geomfromgpb(geometry), geomfromgpb(geom))) from Stations, Reach"

*I wrote geometry and geom for Stations and Reach respectively. They hold geometry with these column names.

It returns me:

('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9616693.712029119 4307906.197583989)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9667592.713239145 4291816.543706047)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9661404.384824551 4276655.139090295)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9616539.003818756 4267372.646468406)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9590857.440898195 4260256.068791624)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9694202.525421893 4322448.76935828)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)
('POINT(9645005.314525882 4320592.270833903)',)

Every 7 coordinates show the points in Stations layer respectively. But it should return 42 coordinates totally, because every line has one closest point. However it returns like 7*42 coordinates and every 7 coordinates are same.

I find the function here: https://www.gaia-gis.it/spatialite-3.0.0-BETA/spatialite-sql-3.0.0.html#p13

And here is my canvas: enter image description here

*I manually try to show the point to give clear vision.

1

As per the docs, ST_ClosestPoint(geom1, geom2)

Returns the Point on geom1 that is closest to geom2.

so if geom1 is a Line and geom2 a Point, it will return the exact point on the Line that is closest to the Point. Your query above, if geometry and geom are correctly refered to Stations and Reach respectively, actually finds the closest point on each Point for each MultiLine...which doesn´t really make sense.

However, I assume you want to find the nearest Point in Stations to each MultiLine in Reach (so that each MultiLine will be be assigned one of the seven Points)?

This is usually refered to as K Nearest Neighbor (KNN) search...and, unfortunately, is not implemented efficiently in SpatiaLite with Version < 4.4.0 (which comes shipped with QGIS 3.0). You will need to do a full CROSS JOIN and find the minimum ST_Distance between each MultiLine and all Points in Stations(don´t forget to fill in the <uid> column names):

    SELECT ln.<uid>,
           pt.<uid> AS closest_point,
           MIN(ST_Distance(pt.geometry, ln.geometry)) AS distance,
           ln.geometry
    FROM Reach AS ln
      CROSS JOIN Stations AS pt
    GROUP BY ln.<uid>

This will return the uid of the closest point (as closest_point) for each MultiLine, together with the line uid, the distance between MultiLine and closest Point, and the MultiLine geometry.

Hope that helps? Not 100% sure if that is what you want...

  • specially thank you for your response. However your answer give me always same point. I put my answer below and they have same vision. – Mustafa Uçar Mar 28 '18 at 9:04
  • 1
    @MustafaUçar Well, I´m pretty sure it does the same thing as your answer, except for the (in this case) unnecessary ST_ClosestPoint generation (adressing performance on large tables)...anyways, glad you got it to work! – ThingumaBob Mar 28 '18 at 10:08
0

The code that gives to me the nearest point to the defined line geometry:

"select r.fid, s.fid, min(ST_Distance(ST_ClosestPoint(geomfromgpb(r.geom), geomfromgpb(s.geometry)),geomfromgpb(s.geometry))) as dist from Stations as s, Reach as group by r.fid")

It gives point id, line id and distance between them respectively. Reach is line layer. Station is point layer.

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.