Do you really need st_intersects? Can you use ST_Dwithin(point, line, your_precision) instead?
Cheers, Rémi-C 2013/10/29 franco base <[email protected]> > Thanks Remi > You are very clear now. > > Meanwhile I make some test and I think there is a bug somewhere. > I use the ST_ClosestPoint example documenation > > > > SELECT ST_AsText(ST_ClosestPoint(pt,line)) AS cp_pt_line, > ST_AsText(ST_ClosestPoint(line,pt)) As cp_line_pt, > ST_Contains(line, ST_ClosestPoint(line,pt)), --- f > ST_Intersects (line, ST_ClosestPoint(line,pt)), --- f > ST_Distance (line, ST_ClosestPoint(line,pt)), ---- 0 > ST_Line_Locate_Point (line, ST_ClosestPoint(line,pt)) --- > return value > > FROM > (SELECT 'POINT(100 100)'::geometry As pt, > 'LINESTRING (20 80, 98 190, 110 180, 50 75 )'::geometry > As line ) As foo > > > give this output: > > > "cp_pt_line";"cp_line_pt";"st_contains";"st_intersects";"st_distance";"st_line_locate_point" > "POINT(100 100)";"POINT(73.0769230769231 > 115.384615384615)";f;f;0;0.828619715102687 > > > Something is wrong > > > > > > > 2013/10/29 Rémi Cura <[email protected]> > >> Hey sorry for being unclear. >> >> I put you a pseudo code and some explanation : >> >> - first you work with real world data (observation, measure), which >> are of a given precision (showing the uncertainty of the measure). >> If your data are real world observation ,they may be precise up to >> the centimeter, the meter , whatever. So there is no significance of >> keeping XXX digits after the comma. >> - Second about translating : for your computer manipulating a number >> like 650254,2354 is not the same as manipulating a number like 254,2354. >> Numbers are represented with double precision, which can be precise >> up to* 15 digits *thanks to postgres. Keeping the number of digits >> low in your coordinates "free" some precision for computing. You can >> translate by any number as long as it reduce the number of digits in the >> coordinates. >> You can also manually translate like : x := x - 859 ; y:= y - 87426. >> - Third : about ST_ClosestPoint : if you point are a result of a >> previous computing, they are already of limited precision, thus you have >> to >> compensate this by manually putting the points onto the lines (using >> ST_ClosestPoint will give you a new point corresponding to old point >> projected onto line). >> >> >> >> Here is the pseudo code >> >> --if your data are of limited precision (always the case ! ) >> snap to grid (your data precision) >> >> --before computation >> translate everyting (-(min_x+max_x)/2, -(min_y+max_y)/2) >> >> --computation : do your spatial test >> >> --if precision issue not solved, use ST_ClosestPoint to project your >> point onto lines >> >> --after computation >> translate everyting (+(min_x+max_x)/2, +(min_y+max_y)/2) >> >> Cheers, >> >> Rémi-C >> >> >> 2013/10/29 franco base <[email protected]> >> >>> Ayway >>> St_split and St_snap don't work >>> >>> SELECT line, >>> point, >>> st_contains (st_snap (geom_line, geom_point,0.001), geom_point), >>> st_geometrytype ((st_dump(ST_split(st_snap (geom_line, >>> geom_point,0.001), geom_point))).geom), >>> (st_dump(ST_split(st_snap (geom_line, geom_point,0.001), >>> geom_point))).geom as splitted_geometry >>> >>> give >>> >>> "id";"line";"point";"st_contains";"st_geometrytype";"splitted_geometry" >>> >>> 1;7646;11764;t;"ST_LineString";"0102000020BB0B000006000000A4703D0A471F37412D211F04AF335341FAEDEBE0491F3741325530C2AE335341BD5296014F1F37418FC2F578AE3353414694F606521F3741D2DEE07BAE3353412D211F74541F37418FC2F578AE3353415713362F611F37410D996234AF335341" >>> >>> 2;7646;11764;t;"ST_LineString";"0102000020BB0B0000020000005713362F611F37410D996234AF33534123B9FC675020374112143FF6BC335341" >>> >>> 3;7646;11769;t;"ST_LineString";"0102000020BB0B000006000000A4703D0A471F37412D211F04AF335341FAEDEBE0491F3741325530C2AE335341BD5296014F1F37418FC2F578AE3353414694F606521F3741D2DEE07BAE3353412D211F74541F37418FC2F578AE3353415EF0DD584620374139BF2762BC335341" >>> >>> 4;7646;11769;t;"ST_LineString";"0102000020BB0B0000020000005EF0DD584620374139BF2762BC33534123B9FC675020374112143FF6BC335341" >>> >>> 5;7646;11762;t;"ST_LineString";"0102000020BB0B000006000000A4703D0A471F37412D211F04AF335341FAEDEBE0491F3741325530C2AE335341BD5296014F1F37418FC2F578AE3353414694F606521F3741D2DEE07BAE3353412D211F74541F37418FC2F578AE335341A5CBC785841F374156A2A33CB1335341" >>> >>> 6;7646;11762;t;"ST_LineString";"0102000020BB0B000002000000A5CBC785841F374156A2A33CB133534123B9FC675020374112143FF6BC335341" >>> >>> St_geometrytype=ST_LineString but in qgis I have point and line. >>> >>> point splitted geometry (id =2,4,6) = geom_point (starting gometry) >>> line splitted geometry (id =1,3,5) = geom_line (starting gometry) >>> >>> >>> 2013/10/29 franco base <[email protected]> >>> >>>> Hi. >>>> Yes the cause can be that every function has his own "tolerance" >>>> >>>> SELECT line,geom_line,point,geom_point, >>>> ST_ClosestPoint(geom_line,geom_point), ----- return geometry=geom_point >>>> ST_distance (geom_line,geom_point), ---- return 0 >>>> ST_contains(geom_line,geom_point) ---- return false >>>> >>>> >>>> "line";"geom_line";"point";"geom_point";"st_closestpoint";"st_distance";"st_contains" >>>> >>>> 7646;"0102000020BB0B000006000000A4703D0A471F37412D211F04AF335341FAEDEBE0491F3741325530C2AE335341BD5296014F1F37418FC2F578AE3353414694F606521F3741D2DEE07BAE3353412D211F74541F37418FC2F578AE33534123B9FC675020374112143FF6BC335341";11764;"0101000020BB0B00005713362F611F37410D996234AF335341";"0101000020BB0B00005713362F611F37410D996234AF335341";0;f >>>> >>>> 7646;"0102000020BB0B000006000000A4703D0A471F37412D211F04AF335341FAEDEBE0491F3741325530C2AE335341BD5296014F1F37418FC2F578AE3353414694F606521F3741D2DEE07BAE3353412D211F74541F37418FC2F578AE33534123B9FC675020374112143FF6BC335341";11769;"0101000020BB0B00005EF0DD584620374139BF2762BC335341";"0101000020BB0B00005EF0DD584620374139BF2762BC335341";0;f >>>> >>>> 7646;"0102000020BB0B000006000000A4703D0A471F37412D211F04AF335341FAEDEBE0491F3741325530C2AE335341BD5296014F1F37418FC2F578AE3353414694F606521F3741D2DEE07BAE3353412D211F74541F37418FC2F578AE33534123B9FC675020374112143FF6BC335341";11762;"0101000020BB0B0000A5CBC785841F374156A2A33CB1335341";"0101000020BB0B0000A5CBC785841F374156A2A33CB1335341";0;f >>>> >>>> Remi I don't undesrtand very well your tip to translate date and use >>>> St_SnapToGrid >>>> >>>> I cannot snap (using St_Snap) line to point because I'm working on a >>>> road network and I cannot move the line versus point. >>>> >>>> >>>> Thanks >>>> fb >>>> >>>> >>>> 2013/10/28 Rémi Cura <[email protected]> >>>> >>>>> I had the same problem, >>>>> all function don't react the same way when hitting precision limit. >>>>> >>>>> You may want to translate your data to reduce digits, and/or use >>>>> ST_SnapToGrid on everything before using ST_ClosestPoint. >>>>> >>>>> Cheers, >>>>> Rémi-C >>>>> >>>>> >>>>> 2013/10/28 Nicolas Ribot <[email protected]> >>>>> >>>>>> Hmm this is strange: >>>>>> >>>>>> Finding the closest point (st_closestPoint) or using >>>>>> st_lineLocatePoint to project point onto the line does not work either >>>>>> on the provided dataset. >>>>>> Though the st_distance(line, point) returns 0. >>>>>> >>>>>> Nicolas >>>>>> >>>>>> >>>>>> >>>>>> On 28 October 2013 18:42, Rémi Cura <[email protected]> wrote: >>>>>> > You may want to split not with point but with the projected point >>>>>> one line >>>>>> > (use http://postgis.refractions.net/docs/ST_ClosestPoint.html) >>>>>> > If it is not enough, you may want to translate all your geometries >>>>>> to reduce >>>>>> > the number of digits in coordinates. >>>>>> > Another possibility is to use ST_Snap to snap line to point (won't >>>>>> work the >>>>>> > other way) >>>>>> > >>>>>> > Cheers, >>>>>> > Rémi-C >>>>>> > >>>>>> > >>>>>> > 2013/10/28 Nicolas Ribot <[email protected]> >>>>>> >> >>>>>> >> Hi, >>>>>> >> >>>>>> >> The points are not on the line, probably due to rounding errors. >>>>>> >> >>>>>> >> st_contains(line, point) returns false. >>>>>> >> >>>>>> >> Nicolas >>>>>> >> >>>>>> >> On 28 October 2013 18:07, franco base <[email protected]> >>>>>> wrote: >>>>>> >> > Hi. >>>>>> >> > I have a set of linestring and point on linestring. >>>>>> >> > I want cut the linestring on the point. >>>>>> >> > I can have n points for one linestring, >>>>>> >> > so I use ST_Split >>>>>> >> > (insted of ST_Line_Interpolate_Point and ST_Line_Substring) >>>>>> >> > but it doesn't work. >>>>>> >> > >>>>>> >> > line;geom_line;point;geom_point >>>>>> >> > >>>>>> >> > >>>>>> 7646;"0102000020BB0B000006000000A4703D0A471F37412D211F04AF335341FAEDEBE0491F3741325530C2AE335341BD5296014F1F37418FC2F578AE3353414694F606521F3741D2DEE07BAE3353412D211F74541F37418FC2F578AE33534123B9FC675020374112143FF6BC335341";11764;"0101000020BB0B00005713362F611F37410D996234AF335341" >>>>>> >> > >>>>>> >> > >>>>>> 7646;"0102000020BB0B000006000000A4703D0A471F37412D211F04AF335341FAEDEBE0491F3741325530C2AE335341BD5296014F1F37418FC2F578AE3353414694F606521F3741D2DEE07BAE3353412D211F74541F37418FC2F578AE33534123B9FC675020374112143FF6BC335341";11769;"0101000020BB0B00005EF0DD584620374139BF2762BC335341" >>>>>> >> > >>>>>> >> > >>>>>> 7646;"0102000020BB0B000006000000A4703D0A471F37412D211F04AF335341FAEDEBE0491F3741325530C2AE335341BD5296014F1F37418FC2F578AE3353414694F606521F3741D2DEE07BAE3353412D211F74541F37418FC2F578AE33534123B9FC675020374112143FF6BC335341";11762;"0101000020BB0B0000A5CBC785841F374156A2A33CB1335341" >>>>>> >> > >>>>>> >> > ST_GeometryType(St_Split(geom_line, geom_point)) >>>>>> >> > give "ST_GeometryCollection" >>>>>> >> > >>>>>> >> > but >>>>>> >> > >>>>>> >> > (ST_Dump(ST_Split(geom_line, geom_point))).geom >>>>>> >> > >>>>>> >> > return only 3 linestring (one for record) and the geometry is >>>>>> egual to >>>>>> >> > geom_line >>>>>> >> > >>>>>> >> > >>>>>> >> > thanks. >>>>>> >> > >>>>>> >> > fb >>>>>> >> > >>>>>> >> > _______________________________________________ >>>>>> >> > postgis-users mailing list >>>>>> >> > [email protected] >>>>>> >> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>>>>> >> _______________________________________________ >>>>>> >> postgis-users mailing list >>>>>> >> [email protected] >>>>>> >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>>>>> > >>>>>> > >>>>>> > >>>>>> > _______________________________________________ >>>>>> > postgis-users mailing list >>>>>> > [email protected] >>>>>> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>>>>> _______________________________________________ >>>>>> postgis-users mailing list >>>>>> [email protected] >>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>>>>> >>>>> >>>>> >>>>> _______________________________________________ >>>>> postgis-users mailing list >>>>> [email protected] >>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>>>> >>>> >>>> >>> >>> _______________________________________________ >>> postgis-users mailing list >>> [email protected] >>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>> >> >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
