I have two tables valid_collisions
(POINT) and intrct_pts
(POINT)
I want to create a new table using a nearest-neighbor type of query to find aggregate values from my valid_collisions
. In essence I am looking for the geometry of the intrsct_pts
and the aggregated values from valid_collisions
based off the distance of the compared geometries.
Example of some code I have
SELECT PT.GEOM, PT.CT, PT.ID,
SUM(VC.PERSONS) AS PEOPLE_INJ,
SUM(VC.MOTORIST) AS MOTOR_INJ,
COUNT(*) AS INCIDENT_CT
FROM PUBLIC.VALID_COLLISIONS AS VC,
PUBLIC.INTRSCT_PTS AS PT
WHERE PT.GEOM(LIMIT 1)***
ORDER BY ST_DISTANCE(VC.GEOM, PT.GEOM) DESC;
The LIMIT
is an error (just there for temp code), but I know that I need to limit the aggregated values and selections by each unique pt.geom
. But how would I go about this through using a spatial call like ST_Distance
to find all closest vc.geom
values to pt.geom
records?
pt.geom
values by sayingGROUP BY pt.geom
. Also, I can use the JOIN call on the collisions data: ` FROM public.intrsct_pts as pt JOIN public.collisions as vc (ST_...) ORDER BY pt.geom`JOIN
the tablesON ST_DWithin
with the given threshold and e.g.GROUP BY pt.<primary_key>
.ST_ClusterDBSCAN
? As asked by Zoltan, it would be useful to give us the expected result.