CODEWITHSUNDEEP
sqlpostgresqlpostgis
poweredbygt
poweredbygt

Reputation: 71

I don't get the results I want from my query

I have two tables called 'District' and 'SensorData'. From these two tables I want to know which points from table 'SensorData' are inside 'District' polygons.

I have written this query to get the results I want: 

SELECT combined_sensor_data.point_geom, district.geom_pol
FROM combined_sensor_data, district
WHERE ST_Within(district.geom_pol, combined_sensor_data.point_geom);

but unfortunately I get no point in any district. Which I know for sure that this is a wrong result. So I am assuming I have a mistake in my query. Therefore, I am asking what could be wrong in my query?

Upvotes: 1

Views: 65

Answers (3)

Arun
Arun

Reputation: 128

Try this query. It changes whether the point is contained by any of the polygon in other table or is overlaped on the polygon. you might need to change the group by clause

SELECT combined_sensor_data.point_geom, district.geom_pol
            bool_or((ST_Contains(T2.geom_pol, T1.point_geom) OR ST_Overlaps(T1.point_geom,T2.geom_pol))) AS my_bool
            FROM combined_sensor_data AS T1
                CROSS JOIN district AS T2 WHERE my_bool is true
                GROUP BY combined_sensor_data.point_geom, district.geom_pol

Upvotes: 0

Pranjal Kaushik
Pranjal Kaushik

Reputation: 13

As per my understanding of your question, it seems you want to check if the table2 ('District') also has the same data points as in table1 ('Sensor Data').

You can use the PostgreSQL EXISTS for this:

select combined_sensor_data.point_geom from combined_sensor_data sd 
where exists (select 1 from district d where d.geom_pol=sd.point_geom)

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 176264

You could try changing arguments order:

SELECT combined_sensor_data.point_geom, district.geom_pol
FROM combined_sensor_data
JOIN district
  ON ST_Within(combined_sensor_data.point_geom, district.geom_pol);

Upvotes: 1

Related Questions