Hi,
I’m going in circles with how to build a complex relation between two models that are based on database tables that have a spatial/geometry field (postgis layers) using a spatial databse function .
Model 1 - Points
Model 2 - Polygons
Geographically many points are within one polygon feature. In my app I regularly need to query this and find the points within the polygons. The basic SQL to do something like this is:
select * from points, polygons
where polygons.id = 123
and ST_within(points.geom, polygons.geom)
As you can see the join really happens with a function rather than a traditional field x = field y
So I’ve tried lots of ways to try and get this to work with relations like this on the polygons model:
(the organisation_id => organisation_id is something I haven’t discussed in this post but its valid and just gets a subset of the records in the Point table)
public function getPoints(){
return $this->hasMany(Points::className(), [‘organisation _id’ => ‘organisation_id’])
->where(“ST_Within(Points.geom, Polygons.geom)”);
}
I’ve also tried onCondition and a few other things but no matter what I do when I run something like this to use the join:
$polygon = $this->findModel($id);
$requiredPoints = polygon->points;
I end up with errors like:
ERROR: missing FROM-clause entry for table “Polygons”
The SQL being executed was: SELECT * FROM “Points” WHERE
(“organisation_id”=1) AND (((“Points”.“organisation_id”=1)) AND (ST_Within(Points.geom, Polygons.geom)))
Feel like Im approaching this the wrong way or missing something simple. Is using a subquery the way to do it? Any suggestions?
Thanks,
Ben