[postgis-users] Select Point near Polyline Postgis

[Rodrigo Martín LÓPEZ GREGORIO]

I think the problem obviously is that some geometries in your table are not
linestrings or multilinestrings. You can check it doing:

SELECT GeometryType(the_geom) FROM mainroad WHERE GeometryType(the_geom) !=
'LINESTRING'

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.

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:

SELECT * ,
line_interpolate_point(the_geom,line_locate_point(the_geom,PointFromText('POINT(5176512121421)',
42102))) FROM
mainroad WHERE GeometryType(the_geom) = 'LINESTRING' ORDER BY
Distance(the_geom,PointFromText('POINT(517651 2121421)', 42102)) LIMIT 1

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:

WHERE (GeometryType(the_geom) = 'LINESTRING') or (GeometryType(the_geom) =
'MULTILINESTRING')

I never used any SRID in my querys but I don't think that can be a problem
at all.

Rodrigo.

On 6/16/07, anhtin <anhtin at gmail.com> wrote:
>
>
> hi Rodrigo
> Thanks very much reply to me.
> With the first script it good.
> However when i run the second script:
>
> SELECT * ,
>
> line_interpolate_point(the_geom,line_locate_point(the_geom,PointFromText('POINT(517651
> 2121421)', 42102)))
> FROM mainroad ORDER BY Distance(the_geom,PointFromText('POINT(517651
> 2121421)', 42102)) LIMIT 1
>
> Suddenly have a exception error :
> ERROR: line_locate_point: 1st arg isnt a line
> SQL state: XX000
>
> Note: the number 42102 is my SRIDs.
>
> if i run this script:
> Select line_locate_point(the_geom,PointFromText('POINT(517651 2121421)',
> 42102)) from mainroad
>
> it error too
>
> this is structure my table mainroad:
> CREATE TABLE mainroad
> (
>   gid serial NOT NULL,
>   name character varying(40),
>   id smallint,
>   the_geom geometry,
>   CONSTRAINT mainroad_pkey PRIMARY KEY (gid),
>   CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2),
>   CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =
> 'MULTILINESTRING'::text OR the_geom IS NULL),
>   CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 42102)
> )
> WITHOUT OIDS;
> ALTER TABLE mainroad OWNER TO postgres;
>
> Can u show me ???
>
>
>
>
>
>
> Rodrigo Martín LÓPEZ GREGORIO-3 wrote:
> >
> > Hi anhtin
> >
> > To find the nearest polyline from a table to a point you can do this:
> >
> > SELECT * FROM mainroad ORDER BY
> > Distance(the_geom,PointFromText('POINT(517651 2121421)')) LIMIT 1
> >
> > To get the coordinates of the nearest point of that line to your point
> > then
> > you must do:
> >
> > SELECT *,
> >
> line_interpolate_point(the_geom,line_locate_point(the_geom,PointFromText('POINT(517651
> > 2121421)'))) FROM mainroad ORDER BY
> > Distance(the_geom,PointFromText('POINT(517651 2121421)')) LIMIT 1
> >
> > So with the last query you can get all the data (*) of the nearest
> > linestring and the point of the that linestring that is nearest to the
> > given
> > Point.
> >
> > The way it works is, first the line_locate_point function get the
> > linstring
> > and the point and gives you a value between 0 and 1 representing the
> > location of the closest point on LineString to the given Point, as a
> > fraction of total 2d line length. Then the line_interpolate_point
> function
> > will take the linestring and the value between 0 and 1 and return a
> Point
> > geometry with the location of the nearest point on the linestring. If
> you
> > want to get the X and Y coordinates of that point you can do also
> > X(geometry), Y(geometry):
> >
> > SELECT *,
> >
> X(line_interpolate_point(the_geom,line_locate_point(the_geom,PointFromText('POINT(517651
> > 2121421)')))),
> >
> Y(line_interpolate_point(the_geom,line_locate_point(the_geom,PointFromText('POINT(517651
> > 2121421)')))) FROM mainroad ORDER BY
> > Distance(the_geom,PointFromText('POINT(517651 2121421)')) LIMIT 1
> >
> > Rodrigo.
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
>
> --
> View this message in context:
> http://www.nabble.com/Select-Point-near-Polyline-Postgis-tf3931432.html#a11151024
> Sent from the PostGIS - User mailing list archive at Nabble.com.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070616/bdfc1e0a/attachment.html>