« Return to Thread: Select Point near Polyline Postgis
Re: Select Point near Polyline Postgis
by Rodrigo Martín LÓPEZ GREGORIO-3
::
Rate this Message:
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(517651 2121421)', 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.
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users
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(517651 2121421)', 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@...> 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@...
> 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@...
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users
« Return to Thread: Select Point near Polyline Postgis
| Free embeddable forum powered by Nabble | Forum Help |
