3

I have a dataset of manholes and pipes; I want to snap the lines start and end nodes to the manholes if they are within a specified distance. Been trying to find away to do this using PostGIS or QGIS because I don't have access to the Arc Desktop tools to do it. I found a post of a similar situation but I cannot get the function to work, I don't know what information is suppose to go where in the expression.

    SELECT f.gid as gid,
        ST_Snap(f.Geometry, g.Geometry, 2) as geom
    FROM
        pipe as f,
        (SELECT ST_Collect(Geometry) as Geometry
         FROM mh) as g

I thought it was suppose to work like this;

            SELECT f.id as gid,
                ST_Snap(f.geom, g.geom, 2) as geom
            FROM
                smains as f,
                (SELECT ST_Collect(geom) as Geometry
                 FROM samh) as g

I get column g.geom does not exist, but the column samh.geom does and to my understanding g = samh. When I change it to samh.geom I get missing FROM-clause entry for table "samh" which sounds to me that the variable samh is not defined, but it isn't a variable it is a table.

Improve this question
4
  • 2
    Change St_Collect(geom) as Geometry to ST_Collect(geom) as geom.
    – John Powell
    Feb 11, 2016 at 8:07
  • I feel so completely stupid for missing that! The query ran so I am hoping it works.
    – MrKingsley
    Feb 11, 2016 at 12:03
  • You want to snap smains to samh with a tolerance of 2? What is your SRID, as a matter of interest. I'm not actually sure that you need ST_Collect. ST_Snap should just chose the nearest geometry, and ST_Collect just bundles geometries together, it doesn't actually transform in the way ST_Union does.
    – John Powell
    Feb 11, 2016 at 12:33
  • My SRID is 2954 should be a NAD83 CSRS98 PEI Double Stereographic Projection Metric. I mentioned metric because there is an imperial but it might be NAD27 on the imperial.
    – MrKingsley
    May 25, 2016 at 15:49

1 Answer 1

Reset to default
1

John Barca "Change St_Collect(geom) as Geometry to ST_Collect(geom) as geom" Thanks again John.

Edit: This seems to make a selection and doesn't actually update the geometry to snap to the points, still closer to what I want.

Improve this answer
1
  • Are you doing select in qgis or postgis , if in postgis you just select data, you do not update it, so add select .. into snapped_table... code
    – simpleuser001
    Feb 11, 2016 at 12:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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