postgis : select points and polygons of one table inside polygon of another table

Question

I have searched the forum and tried with solutions of the forum but it didnt work.

I have tried to select points and polygons of one table inside a polygon of another table with the following code :

SELECT pt_poly.*, poly2.*
FROM osm_fuel pt_poly
JOIN boundingbox poly2 
ON ST_Intersects(poly2.way, ST_GeometryFromText(pt_poly.geometry,27572));

I have also tried with st_contains but it's the same. I end up with a table of rows which geometry is only my poly2 polygon repeated many times.

I remember managing to do this with a join and a. ::geometry but i couldnt find exactly how. If someone could help me with this it would be great.

by the way, what is the meaning of :: in postgis?

Answer 1

Most likely you open the result in some app (e.g. QGIS) and because in this query only one column has type 'geometry'(poly2.way) you see the described result.

Don't use *, specify the columns you want explicitly, and append to result columns transformation wkt-geometry from pt_poly, e.g.:

    SELECT pt_poly.<column_name1>
         , pt_poly.<column_name2>
         , poly2.<column_name1>
         , poly2.<column_name2>
         , ST_GeometryFromText(pt_poly.geometry,27572)) as geom
      FROM osm_fuel pt_poly
      JOIN boundingbox poly2 
        ON ST_Intersects 
                       ( poly2.way
                       , ST_GeometryFromText(pt_poly.geometry, 27572)
                       )