3

I have a point layer (ad.shp) and street layer (st.shp) and I am trying to project the points to the selected street in a distance of 20 meters (snapping all points to the lines within 20 meters) using this approach:

Snapping points in PostGIS

I tried to modify the code given in the mentioned webpage as per points and line layer like this:

-- ad is multipoint layer
-- st is multiline layer
-- Snapping all points within 20 meters
-- Code orginal source: http://blog.cleverelephant.ca/2008/04/snapping-points-in-postgis.html

Select Distinct ON (ad.objectid)
    ad.objectid,
    st.objectid,
        St_AsText(
        St_LineInterpolatePoint(
            st.geom,
            St_LineLocatePoint(st.geom, ad.geom)
        )
    )
From
    (
        Select
            st.geom As Line_geom,
            ad.geom As Address_geom,
            st.gid As Line_id,
            ad.gid As address_id,
            St_Distance(st.geom, ad.geom) As d
        From
            ad,
            st
        Where
            ST_Dwithin(ad.geom, st.geom, 20)
        Order By address_id, d
    ) As subquery;

The error I am getting is "missing From-clause entry for table "ad". Can someone please point to the ambiguity in the above code? I have not tried ST_ClosestPoint() yet.

1

couple of general points 1st: 1) that's a pretty old reference considering the awesome advances made in the last few PostGIS releases--you might want to check the current documentation for newer/better/faster functions; and 2) i see Regina Obe made some comments below the post you reference--always safe to pay close attention when she weighs in.

so without researching the method employed here, here are two issues relating to the specific error you report.

1) there are table.column references in your outer/main query (ad.objectid, st.geom, etc.) but you're selecting only from the subquery--not from any tables.

2) you define aliases in your subquery (Line_geom, Address_geom, Line_id, address_id, and d) that aren't being called by the outer/main query.

So, for example, where...

SELECT foo.id, bar.geom
FROM
(
SELECT foo.id as the_id, bar.geom as the_geom
FROM foo, bar WHERE foo.id = bar.id 
ORDER BY 1
) As Subquery;

...won't work, switching from table.column names to aliases like...

SELECT the_id, the_geom
FROM
(
SELECT foo.id as the_id, bar.geom as the_geom
FROM foo, bar WHERE foo.id = bar.id 
ORDER BY 1
) As Subquery;

...should work just fine. but again, i'd start by reading through the comments below the blog post you reference and checking your methodology with care.

  • Well, thank you so much for the feedback. I was aware that the post was quite older but I can not even dare to overlook the comments of Regina Obe, the iconic name in PostGIS developments! So, I was trying to figure it out through comments in that post as well. Yes, the problem was exactly as you pointed out. Resolving the synergy between outer and sub-query was a solution. Thanks a bunch! :) – khajlk Mar 9 '16 at 23:13

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.