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
        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
                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.

  • 2
    Change St_Collect(geom) as Geometry to ST_Collect(geom) as geom. Feb 11 '16 at 8:07
  • I feel so completely stupid for missing that! The query ran so I am hoping it works. Feb 11 '16 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. Feb 11 '16 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. May 25 '16 at 15:49

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.

  • 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 Feb 11 '16 at 12:38

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.