2

I have a table with MULTILINESTRING objects and I want to build a query that outputs the shortest distances between every MULTILINESTRING object and a specific point (the same for all records) using PostGIS. The coordinate system is WGS84.

I'd like a result like the following:

MULTILINESTRING(....) distance1
MULTILINESTRING(....) distance2
MULTILINESTRING(....) distance3

I have tried commands like the following:

SELECT ST_AsText(ST_Distance(geom, st_GeomFromText('POINT(25.3622 35.3621)'), 4326))
FROM my_table;

But it raises the following error:

ERROR:  function st_distance(geometry, geometry, integer) does not exist
LINE 1: SELECT ST_AsText(ST_Distance(geom, st_GeomFromText('POINT(25...
                     ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
********** Error ********** **********

I feel that the answer is easy but I have no much experience in SQL.

UPDATE

After more searching I think that I'm closer to the solution. I run the following SQL code in order to calculate the shortest distance between a Point and Multilinestring records from "my_table" table:

SELECT geom FROM my_table
ORDER BY ST_Distance(geom, ST_SetSRID(ST_GeogFromText('POINT(25.3622 35.3621)'), 4326),
true
)
ASC
LIMIT 1;

This code works and gives me the geometry with the shortest distance:

geom
geometry(MultiLineString,4326)
<geometry object>

But how can I take the corresponding number (distance) ?

1

Finally I managed to find the solution. The code that retrieves all the distances between every MultiLineString record in a table and a specific point:

SELECT ST_Distance(geom, ST_SetSRID(ST_GeogFromText('POINT(25.3622 35.3621)'), 4326), true)
FROM my_table

And to retrieve the shortest distance between MultiLineString records and a Point:

SELECT MIN(ST_Distance(geom, ST_SetSRID(ST_GeogFromText('POINT(25.3622 35.3621)'), 4326), true))
FROM my_table
0

the thing is that you are passing 3 arguments to st_astext, if you just need the distance, the st_distance result is already the thing you need, just avoid using st_astext and you may have what you need:

--test
SELECT 
ST_Distance(st_GeomFromText('POINT(20.3622 35.3621)'), st_GeomFromText('POINT(35.3622 35.3621)'))
--result: 15
  • 1
    Thank you for your answer. I know how use ST_Distance if I have two objects. The point of my question is how to use ST_Distance to calculate the distances between every record (multilinestring) of my_table and a Point (e.g POINT(20.3622 35.3621)). – Gpetr May 10 at 10:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.