1

Having a geometry column wkb_geometry, srid 4326 that is a MULTILINESTRING I would like to determine which of these records are within a predetermined distance (say 5000m) of a geometry object POINT

while the following method allows to determine if a polygon contains a point

def self.containing_latlon(lat,lon, polygon)
  ewkb = EWKB.generate(RGeo::Geographic.simple_mercator_factory.point(lon, lat).projection)
  where("ST_Intersects(polygon, ST_GeomFromEWKB(E'\\\\x#{ewkb}'))")
end

ST_Intersects is clearly not an option, as it applies to "any portion of space then they intersect".
I have not found documentation in order to determine if a line is within X distance of a point. But possibly the question is reversed? Should the question not be is the point within a polygon defined by the MULTILINESTRING and a buffer.

How would the above method need to be modified in order to execute this?

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
Jerome
  • 5,583
  • 3
  • 33
  • 76

1 Answers1

1

Use ST_DWithin instead.

For distances using meters cast the parameters to geography, e.g. 5km:

SELECT * FROM t
WHERE ST_DWithin('POINT(7.00 51.82)'::geography,geom::geography,5000);

If you're happy with the unit of measurement of your SRS, just stick to `geometry

SELECT * FROM t
WHERE ST_DWithin('POINT(7.00 51.82)'::geometry,geom,42);

The :: after the WKT literals is a postgres syntax to cast data types. But as it is customary in postgres, there are many ways to do the same thing. The following example casts a WKT literal into a geometry using different techniques:

SELECT 
  CAST('SRID=4326;POINT(1 2)' AS geometry),
  'SRID=4326;POINT(1 2)'::geometry,
  ST_GeomFromText('SRID=4326;POINT(1 2)'),
  ST_SetSRID(ST_MakePoint(1,2),4326);

-[ RECORD 1 ]---+---------------------------------------------------
geometry        | 0101000020E6100000000000000000F03F0000000000000040
geometry        | 0101000020E6100000000000000000F03F0000000000000040
st_geomfromtext | 0101000020E6100000000000000000F03F0000000000000040
st_setsrid      | 0101000020E6100000000000000000F03F0000000000000040

Further reading: Getting all Buildings in range of 5 miles from specified coordinates

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
  • It is `ST_DWithin` and not `ST_Within`. I had cast aside the latter given the conditions, but not known of the former... which requires a tad more attention to decipher given the use of caps! May I also ask about the syntax of the double-colon as I had not come across it yet in postgis.net/docs ? – Jerome May 10 '21 at 10:10
  • 1
    @Jerome the `::` is a postgres syntax. It is a short form for `CAST(value AS datatype)`. I will add a few examples in my answer. Did ST_DWithin work in your use case? – Jim Jones May 10 '21 at 11:11
  • 1
    I believe it will (found docs for it - the shoe fits); I am charting out a work plan and this element was the only gaping hole. I will be trying shortly as the steps get enacted. – Jerome May 10 '21 at 11:16
  • Yes, that worked. Required some finagling with the attribute syntax: `where("ST_DWITHIN('#{class.attribute.as_text}'::geography,wkb_geometry::geography,5000)").all` – Jerome May 11 '21 at 14:10