Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

First, are there errors in the results that I described? Secondly, would you have any suggestions or additional ideas for me?

Edit: I cannot install and use PostGre. I know that it can be done with it.

share|improve this question
    
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/… –  simplexio 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 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 '13 at 19:48
    
see stackoverflow.com/questions/12800045/… implement that to MySQL side –  simplexio Aug 13 '13 at 11:56
add comment

1 Answer

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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