2

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?

Taras
  • 32,823
  • 4
  • 66
  • 137
sTonystork
  • 55
  • 1
  • 6
  • The list of tables in the TABLES clause really needs to be stamped out, replaced by modern use of the JOIN clause. This has the side effect of letting you focus on the join rule in isolation, so you can't leave it out of the WHERE accidentally. You're also missing a GROUP BY for aggregation. You've tagged this with KNN -- have you researched the KNN operator and/or ST_DWithin? – Vince May 02 '20 at 03:51
  • Hi Vince, I've researched those methods but in this case I don't need and index of distance value(i.e. 30m). All points I have fall within a distance to begin with. I assume the GROUP BY I am missing takes care of the unique pt.geom values by saying GROUP 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 – sTonystork May 02 '20 at 04:13
  • 2
    Could you give us more details about your tables? This query uses Cartesian join which is usually not effective enough. You must use GROUP BY on the columns which are without aggregator function in the result (e.g. GROUP BY PT.GEOM, PT.CT, PT.ID). It is not clear why do you want LIMIT 1? Do you want to use only the nearest point? But that case no sense to use aggregate function. Please give sample data and sample result what you would like to get. – Zoltan May 02 '20 at 06:53
  • 1
    @StevenNac even if all collisions are within e.g. 30m of an intersection to begin with, if your data is missing a relational attribute that links those collisions to intersections, you need to explicitly create that link; you will have to JOIN the tables ON ST_DWithin with the given threshold and e.g. GROUP BY pt.<primary_key>. – geozelot May 02 '20 at 11:12
  • Are you looking for ST_ClusterDBSCAN ? As asked by Zoltan, it would be useful to give us the expected result. – obchardon May 02 '20 at 18:02

1 Answers1

0

I ended up using ST_DWithin and was able to retrieve a result:

SELECT 
pt.geom, pt.id,
 SUM(VC.PERSONS) AS PEOPLE_INJ,
 SUM(VC.MOTORIST) AS MOTOR_INJ,
 COUNT(*) AS INCIDENT_CT
FROM  public.intrsct_pts as pt
JOIN public.valid_collisions as vc
ON ST_DWITHIN(vc.geom, pt.geom, 30.0) 
GROUP BY pt.id
ORDER BY incident_ct DESC;

-NOTE: this will return ALL collision points that fall within 30m of each point (i.e. some points will be included in multiple different intersections). I'm pretty sure there is a more efficient way to only include each collision using a CROSS JOIN LATERAL and <-> for each vc.geom and pt.geom. Will post a reply if I find the solution

obchardon
  • 1,724
  • 2
  • 13
  • 21
sTonystork
  • 55
  • 1
  • 6
  • 1
    see e.g. https://gis.stackexchange.com/a/352905 with links to further info on the concept – geozelot May 02 '20 at 19:11
  • I was able to figure it out doing something similarly, used this article by Paul Ramsey as a guide: https://carto.com/blog/lateral-joins/ – sTonystork May 02 '20 at 23:44
  • SELECT pt.geom, pt.id, SUM(VC.PERSONS) AS PEOPLE_INJ, SUM(VC.MOTORIST) AS MOTOR_INJ, COUNT(*) AS INCIDENT_CT FROM intersections.geom FROM (SELECT * FROM public.valid_collisions ) as vc CROSS JOIN LATERAL (SELECT pt.id, pt.geom FROM public.intrsct_pts as pt ORDER BY vc.geom <-> pt.geom LIMIT 1 ) as intersections GROUP BY pid, intersections.geom ORDER BY incident_ct DESC); – sTonystork May 02 '20 at 23:45