Search code examples
mysqlgeospatialspatialopengis

chosing row with smallest GLength value

Lets say I have table named Place with columns:

placeId int not null auto_increment,
latitude double,
longitude double,

Doesn't exacly matter what columns they are, just get an idea.

Now lets say I receive somehow latitude and longitude of a point, what I'm trying to achive is select from table Place row, which GLength value is the smallest.

Its easy when I do something like this in mysql:

SET @ls = 'LineString(50.123 23.321,51.567 23.123)';
SELECT GLength(GeomFromText(@ls));

But generally I have no idea how could I make such query with variables (I'm poor at mysql) any help?

I'd use order by GLength(..) and limit result to 1, but how can I make such iteration?

My goal is to fire code via Java and Hibernate as native sql query

Solution

  • OK, made it with my teammate, if anyone wants it, here's the select:

    SELECT GLength(
    linestringfromwkb(
    LineString(
        GeomFromWKB(Point(j.latitude,j.longitude))
    ,GeomFromWKB(Point(51,22))))),j.latitude,j.longitude FROM my_database.place_table j 
order by 
GLength(
    linestringfromwkb(
    LineString(
        GeomFromWKB(Point(j.latitude,j.longitude))
    ,GeomFromWKB(Point(51,22))))) asc
limit 1;