data (#23) - How to convert x/y coordinate to lat/long cooordinate? (#182) - Message List

How to convert x/y coordinate to lat/long cooordinate?

Hi everybody.

I'm new to pgRouting. I have a project concerns to shorstest path. I use fridastreets with postgis and pgrouting. it work. But i work with desktop application( not web with WMS4W). And I want to convert x/y coordinate data of fridastreet to lat/long coordinate to display firda map with my Gis library(it only supports lat/long coordianate). Are there a formula or funtionce to do it?

I would appreciate your help.


  • Message #632


    What is wrong with x/y coordinates? I'm not familiar with FRIDA data, but I think it is in one of standard projections. Can you please point me to any documentation of that data format or just tell me how x/y values look like?

    One more thing - I'm going to delete the ticket you created, because it has nothing to do with pgRouting itself. For this case the forum post is enough.


    • Message #633

      The x/y looks like X = 3434391.068,Y = 5794015.404 in fridastreets data table, I use mapinfo to show "strassen-joined.shp" file , and this x, y is equivalent with latitude:52o16’33.6’’ and longtitude: 8o2’16.8’’( is it right? in WSG84). I meaned how to convert X/Y to latitude/longtitude like this. Is there a formula or function to do it?

      • Message #634


        It looks like normal meter-based projection. You can convert it to degree-based with transform() function of PostGIS, but for that you need to figure out your original projection EPSG code (I think it should be somewhere in the documentation). I tried to find it, but all information is in German.

        • Message #635

          Thanks for help but now i want to convert degree-based to meter-based projection to use with this function Distance(startpoint(the_geom), SetSRID(MakePoint?(x,y),-1))(i have lat/long must convert to x,y for this function). How can i do it? Can i do it with transform() function of PostGIS? I don't know this function() realy well. Can you show me how to use this function?. I think the meter-based coordinate data of frida(sample of pgrouting map data) in WSG84, it's right? And i want to convert it to lat/long in WSG84 too.

          • Message #636

            Yes, you can use transform function. Please refer to the PostGIS functions documentation ( But first you need to find out the EPSG code for FRIDA data.

            • Message #637

              I don't know how to find th EPSG code forFRIDA data but in file .prj with frida street shape file is: PROJCS["Transverse Mercator",GEOGCS["bessel",DATUM["Deutsches_Hauptdreiecksnetz",SPHEROID["bessel",6377397.155,299.1528128],TOWGS84[598.1,73.7,418.2,0.202,0.045,-2.455,6.70]],PRIMEM["Greenwich",0],UNIT["degree",0.0174532925199433]],PROJECTIONTransverse_Mercator?,PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",9],PARAMETER["scale_factor",1],PARAMETER["false_easting",3500000],PARAMETER["false_northing",0]]

              Can we know EPSG code from this information? If i have the EPSG code of frida. Can you show me the example to use transfrom() funtion to convert a point in degree-based to meter-based projection? Thanks for help!

              • Message #638

                According to the projection description, EPSG code is either 31463 or 31467.

                Let's suppose it is 31467 and you want to transform it to 4326 (good old equidistant cylindrical). Then to transform the geometry you need to do:

                select transform(setsrid(the_geom, 31467), 4326) from tablename;

                But if you have only coordinate values, first you need to make a geometry, then transform it and only then get coordinates:

                select x(transform(setsrid(geometryfromtext('POINT('x' 'y')'), 31467), 4326)), y(transform(setsrid(geometryfromtext('POINT('x' 'y')'), 31467), 4326)) from tablename;

                • Message #644

                  I did what you said, it's ok. Thanks! But I have another problem need your help. How can i get the shortest path distance between 2 any point which have only the lat/long coordinate with function: shortest_path_astar(sql text, source_id integer, target_id integer, directed boolean, has_reverse_cost boolean). If possible, can you show me sample? Thanks again!

                  • Message #645


                    Nice to know it works.

                    Do you mean you need to get a shortest path length? To calculate a length you need to calculate a shortest path first. Knowing coordinates you can get node id values (source_id and target_id). Here is a query that can give you some clue how it works:

                    SELECT gid, source, target, the_geom, distance(the_geom, GeometryFromText?( 'POINT(-66.807175 10.493085)', 54004)) AS dist FROM ccsroad WHERE the_geom && setsrid( 'BOX3D(-66.907175 10.393085, -66.707175 10.593085)'::box3d, 54004) ORDER BY dist LIMIT 1

                    Here is an example of the way how you can get a number of edges and a length of the shortest path -

                    • Message #646

                      I don't know your sample code claerly. Remember I have firdaroad data with EPSG code is 31467 and to any 2 point/long coordinate(i can a point not a startpoint or endpoint of the edge) lat/long coordinate(EPSG code is 4326)because in my project i can oly get the lat/long coordinate of point, but the routing database(frida) is in meter-based coordinate). I want to get shortest path length between these point. Plese give me some more detailed sample.

                      • Message #647

                        Exactly! Look at the query I posted here once again. It selects the first closest edge to a point we specify with a point geometry string. Knowing the closest edge you can select its source or target point (arbitrary or checking which one is closer). The only difference with your case is that it uses projection 54004. Knowing your projection EPSG code and how reproject a data with transform function you can easily change this query to be used with your data. The only unknown place might be the BOX3D part. This part construct a bounding box around given point to search for a closest edge within.

                        • Message #658

                          I know. But Can you show me a detail exmple to get the shortest path length between to point(arbitrary)if possible. I can get the closest edge of 2 point. Then what must i do to get the shortest path length? I try to use joint table sql follow your link, but i can't use it because i don't know exactly this sql statement sentence. Once again please show me another detail exmple(after i get the closest edge of 2 point). Regard. PS: if i get the shortest path length from the source(or target) of one edge to source(or target) of another edge, this shortest length is apporoximate length(exact length is [distance of point to closest edge]+[the shortest lenght from these intersection point to second point]. It's right? How can i get the exact length like that.

                          • Message #659

                            Did you have a look at or tried one of the tutorials already? We prepared them as FOSS4G conference workshops.

                            There is one chapter in the 2007 workshop. It explains how to get the length of each road link in the result table. You just need to loop through the result then and this will be the path length. Look here: