SQL select features within a polygon

user_14390315 0
Viewed 74

I have the following code whick works fine:

select vissoort, count(1), ST_Buffer(ST_GeomFromText('POINT(5.341248 51.615590)',4326):: geography, 2500)
from   visvangsten
where  st_intersects(visvangsten.locatie,
ST_Buffer(ST_GeomFromText('POINT(5.3412480 51.615590)',4326):: geography, 2500))
group by vissoort
order by 2 desc

Now I want the same function but then selecting the features within a polygon instead of the circle/buffer.

I tried things like this but nothing worked:

select vissoort, count(1), ST_asText( ST_Polygon('LINESTRING(5.303 51.629, 5.387 51.626, 5.393 51.588, 5.281 51.592)'::geometry, 4326) )
from   visvangsten
where  st_contains(ST_asText( ST_Polygon('LINESTRING(5.303 51.629, 5.387 51.626, 5.393 51.588, 5.281 51.592)'::geometry, 4326) ), visvangsten.locatie);
group by vissoort
order by 2 desc limit 1

The database table looks like this:

id ([PK]bigint) datum(date) vissoort(character varying) locatie(geometry)
15729 2007-06-23 Blankvoorn 0101000...etc.
etc. etc. etc. etc.

Does someone know the answer?

postgis polygon postgresql sql
1 Answers

Keep in mind that to transform a LineString into a Polygon you need to have a closed ring - in other words, the first and last coordinate pairs must be identical. That being said, you can convert a LineString into a Polygon using the function ST_MakePolygon. The following example is probably what you're looking for:

Data set inside the CTE

WITH j (geom) AS (
  VALUES 
    (ST_MakePolygon('SRID=4326;LINESTRING(-4.59 54.19,-4.55 54.23,-4.52 54.19,-4.59 54.19)'::geometry)),
    (ST_Buffer('SRID=4326;LINESTRING(-4.59 54.19,-4.55 54.23,-4.52 54.19,-4.59 54.19)'::geometry,0.1))
)
SELECT ST_Contains(geom,'SRID=4326;POINT(-4.5541 54.2043)'::geometry) FROM j;

 st_contains 
-------------
 t
 t
(2 Zeilen)
user_22753880
Related