1

I am very new to this topic and after a long time of research I didn't find a solution. I want a query that I can use on my PostGIS database performing a radiussearch.. I inserted the Open Street Map Data from Geofarbik covering whole Austria. The Input parameters are a point in lng,lat notation, like Point(16 48) ~ some point in Vienna and as second parameter the radius in m.

For a reason I don't know the lat lon values in the db are somewhat different. On the internet I found people talking about different types of SRID, but I really don't know which SRID I should use and how it could be useful to me to compare the input data with the data in the database. I added a column geom, because the command ST_Within(geom, geom) needs a geom. Now the data is even less readable, so I cleary don't know what to do with the input data for comparing and knowing it was done the right way. I can't look behind the concept of the SRID in this moment.

SELECT * mytable node
WHERE node.tags @> ARRAY['subway', 'station', 'uic_ref']
and ST_DWithin(geom, st_makepoint(mylon, mylat), radius) = true

My query would look like this, but I doesn't work yet. If someone could give me some advices how to speed up queries like this it would be great to.

1
  • Welcome to GIS SE. Please edit the new information (added as a comment to an answer) into the question. (Questions should stand alone and be complete.)
    – Martin F
    Feb 4 '15 at 16:15
2

In the absence of knowing the error messages are you getting, here's my take on some initial improvements -- I'm new to this too.

SELECT * FROM mytable,node
WHERE node.tags @> ARRAY['subway', 'station', 'uic_ref']
  and ST_DWithin(geom, ST_SetSRID(ST_MakePoint(mylon, mylat),3857), radius)
4
  • yeaI forgot the from, but that wasn't the issue. So the crucial point in my quesion is how I can transform the input data, that's common lng, lat degrees to compare it with the geom inside, because the lng, lat that are used in the database are in a very different format and I think that's something to do with the SRID, but I hardly know anything about this.
    – Shachty
    Feb 3 '15 at 21:31
  • so the usual lng = 16.547 and lat=48.24 is in this format lat:614161876 and lng:182462380 and thats pretty awkward
    – Shachty
    Feb 3 '15 at 21:32
  • Maybe it's EPSG:3857 -- spheroidal mercator in meters? Modified answer to reflect that one. Feb 3 '15 at 22:08
  • No luck with 3857 :-(
    – Shachty
    Feb 4 '15 at 11:07
2

Okay, I found out why the lat lng data was that strange in this case. For importing the data with osm2pgsql in slime node the additional table named planet_osm_nodes is getting generated and that was the one I used. Without the additional parameter -l in the import statement the coordinates are getting projected in the Sperical Mercator projection EPSG:3857. So this issue doesn't come up with using this paramter. Afterwards the values are still integers and thats pretty unuseful, but a cast to floor and everything is fine. Hope someone who has the same issue finds this answer.

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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