I think the problem obviously is that some geometries in your table are not linestrings or multilinestrings. You can check it doing:<br><br>SELECT GeometryType(the_geom) FROM mainroad WHERE GeometryType(the_geom) != 'LINESTRING'
<br><br>Then you will find wich Geometries are not linestring and those are the ones that are giving you some problems. You must take a look at that geometries and choose what to do with them. <br><br>As a temporary sollution I think you can add a condition to your query that only takes into account for the result the geometries that are LINESTRING. So your query will look something like this:
<br><br><span class="q">SELECT * , line_interpolate_point(the_geom,line_locate_point(the_geom,PointFromText('POINT(517651</span> 2121421)', 42102))) <span class="q">FROM mainroad WHERE </span>GeometryType(the_geom) = 'LINESTRING'
<span class="q"> ORDER BY Distance(the_geom,PointFromText('POINT(517651</span> 2121421)', 42102)) LIMIT 1<br><br>Maybe you can also consider the 'MULTILINESTRING' geometries; the line_interpolate_point should work also with that type of geometry. So the condition will be:
<br><br><span class="q">WHERE </span>(GeometryType(the_geom) = 'LINESTRING') or <span class="q"></span>(GeometryType(the_geom) = 'MULTILINESTRING')<br><br>I never used any SRID in my querys but I don't think that can be a problem at all.
<br><br>Rodrigo.<br><br><div><span class="gmail_quote">On 6/16/07, <b class="gmail_sendername">anhtin</b> <<a href="mailto:anhtin@gmail.com">anhtin@gmail.com</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<br>hi Rodrigo<br>Thanks very much reply to me.<br>With the first script it good.<br>However when i run the second script:<br><br>SELECT * ,<br>line_interpolate_point(the_geom,line_locate_point(the_geom,PointFromText('POINT(517651
<br>2121421)', 42102)))<br> FROM mainroad ORDER BY Distance(the_geom,PointFromText('POINT(517651<br>2121421)', 42102)) LIMIT 1<br><br>Suddenly have a exception error :<br>ERROR: line_locate_point: 1st arg isnt a line
<br>SQL state: XX000<br><br>Note: the number 42102 is my SRIDs.<br><br>if i run this script:<br>Select line_locate_point(the_geom,PointFromText('POINT(517651 2121421)',<br>42102)) from mainroad<br><br>it error too
<br><br>this is structure my table mainroad:<br>CREATE TABLE mainroad<br>(<br> gid serial NOT NULL,<br> name character varying(40),<br> id smallint,<br> the_geom geometry,<br> CONSTRAINT mainroad_pkey PRIMARY KEY (gid),
<br> CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2),<br> CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =<br>'MULTILINESTRING'::text OR the_geom IS NULL),<br> CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 42102)
<br>)<br>WITHOUT OIDS;<br>ALTER TABLE mainroad OWNER TO postgres;<br><br>Can u show me ???<br><br><br><br><br><br><br>Rodrigo Martín LÓPEZ GREGORIO-3 wrote:<br>><br>> Hi anhtin<br>><br>> To find the nearest polyline from a table to a point you can do this:
<br>><br>> SELECT * FROM mainroad ORDER BY<br>> Distance(the_geom,PointFromText('POINT(517651 2121421)')) LIMIT 1<br>><br>> To get the coordinates of the nearest point of that line to your point<br>
> then<br>> you must do:<br>><br>> SELECT *,<br>> line_interpolate_point(the_geom,line_locate_point(the_geom,PointFromText('POINT(517651<br>> 2121421)'))) FROM mainroad ORDER BY<br>> Distance(the_geom,PointFromText('POINT(517651 2121421)')) LIMIT 1
<br>><br>> So with the last query you can get all the data (*) of the nearest<br>> linestring and the point of the that linestring that is nearest to the<br>> given<br>> Point.<br>><br>> The way it works is, first the line_locate_point function get the
<br>> linstring<br>> and the point and gives you a value between 0 and 1 representing the<br>> location of the closest point on LineString to the given Point, as a<br>> fraction of total 2d line length. Then the line_interpolate_point function
<br>> will take the linestring and the value between 0 and 1 and return a Point<br>> geometry with the location of the nearest point on the linestring. If you<br>> want to get the X and Y coordinates of that point you can do also
<br>> X(geometry), Y(geometry):<br>><br>> SELECT *,<br>> X(line_interpolate_point(the_geom,line_locate_point(the_geom,PointFromText('POINT(517651<br>> 2121421)')))),<br>> Y(line_interpolate_point(the_geom,line_locate_point(the_geom,PointFromText('POINT(517651
<br>> 2121421)')))) FROM mainroad ORDER BY<br>> Distance(the_geom,PointFromText('POINT(517651 2121421)')) LIMIT 1<br>><br>> Rodrigo.<br>> _______________________________________________<br>> postgis-users mailing list
<br>> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users
</a><br>><br>><br><br>--<br>View this message in context: <a href="http://www.nabble.com/Select-Point-near-Polyline-Postgis-tf3931432.html#a11151024">http://www.nabble.com/Select-Point-near-Polyline-Postgis-tf3931432.html#a11151024
</a><br>Sent from the PostGIS - User mailing list archive at <a href="http://Nabble.com">Nabble.com</a>.<br><br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net">
postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></blockquote></div><br>