Hello there, here's a question I've looked for on this forum but surprisingly I could not find an answer, so maybe my approach or assumptions aren't right.

I've recently imported an osm-file into postgresql for the purpose of geocoding. using the planet_osm_line table I could extract the street names of a particular city. However the lines table contains multiple way-segments/records for one street. I can join the segments into a single line but that does not work in all cases. Is there a way to extract the "real" CenterPoint of a street ?

I've created a recusive function to create a table with all connected segments like this:

CREATE OR REPLACE FUNCTION public.connectedways (
  main_id bigint
)
RETURNS SETOF public.planet_osm_line AS
$body$
   BEGIN
      Insert into connected Select osm_id,name,way from planet_osm_line where osm_id = main_id; 

  Perform connectedways(b.osm_id) from planet_osm_line a, planet_osm_line b where a.osm_id = main_id 
    and st_touches(a.way,b.way) AND (st_startpoint(a.way)=st_endpoint(b.way)  or 
            st_startpoint(b.way)=st_endpoint(a.way))
    and a.name=b.name and not EXISTS(Select osm_id from connected c where c.osm_id=b.osm_id);        
  RETURN;

END $body$ LANGUAGE 'plpgsql'

Using the result table I merge all geometry to a single line and use st_line_interpolate_point(way, 0.5) to get the centerpoint ;
SELECT ST_AsEWKT(ST_Line_Interpolate_Point(st_makeline(way), 0.50)) from connected;
Using this method it works but the results are not correct???

asked 16 Apr '15, 12:46

Eric%20Bossink's gravatar image

Eric Bossink
26112
accept rate: 0%

edited 16 Apr '15, 14:05

SimonPoole's gravatar image

SimonPoole ♦
42.5k13309670

Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×631
×160
×124

question asked: 16 Apr '15, 12:46

question was seen: 4,405 times

last updated: 16 Apr '15, 14:05

powered by OSQA