[GIS] spatial query st_contains postgis

Question

This seems too simple to be asking this but I am really new to PostGIS/Postgres have been going around in circles with this. I am performing a really simple spatial query where I would like to make a subset of a table of points conditional on whether they are contained within a polygon in another table.

I can return the rows I want from my table, however, they are being joined to the other table. All I want is a subset of one table based on the condition, not a join per se.

Here's my query:

SELECT * 
FROM
public."KDhh_survey",
public."ur_pilot_survey"
WHERE
ST_contains(public."ur_pilot_survey".the_geom, public."KDhh_survey".the_geom);

I only want the records in table public."KDhh_survey" returned in a new table or view, but ALL records are returned from both and a join created.

I promise I have searched high and low for an answer to this, but every example I found for ST_contains is based on spatially joining two table together. I really only want to query my data set, so that I can create a subset of my original table.

Answer 1

You just need "KDhh_survey".* in your Select

SELECT "KDhh_survey".* 
FROM
public."KDhh_survey",
public."ur_pilot_survey"
WHERE
ST_contains(public."ur_pilot_survey".the_geom, public."KDhh_survey".the_geom);