« Return to Thread: Select Point near Polyline Postgis

Re: Select Point near Polyline Postgis

by anhtin :: Rate this Message:

hi Rodrigo
this my column the_geom is type "MULTILINESTRING"
and all geometry data on this table is type = "MULTILINESTRING"
when i run this script:
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

it not error, however it has not result because geometry data is type = "MULTILINESTRING"

but when i add a condition is:
WHERE (GeometryType(the_geom) = 'LINESTRING') or (GeometryType(the_geom) = 'MULTILINESTRING')
it have exception error:

ERROR: line_locate_point: 1st arg isnt a line
SQL state: XX000

I think this script error because the condition (GeometryType(the_geom) = 'MULTILINESTRING')

How could i do? Because my all geometry data is MULTILINESTRING type.
Could i convert my geometry data to orther data type ("LINESTRING").
But i think it not good if i convered to orther type.

this is my Data MainRoad. Can u try to use it. And u could show me the good a way :).
data+type+MainRoad.rar

Rodrigo Martín LÓPEZ GREGORIO-3 wrote:

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.

_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

« Return to Thread: Select Point near Polyline Postgis