5

Here is the environment in which I work:

  • An application using geo-referenced data
  • Database MySQL 5.5, storing georeferenced points
  • A Geoserver server, connected to the MySQL database

Here is the context (common to many people): I have to calculate the distance between two points stored in my DB (and better, I want all the points within a known distance)

I have searched about the methods available to me on the net, and here are my results:

  • MySQL: I can use the method GLength of the LineString type , but the result is in degrees, which is useless. In addition, the SRID (mine is 4326) can not be used.
  • WFS protocol: I can connect to GeoServer WFS with a query, but currently there is an open issue with DWithin method (the one that interests me), indicating that the parameter "unit" cannot be used (http://jira.codehaus.org/browse/GEOS-937).
  • WPS protocol: I saw a method "Distance". But there is little information, and this method does not seem to use a calculation method that suits me (https://wiki.state.ma.us/confluence/display/massgis/GeoServer+-+WPS+-+Distance).

I have not found other potential solutions.

Are there errors in the results that I described?

I cannot install and use PostgreSQL. I know that it can be done with it.

Improve this question
5
  • Adding comment because i know how this is done in PostGIS. First you can use PostGIS methdot st_length(geom,geom), but data has to be in srid which uses metric units. to archieve this you call ST_Transform(geom, new_srid) where new_srid uses meters. Also look into gis.stackexchange.com/questions/593/… , codeguru.com/cpp/cpp/algorithms/article.php/c5115/…
    – simpleuser001
    Jan 14, 2013 at 14:11
  • Is it possible to calculate the distance in degrees and then convert to your unit (meters?). Lots of overhead, but pretty easy to custom code (assuming Euclidean distance). As an aside - what distance metric are you trying to calculate - Euclidean will introduce error over long distances.?
    – Jay Laura
    Jan 14, 2013 at 19:05
  • Yes it is possible to calculate in degrees and convert, but it's not always precise. Furthermore, we don't want to make such calculation, but we want to use GIS tools.
    – nicolasca
    Jan 15, 2013 at 8:15
  • What is the environment you have for your calculation? Can you utilize python? Ajax calls? Ultimately you -must- do a projection for this to work, the question is how are you going to access a projection engine for this.
    – blord-castillo
    Mar 15, 2013 at 19:48
  • see stackoverflow.com/questions/12800045/… implement that to MySQL side
    – simpleuser001
    Aug 13, 2013 at 11:56

1 Answer 1

Reset to default
1

The code above was tested in Postgre and PostGIS 2.1 :

I have a table named UNITS and need to search units near by 500 Kilometers to the "MYSERIAL" unit. So I make the query using two alias to same table UNITS, one to refer MYSERIAL (p1) and other to search near units and show the DISTANCE (p2). A nested query is necessary to order by DISTANCE.

SELECT * FROM ( 
  SELECT p2.*, 
    ST_X(p2.the_geom) AS longitude, 
    ST_Y(p2.the_geom) as latitude ,
    ST_Distance_Sphere(p1.the_geom,p2.the_geom) as distance 
  FROM units AS p1, units AS p2 where p2.serial<> 'MYSERIAL' and p1.serial = 'MYSERIAL' order by distance asc) as t1 
WHERE ( t1.distance / 1000 ) < 500 order by t1.distance asc

Note P1 refer to MYUNIT, so P2 must ignore the searched unit, so I make p2.serial <> 'MYSERIAL' to avoid include itself in result (distance = 0). I use ST_Distance_Sphere (PostGIS) to make sure the distance respects the used projection.

Improve this answer
1
  • I show point distances, not line length because you said "I have to calculate the distance between two points stored in my DB".
    – Magno C
    Jan 14, 2013 at 14:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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