I think ST_Intersects would work better, it returns a true or false on whether or not two geometries intersect. So you'd want to do something like:
SELECT ST_Intersects(myPointGeo, myPolygonGeo);
or you can do:
SELECT a.id, b.id
FROM pointTableName a, polygonTableName b
WHERE ST_Intersects(a.myPointGeo, b.myPolygonGeo);
Let's start off with what a regular query should look like:
SELECT *
FROM locations
WHERE
ST_Within(point_geom,
ST_GeomFromText(
'POLYGON((50.4 8.2,50.3 9.3,49.9 9.5,49.7 8.8,49.9 7.8,50.4 8.2))',
4326));
If you want to make a purpose-made function to do a similar task:
CREATE OR REPLACE FUNCTION spGeoPoly(polystring text) RETURNS setof points
AS $$BEGIN
RETURN QUERY
SELECT *
FROM locations
WHERE
ST_Within(point_geom, ST_GeomFromText('POLYGON((' || polystring || '))', 4326));
END;$$ LANGUAGE plpgsql;
The correction to your attempt was to correctly concatenate polystring
to build the WKT required to make a geometry.
And to use it to return all columns of location
, it needs to be in the FROM
part:
SELECT *
FROM spGeoPoly('50.4 8.2,50.3 9.3,49.9 9.5,49.7 8.8,49.9 7.8,50.4 8.2');
Or to get only one of the columns of locations
(e.g. name
):
SELECT (spGeoPoly('50.4 8.2,50.3 9.3,49.9 9.5,49.7 8.8,49.9 7.8,50.4 8.2')).name;
Best Answer
From the documentation use a
WKTReader
: