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.

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
nicolasca
  • 99
  • 7
  • 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 http://gis.stackexchange.com/questions/593/how-to-calculate-distance-between-two-locations-using-stored-procedure-in-mysql?rq=1 , http://www.codeguru.com/cpp/cpp/algorithms/article.php/c5115/Geographic-Distance-and-Azimuth-Calculations.htm – simpleuser001 Jan 14 '13 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 '13 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 '13 at 08: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 '13 at 19:48
  • see http://stackoverflow.com/questions/12800045/distance-using-wgs84-ellipsoid implement that to MySQL side – simpleuser001 Aug 13 '13 at 11:56

1 Answers1

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.

Magno C
  • 2,140
  • 3
  • 24
  • 63
  • 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 '13 at 14:15