2

I have just one table of street segments of Berlin (OSM Data, imported using osm2po, SRID set to 25833). I want my query to output all street segments, that are within a certain distance (e.g. 2000m) of those segments which have the attribute clazz of 11 (motorways).

I've tried this query:

SELECT id, clazz FROM berlin_2po_4pgr WHERE NOT clazz = '11' AND ST_DWithin(
(SELECT geom_way WHERE clazz = '11'), geom_way, 2000);

My output is empty (for any given distance). Why? Is it because of wrong use of the Subselect?

3

You could try the following which is a variation of the example from the documentation

SELECT DISTINCT s.id, s.clazz
FROM berlin_2po_4pgr s
    INNER JOIN berlin_2po_4pgr m ON ST_DWithin(s.geom_way, m.geom_way, 2000)
WHERE m.clazz = '11' and 
    s.clazz != '11';
2

Your subquery is wrong and is probably evaluating to null so that no street segments are close to the null geometry.

running the subquery alone would help you identify the problem:

SELECT geom_way WHERE clazz = '11'

Including the FROM clause to your subquery should return some results:

SELECT id, clazz
FROM berlin_2po_4pgr
WHERE NOT clazz = '11' 
  AND ST_DWithin(
    (SELECT geom_way 
     FROM berlin_2po_4pgr 
     WHERE clazz = '11'), geom_way, 2000);
  • Yes, you're right. The Subquery itself outputs a error: "column "geom_way" does not exist". But adding the FROM-part in the subquery outputs an error, too: "more than one row returned by a subquery used as an expression". That was the reason i had omitted this part. I've not found a good description how to correct this error, in this context. – dijea Jun 16 '16 at 10:21

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.